Forum Discussion
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 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 |
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.
6 Replies
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"Thanks Vijay. It works fine..
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.
What is your source? Are you using Power BI Desktop to plot the graph?