Get Column Table Summary from Same Column Header Name

[Anonymous]

Hello,

Good day! Pls your support to assist how can i get the summary from table column header name to another table row name value.

This should be my result table.

Month	Total
Jan Target	6
Feb Target	15
March Target	24

This is the main table i have.

PO ID	Jan Target	Feb Target	Mar Target
123-A	1	4	7
456-B	2	5	8
789-C	3	6	9

[Anonymous]

Thanks Vijay. It works fine.. 

Spoiler

May i know how could i able to use the existing table as a source.

Also, how could i plot the month as a bar chart with Jan, Feb.. as month in correct order.. 

Thanks again.

[Vijay_A_Verma]

What is your source? Are you using Power BI Desktop to plot the graph?

[Anonymous]

yes.. im using the sharepoint list as my data source.

[Vijay_A_Verma · Accepted solution]

Use this link to connect to Sharepoint list - https://docs.microsoft.com/en-us/power-query/connectors/sharepointlist 

In the last step, select Transform which will take you to the PQ. Here, Home - Advanced Editor - Copy the 2nd line which starts with Source and paste into a notepad

Now remove everything from advanced editor.

Copy my code there.

Replace source line from notepad.

Now OK.

#"Changed Type", you will need to remove as it contains only Jan to Mar  and do your own Changed Type in the Power Query after Source step.

[Anonymous]

Great. Thanks for your support.

[Vijay_A_Verma]

Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code (later on when you use the query, you will have to change the source)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("HcixEQAgCATBXj6WQBTFUC2Dof82/DHY4C4CVZtsFFTqNJEl0G3IYSkZ+b/Tl1xWo0ELmQ8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"PO ID" = _t, #"Jan Target" = _t, #"Feb Target" = _t, #"Mar Target" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"PO ID", type text}, {"Jan Target", Int64.Type}, {"Feb Target", Int64.Type}, {"Mar Target", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"PO ID"}, "Attribute", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Attribute"}, {{"Total", each List.Sum([Value]), type number}}),
    #"Renamed Columns" = Table.RenameColumns(#"Grouped Rows",{{"Attribute", "Month"}})
in
    #"Renamed Columns"