how to summarize several columns into a new table

[Anonymous]

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

[Anonymous]

Hello @Anonymous ,
Are you looking for this then please try the below calculation for the new table.

Summarize table =

SUMMARIZE(

'Summarize',

"Column 1", SUM('Summarize'[Column1]),

"Column 2", SUM('Summarize'[Column2]),

"Column 3", SUM('Summarize'[Column3])

)

[Anonymous]

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

[mahoneypat]

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

[lbendlin]

unpivot your table, then group by attribute with aggregation type sum for the value

[Anonymous]

@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!

[mahoneypat]

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

[Anonymous · Accepted solution]

@mahoneypat -- Thanks, Pat...I was able to follow your directions -- table created!

[Anonymous]

@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!