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.
I have data in in this format:
column1, column2, column3
0, 1, 1
1, 1, 1
1, 0, 1
1, 0, 1
How can I summarize the data into a new table where the first
column contains the column name from above,
and the second column contains the sum for each column above:
column1, 3
column2, 2
column3, 4
Solved! Go to Solution.
@mahoneypat -- Thanks, Pat...I was able to follow your directions -- table created!
Hello @Anonymous ,
Are you looking for this then please try the below calculation for the new table.
Hello TarunSharma – thanks, this produces a table that looks like this:
col1, col2, col3
1 2 3
I need a table that looks like this
col1, 1
col2, 2
col3, 3
Those steps are all from the Ribbon (no custom M). Here are the steps
- Connect to your data so you have the initial table in power query
- Change the data types of the columns if needed
- Highlight all 3 columns, right click and choose Unpivot Columns
- Rename the Attribute Column to "Column" (or whatever you want)
- Highlight that column and click on Group By on the Home tab of ribbon
- In the pop up, give the new column a name, choose Operation of Sum on the Value column
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
unpivot your table, then group by attribute with aggregation type sum for the value
@lbendlin -- thanks for the note!
I started by importing data using "Get Data" in Power BI...so at this point,
I would need to start with Power Pivot, perform the group by, then import
that data into Power BI...correct? THANKS!
This is most easily done in the query editor with an unpivot followed by a group by step with Sum aggregations. To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAWMDpVidaCCJzIOw0HmGKCqBvFgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", Int64.Type}, {"Column3", Int64.Type}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {}, "Attribute", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Column"}}),
#"Grouped Rows" = Table.Group(#"Renamed Columns", {"Column"}, {{"ColumnSum", each List.Sum([Value]), type number}})
in
#"Grouped Rows"
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@mahoneypat -- Thanks, Pat...I was able to follow your directions -- table created!
@mahoneypat -- thanks for the code!
Unfortunately I'm el-newbo-grande with M code -- how would I integrate this code with an actual table? Thanks!
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 |
---|---|
45 | |
26 | |
22 | |
13 | |
8 |
User | Count |
---|---|
73 | |
51 | |
45 | |
16 | |
12 |