电力查询分组

Question

Power query能做到这一点吗？所以我有一组父母的身份证。如果父If相同，但相应属性的值不同，我希望PQ让我知道它们可以分组在一起。这是一个例子。所以父it 12345是相同的，值是不同的，我希望输出表示SDSKU..Yes，如果父it 333是相同的，值是相同的，那么这将不是一个分组，我希望它说不。见图像链接

​

​

Answer 1

如果您的意思是“值”列"Color“的值，请尝试下面的M代码：

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Parent ID", Int64.Type}, {"Kitchen Sink", Int64.Type}, {"Color", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Parent ID", "Kitchen Sink"}, {{"AllData", each _, type table [Parent ID=nullable number, Kitchen Sink=nullable number, Color=nullable text]}, {"OccuID", each Table.RowCount(_), Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "NumberOfColors", each List.Count(List.Distinct([AllData][Color]))),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "SDSKU", each if [OccuID] = [NumberOfColors] then "Yes" else "No"),
    #"Expanded AllData" = Table.ExpandTableColumn(#"Added Custom1", "AllData", {"Kitchen Sink", "Color"}, {"Kitchen Sink.1", "Color"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded AllData",{"OccuID", "NumberOfColors"})
in
    #"Removed Columns"

​

​

Answer 2

如果"attributes“是除命名父ID之外的每一列的值，请尝试下面的M代码：

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Grouped Rows" = Table.Group(Source , {"Parent ID"}, {
    {"data", each _, type table },
    {"check", each if Table.RowCount(_) = Table.RowCount(Table.Distinct(_, List.Difference(Table.ColumnNames(_),{"Parent ID"}))) then "YES" else "NO"}}),
#"Expanded data" = Table.ExpandTableColumn(#"Grouped Rows", "data", List.Difference(Table.ColumnNames(Source),{"Parent ID"}), List.Difference(Table.ColumnNames(Source),{"Parent ID"}))
in #"Expanded data"