Help with M-code to add columns with aggregated information

I would like to ask for some help with the need for extending my dataset in Power Query.

My data model consists of the following two tables describing the buildings for a few companies. I would like to extend the company table with three more columns, counting the number of buildings and adding up their capacity. Like the columns shown below in grey.

Can anyone explain the M-code needed to add these columns properly in my query? I tried to determine No. of buildings with COUNTIF, but couldn’t make it work.

Thanks for all useful suggestions!


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:

  1. Referenced the Buildings table and renamed the new table Buildings Aggregated
  2. Did a Group by of the table created above by Company ID:

  1. Used a left outer join merge to bring the Total Buildings and Total Capacity fields into the Companies table
  1. 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:

    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)
    #"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.

1 Like

Thanks Brian. This was the direction I was looking for.

When the no. of buildings of the capacity exceeds 999, it would be nice to have a thousand separator inserted in the merged column. Any idea how that goes?



Power Query has limited formatting options. I think the best way to handle this is after you hit Close and Apply, highlight the column you want to format, go to Column Tools, and apply the desired format mask (see screenshot below). When you refresh your data, that format mask should be applied automatically to the new data as well.

  • Brian