Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Pivoting Data

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

    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"

     

tackytechtom's avatar
tackytechtom
Most Valuable Professional
4 years ago

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/