Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Anonymous
Not applicable

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 below where each location on a particular date has a high and low value. 

 

davesab_0-1653332969637.png

 

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. 

 

davesab_1-1653333454115.png

 

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

 

 

1 ACCEPTED SOLUTION
Fowmy
Super User
Super User

@Anonymous 

Hope this is what you are after.

Fowmy_0-1653334738917.png


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"

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

3 REPLIES 3
tackytechtom
Super User
Super User

Hi @Anonymous ,

 

How about this:

tomfox_0-1653336106128.png

 

 

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/



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

Fowmy
Super User
Super User

@Anonymous 

Hope this is what you are after.

Fowmy_0-1653334738917.png


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"

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Anonymous
Not applicable

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

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.