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
carinat
Advocate I
Advocate I

Create new table with averages of columns from other table

Hi,

 

I'm trying to make a new table containing two columns. Column1 should have categories which represent column headers from another table and Column2 should contain the corresponding average of the column.

 

Example: Current table looks like:

ABC
1437
5655
334
65854
38

4

I would like to obtain the following table:

CategoryAverage
A3,6
B24,8
C174,8

 

I'm not very experienced so I hope I'm just overlooking something. I've tried to think in both advanced queries on my original source and calculated tables but didn't come up with anything.

 

Any help would be appreciated!

1 ACCEPTED SOLUTION
pqian
Employee
Employee

@carinat what you are describing is a unpivot and a group by with average:

 

Assuming your original table is "Table 1":

let
    Source = Table1,
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Columns", {"Attribute"}, {{"Average", each List.Average([Value]), type number}})
in
    #"Grouped Rows"

Gives:

AttributeAverage

A3.6
B24.8
C174.8

View solution in original post

2 REPLIES 2
pqian
Employee
Employee

@carinat what you are describing is a unpivot and a group by with average:

 

Assuming your original table is "Table 1":

let
    Source = Table1,
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Columns", {"Attribute"}, {{"Average", each List.Average([Value]), type number}})
in
    #"Grouped Rows"

Gives:

AttributeAverage

A3.6
B24.8
C174.8

Great thanks! It works like a charm. And I'm even learning something new 🙂

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.