Pivoting Data

[Anonymous]

Hi all,

I have a problem where I am trying to pivot the data in a table to get a particular output but I am struggling to find a solution. 

For example, I have a table of data similar to below where each location on a particular date has a high and low value. 

I would like to somehow pivot or transform the data so that I only have one row for each date with the location high and low values displaying as separate columns. 

Any advice or guidence on how this can be achieved would be greatly appreciated. 

[tackytechtom]

Hi @Anonymous ,

How about this:

Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc0xDsIwDIXhq6DMles8OyS9A2LqgqpuMFWqJRbE7YlcQKJk8hs++Z+mEHPP2oOB0IXRlqfVK3wUgg9EiinM3U6ebL3a6kIzJR/C1JDn2+NwsfvipMjnaX7T0sxzrdaBUhJpQ37zGKAkG43/7idepSanA7w/vwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Location = _t, High = _t, Low = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Location", type text}, {"High", type number}, {"Low", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Location Low", each [Location] & " Low"),
    #"Pivoted Column" = Table.Pivot(#"Added Custom", List.Distinct(#"Added Custom"[Location]), "Location", "High", List.Sum),
    #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"Tokyo", "Tokyo High"}, {"London", "London High"}, {"New York", "New York High"}}),
    #"Pivoted Column1" = Table.Pivot(#"Renamed Columns", List.Distinct(#"Renamed Columns"[#"Location Low"]), "Location Low", "Low", List.Sum),
    #"Grouped Rows" = Table.Group(#"Pivoted Column1", {"Date"}, {{"Tokyo High", each List.Max([Tokyo High]), type nullable number}, {"London High", each List.Max([London High]), type nullable number}, {"New York High", each List.Max([New York High]), type nullable number}, {"Tokyo Low", each List.Max([Tokyo Low]), type nullable number}, {"London Low", each List.Max([London Low]), type nullable number}, {"New York Low", each List.Max([New York Low]), type nullable number}})
in
    #"Grouped Rows"

Hope this helps 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[Fowmy · Accepted solution]

@Anonymous 

Hope this is what you are after.

Create a blank Query, go to the Advanced Editor, clear the existing code, and paste the codes give below and follow the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTXdSwo0jUyUtJRCsnPrswH0sYGZsZGYNrI0NBUKVYHVZlPfl5Kfh5Y3sTcFEwbG2Aq80stV4jML8oGK7AwhppnDlVogc1aA0NDIG1kYWFqgqkMbq2RpZGJMUSdIYYqFFuNTExMwQotjUD2xgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Location = _t, #"1.2 High" = _t, #"1.2 Low" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"1.2 High", Int64.Type}, {"1.2 Low", Int64.Type}, {"Date", type date}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Date", "Location"}, "Attribute", "Value"),
    #"Added Custom Column" = Table.AddColumn(#"Unpivoted Other Columns", "Locations", each Text.Combine({Text.Start([Attribute], 4), [Location], Text.Middle([Attribute], 3)}), type text),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom Column",{"Location", "Attribute"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Locations]), "Locations", "Value", List.Sum)
in
    #"Pivoted Column"

[Anonymous]

That is exactly what i was looking for! Thank you. 🙂