Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to 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 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 |
Great thanks! It works like a charm. And I'm even learning something new 🙂
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |