Create new table with averages of columns from other table

[carinat]

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:

A	B	C
1	43	7
5	65	5
3	3	4
6	5	854
3	8	4

I would like to obtain the following table:

Category	Average
A	3,6
B	24,8
C	174,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!

[pqian · Accepted solution]

@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

A	3.6
B	24.8
C	174.8

[carinat]

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