PowerQuery, merge each row in a table separately

Question

I want to iterate a merging function in PowerQuery over each column. I have a dataset where each column has references to 3 datapoints in another dataset. dataset1 BA005 for example refers to a total of 15 datapoints in the other dataset.The other dataset contains the following: dataset I want my output to look like the following: desired output I can do it manually, but I have over 99 columns in my dataset, so I would rather not.

I've tried to make a function based on this Power Query Applying a Function Across Every Column but I'm very new to coding functions in PowerQuery so I do not know if it's even possible. Any help or references to guides would be helpful as I do not know where to start.

The code of the manual merge is:

#"Merged Queries" = Table.NestedJoin(#"Changed Type", {"BA005"}, Format2, {"Account Number"}, "BA005.", JoinKind.LeftOuter),
#"Expanded BA005." = Table.ExpandTableColumn(#"Merged Queries", "BA005.", {"Total of reporting period", "Total of the comparison period", "Absolute difference"}, {"BA005..Total of reporting period", "BA005..Total of the comparison period", "BA005..Absolute difference"}),
#"Merged Queries2" = Table.NestedJoin(#"Expanded BA005.", {"BA009"}, Format2, {"Account Number"}, "BA009.", JoinKind.LeftOuter),
#"Expanded BA009." = Table.ExpandTableColumn(#"Merged Queries2", "BA009.", {"Total of reporting period", "Total of the comparison period", "Absolute difference"}, {"BA009..Total of reporting period", "BA009..Total of the comparison period", "BA009..Absolute difference"})

without knowing what the other data set looks like, it is unlikely we can help you — horseyride
– horseyride, Commented May 3, 2023 at 11:29
Made some changes to the post to make it more comprehensive, please let me know if I can improve it more. — StijnvdBerg
– StijnvdBerg, Commented May 3, 2023 at 11:58
In the future, please post sample data as text, not images, so we don't have to retype things. This is why the answer is simplified — horseyride
– horseyride, Commented May 3, 2023 at 12:37

horseyride · Accepted Answer · 2023-05-03 12:30:23Z

You can try this powerquery code for Table 1. It works for any number of columns in Table1 and any number of columns in Table2. (It is also a terrible data format, so I would suggest instead stopping after the #"Unpivoted Other Columns1" step)

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Contents"),
#"Merged Queries" = Table.NestedJoin(#"Unpivoted Other Columns", {"Contents"}, Table2, {"Account Number"}, "Table2", JoinKind.LeftOuter),
ColumnsToExpand =List.Distinct(List.Combine(List.Transform(Table.Column(#"Merged Queries", "Table2"), each if _ is table then Table.ColumnNames(_) else {}))),
#"Expand" = Table.ExpandTableColumn(#"Merged Queries", "Table2",ColumnsToExpand ,ColumnsToExpand ),
#"Unpivoted Other Columns1" = Table.UnpivotOtherColumns(Expand, {"Attribute", "Contents", "Account Number"}, "Attribute.1", "Value"),
#"Merged Columns" = Table.CombineColumns(#"Unpivoted Other Columns1",{"Attribute", "Attribute.1"},Combiner.CombineTextByDelimiter(".", QuoteStyle.None),"MergedName"),
#"Removed Columns" = Table.RemoveColumns(#"Merged Columns",{"Contents"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[MergedName]), "MergedName", "Value", List.Sum)
in  #"Pivoted Column"

Collectives™ on Stack Overflow

PowerQuery, merge each row in a table separately

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related