Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Get Column Table Summary from Same Column Header Name

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 T...
Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    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.

Vijay_A_Verma's avatar
Vijay_A_Verma
Most Valuable Professional
4 years ago

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"