@Sebastiaan,
I’m sure there are many different ways to do this in Power Query, but here’s the approach I took using the UI entirely:
- Referenced the Buildings table and renamed the new table Buildings Aggregated
- Did a Group by of the table created above by Company ID:
- Used a left outer join merge to bring the Total Buildings and Total Capacity fields into the Companies table
- Highlighted the Company, Total Buildings and Total Capacity fields and right clicked on Create Column by Example to create the final column.
Here’s the resulting table:
And the M code used to create it:
let
Source = Excel.Workbook(File.Contents("C:\Users\brjul\Desktop\Buildings Companies.xlsx"), null, true),
Companies_Sheet = Source{[Item="Companies",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Companies_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Company ID", Int64.Type}, {"Company", type text}, {"CEO", type text}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Company ID"}, #"Buildings Aggregated", {"Company ID"}, "Buildings Aggregated", JoinKind.LeftOuter),
#"Expanded Buildings Aggregated" = Table.ExpandTableColumn(#"Merged Queries", "Buildings Aggregated", {"Total Buildings", "Total Capacity"}, {"Total Buildings", "Total Capacity"}),
#"Inserted Merged Column" = Table.AddColumn(#"Expanded Buildings Aggregated", "Company (Builldings | Capacity )", each Text.Combine({[Company], " (", Text.From([Total Buildings], "en-US"), " | ", Text.From([Total Capacity], "en-US"), " )"}), type text)
in
#"Inserted Merged Column"
I hope this is helpful. Full solution file and source data attached, so you can look through the details of the applied steps.
