跨多个表比较数据

Question

背景:在左边的图片中，我有A，B.C等材料。每种材料都可以来自3个供应商(1-3)，但由于它们是不同的供应商，每种材料都含有不同的杂质(金属)。

​

​

Problem/到目前为止:为了简化，我使用了power查询将数据分割成3个独立的表，这样数据就被划分了(如下所示)。

​

​

现在，对于每个杂质(金属)，我希望比较三个表之间的金属，如果可能的话，返回一个仅包含最高值的列表以及表名(供应商)。

事实证明，这是相当具有挑战性的，尽管我相信类似的分析也会在此之前完成。本质上，我只是想对多个表的值进行比较，以返回最高的值和它的来源。如果有人做过这样的事情，有任何建议/解决方案，我们将不胜感激。

Answer 1

有点不同的PQ方法

• 对原始表进行拆分，为每个项目创建一行，为金属和金额
• 分别创建列，使用Table.Group函数提取每个材料/金属组合

的最高数量和对应的供应商。

M码

let
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    #"Renamed Columns1" = Table.RenameColumns(Source,{{"Column1", "Material"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns1",
        {{"Material", type text}, {"1", type text}, {"2", type text}, {"3", type text}}),

//Unpivot to create three columns
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Material"}, "Supplier", "Value"),

//Split the Metals column into rows by line feed to => one row per metal
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Unpivoted Other Columns", {
        {"Value", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), 
        let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Value"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Value", type text}}),

//Split Metals column at the space to => two columns -- one for the name; the other for the amount
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "Value", 
        Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Metal", "Amount"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Metal", type text}, {"Amount", Int64.Type}}),

//Group rows by Material and Metal
//Extract the highest amount and corresponding suppler
    #"Grouped Rows" = Table.Group(#"Changed Type2", {"Material", "Metal"}, {
        {"Amount", each List.Max([Amount]),type number},
        {"Supplier", (t) => t[Supplier]{List.PositionOf(t[Amount],List.Max(t[Amount]))}, type text}
        })
in
    #"Grouped Rows"

​

​

Answer 2

假设我们从powerquery中的top表开始，您可以使用以下代码转换到第二个映像：

它做一个upivot，在linefeed上分割，在空间上分割，然后重命名。

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"1", type text}, {"2", type text}, {"3", type text}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Column1"}, "Attribute", "Value"),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Unpivoted Other Columns", {{"Value", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.None), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Value"),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "Value", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.None, false), {"Metal", "Amount"}),
#"Renamed Columns" = Table.RenameColumns(#"Split Column by Delimiter1",{{"Attribute", "Supplier"}, {"Column1", "Material"}})
in #"Renamed Columns"

​

​

在那里，您可以添加更多的行，以便根据分组和取最大值生成所需的表，然后合并供应商名称。

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"1", type text}, {"2", type text}, {"3", type text}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Column1"}, "Attribute", "Value"),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Unpivoted Other Columns", {{"Value", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.None), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Value"),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "Value", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.None, false), {"Metal", "Amount"}),
#"Renamed Columns" = Table.RenameColumns(#"Split Column by Delimiter1",{{"Attribute", "Supplier"}, {"Column1", "Material"}}),
// generate max table
#"Grouped Rows" = Table.Group(#"Renamed Columns", {"Material", "Metal"}, {{"Highest Amount", each List.Max([Amount]), type text}}),
#"Merged Queries" = Table.NestedJoin(#"Grouped Rows",{"Material", "Metal", "Highest Amount"},#"Renamed Columns" ,{"Material", "Metal", "Amount"},"Table0",JoinKind.LeftOuter),
#"Expanded Table1" = Table.ExpandTableColumn(#"Merged Queries", "Table0", {"Supplier"}, {"Supplier"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Table1",{{"Highest Amount", type number}})
in  #"Changed Type1"

​

​