在功率查询中复制Excel公式逻辑

Question

如果这不属于这里，请告诉我！或者如果需要更多的信息

在此处提供示例数据文件的链接-https://www.dropbox.com/s/mz3f24ciby7qthv/example%20PBI%20Community.xlsx?dl=0

上下文：第18至25行包含国家/地区客户分组谷物的预测数据。。。并且我们需要分配给Label1和Label2的细粒度第2行至第17行包含具有我们所需粒度的2020年数据，因此我们使用2020年数据为2021年数据创建一个分配基数，该基数由第一列和第J列中的公式完成

公式/逻辑解释：

• 这是在一个"；数据库"；总体安排即使添加了新列，它也需要保持这种状态
• FormulaPart1是"；基本图形"；基于N列中索引键的数据
• FormulaPart2是"；基本图形"；基于O列中索引键的数据
• 公式Part3是"；待分配"；基于P列中索引键的数据

希望一切都清楚。。。为了效率和最大限度地减少错误，我想把这个逻辑转移到PQ因此，任何正确方向的指导都非常有用：(我使用的真实数据集要大得多，在excel表中包含所有这些公式和索引键列是有问题的：(

感谢您的指导！：(

Answer 1

假设范围A1:H25命名为Table1，这似乎可以实现

//assumes range A1:H25 from sample data is loaded as named range Table1
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Label1", Int64.Type}, {"Label2", type text}, {"Type", type text}, {"Country", type text}, {"Customer", type text}, {"Grouping", type text}, {"Original Value", Int64.Type}}),
// add index for re-sorting later
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
// pull Base Figure rows This starts us with Part 1 of formula
#"Filtered Rows" = Table.SelectRows(#"Added Index", each ([Type] = "Base Figure")),
// formula part 2
#"Grouped Rows" = Table.Group(#"Filtered Rows", {"Country", "Customer", "Grouping"}, {{"Original Value 2", each List.Sum([Original Value]), type number}}),
#"Merged Queries" = Table.NestedJoin(#"Filtered Rows",{"Country", "Customer", "Grouping"},#"Grouped Rows" ,{"Country", "Customer", "Grouping"},"Table2",JoinKind.LeftOuter),
#"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"Original Value 2"}, {"Original Value 2"}),
// formula part 3
#"Filtered Rows2" = Table.SelectRows(#"Added Index", each ([Type] = "To be Allocated")),
#"Grouped Rows2" = Table.Group(#"Filtered Rows2", {"Country", "Customer", "Grouping"}, {{"Original Value", each List.Sum([Original Value]), type number}}),
#"Merged Queries1" = Table.NestedJoin(#"Expanded Table2",{"Country", "Customer", "Grouping"},#"Grouped Rows2",{"Country", "Customer", "Grouping"},"Table2",JoinKind.LeftOuter),
#"Expanded Table1" = Table.ExpandTableColumn(#"Merged Queries1", "Table2", {"Original Value"}, {"Original Value 3"}),
// Add math for new column based on Formula / Formula2 * Formula3
#"Added Custom" = Table.AddColumn(#"Expanded Table1", "Custom", each [Original Value]/[Original Value 2]*[Original Value 3]),
// Replace year with following year, replace Base Figure with Allocated Figure, re-sort on original sort
#"Replaced Value" = Table.ReplaceValue(#"Added Custom", #"Filtered Rows"{0}[Year], #"Filtered Rows2"{0}[Year],Replacer.ReplaceValue,{"Year"}),
#"BlankOriginalValue" = Table.TransformColumns(#"Replaced Value",{{"Original Value", each null}}),
#"Replaced Value1" = Table.ReplaceValue(#"BlankOriginalValue","Base Figure","Allocated Figure",Replacer.ReplaceText,{"Type"}),
#"Removed Columns" = Table.RemoveColumns(#"Replaced Value1",{"Original Value 2", "Original Value 3"}),
#"Sorted Rows" = Table.Sort(#"Removed Columns",{{"Index", Order.Ascending}}),
//combine new Allocated Figure data with original range and remove extra columns
#"Combine" = Table.Combine({#"Added Index" , #"Sorted Rows" }),
#"Added Custom1" = Table.AddColumn(Combine, "Forumla", each if [Custom]=null then [Original Value] else [Custom]),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Custom", "Index"})
in #"Removed Columns1"

第二个版本应该适用于基准年之外的多个待分配年份。省略注释。有关的解释，请参阅第一版

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Label1", Int64.Type}, {"Label2", type text}, {"Type", type text}, {"Country", type text}, {"Customer", type text}, {"Grouping", type text}, {"Original Value", Int64.Type}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
#"Filtered Rows" = Table.SelectRows(#"Added Index", each ([Type] = "Base Figure")),
#"Grouped Rows" = Table.Group(#"Filtered Rows", {"Country", "Customer", "Grouping"}, {{"Original Value 2", each List.Sum([Original Value]), type number}}),
#"Filtered Rows2" = Table.SelectRows(#"Added Index", each ([Type] = "To be Allocated")),
#"Merged Queries3" = Table.NestedJoin(#"Filtered Rows2",{"Country", "Customer", "Grouping"},#"Filtered Rows" ,{"Country", "Customer", "Grouping"},"Table3",JoinKind.LeftOuter),
#"Removed Columns1" = Table.RemoveColumns(#"Merged Queries3",{"Label1", "Label2"}),
#"Expanded Table3" = Table.ExpandTableColumn(#"Removed Columns1", "Table3", {"Label1", "Label2", "Original Value"}, {"Label1", "Label2", "Formula1"}),
#"Merged Queries" = Table.NestedJoin(#"Expanded Table3" ,{"Country", "Customer", "Grouping"},#"Grouped Rows" ,{"Country", "Customer", "Grouping"},"Table3",JoinKind.LeftOuter),
#"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table3", {"Original Value 2"}, {"Formula2"}),
#"Grouped Rows1" = Table.Group(#"Filtered Rows2" , {"Year", "Country", "Customer", "Grouping"}, {{"Part3", each List.Sum([Original Value]), type number}}),
#"Merged Queries1" = Table.NestedJoin(#"Expanded Table2",{"Year", "Country", "Customer", "Grouping"},#"Grouped Rows1",{"Year", "Country", "Customer", "Grouping"},"Table3",JoinKind.LeftOuter),
#"Expanded Table1" = Table.ExpandTableColumn(#"Merged Queries1", "Table3", {"Part3"}, {"Formula3"}),
#"Added Custom" = Table.AddColumn(#"Expanded Table1", "New", each [Formula1]/[Formula2]*[Formula3]),
#"Sorted Rows" = Table.Sort(#"Added Custom",{{"Index", Order.Ascending}, {"Label1", Order.Ascending}}),
#"Replaced Value" = Table.ReplaceValue(#"Sorted Rows","To be Allocated","Allocated Figure",Replacer.ReplaceText,{"Type"}),
#"BlankOriginalValue" = Table.TransformColumns(#"Replaced Value",{{"Original Value", each null}}),
#"Removed Columns" = Table.RemoveColumns(BlankOriginalValue,{"Index", "Formula2", "Formula3", "Formula1"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Year", "Label1", "Label2", "Type", "Country", "Customer", "Grouping", "Original Value", "New"}),
#"Combine" = Table.Combine({#"Changed Type",#"Reordered Columns"  })
in #"Combine"