Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
abedkhooli
Frequent Visitor

Aggregate columns from one table into rows of another table

PBIQ-Eggs.png

I have dynamic data in a few columns in Table2 (see image). In Table 1 (created manually - enter data), each row represents one of the columns in Table 2. I need to replace the placeholders in the 3rd column of Table 1 with the average of the values in the indicated columns from Table 2 (the two tables are not related). Tried conditional column but it does not accept formulas in the value field. Thought about Table.FromRows and got an error (not sure if formula or reference related). Any ideas? Hopefully it is simple, not an expert!

1 ACCEPTED SOLUTION
MarcelBeug
Community Champion
Community Champion

Easiest is to add the averages as a new column.

So, if you delete the column with placeholders from Table1, you can add it in your query on the tab "Add Column", option "Custom Column" with name AvNO2

and formula

= List.Average(Table.Column(Table2,[EggName]&"NO2"))

 

or, if you need to catch any errors:

 

= try List.Average(Table.Column(Table2,[EggName]&"NO2")) otherwise null

 

 

Specializing in Power Query Formula Language (M)

View solution in original post

3 REPLIES 3
MarcelBeug
Community Champion
Community Champion

Easiest is to add the averages as a new column.

So, if you delete the column with placeholders from Table1, you can add it in your query on the tab "Add Column", option "Custom Column" with name AvNO2

and formula

= List.Average(Table.Column(Table2,[EggName]&"NO2"))

 

or, if you need to catch any errors:

 

= try List.Average(Table.Column(Table2,[EggName]&"NO2")) otherwise null

 

 

Specializing in Power Query Formula Language (M)

Thank you @MarcelBeug - this works very well.

Although naming was not intended for calculation purposes, your formula made it elegant and simple. Will add my other columns in the same way.

Thank you, worked for me!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.