Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
152 | |
120 | |
102 | |
87 | |
68 |