Forum Discussion
Pivot Columns Excel/ Power Query
Hello,
I have the dateset as below (with fictional number)
| Hourly | A City | B City |
| 12:00 AM | 1000 | 200 |
| 1:00 AM | 5000 | 400 |
| 2:00 AM | 7000 | 6000 |
| 3:00 AM | 8000 | 7000 |
| 4:00 AM | 10000 | 800 |
And I want to change the data into this:
| City | Hourly | Visits |
| A City | 12:00 AM | 1000 |
| A City | 1:00 AM | 5000 |
| A City | 2:00 AM | 7000 |
| A City | 3:00 AM | 8000 |
| A City | 4:00 AM | 10000 |
| B City | 12:00 AM | 200 |
| B City | 1:00 AM | 400 |
| B City | 2:00 AM | 6000 |
| B City | 3:00 AM | 7000 |
| B City | 4:00 AM | 800 |
Can you give me any advice on how to do so? Thank you!
Hi ale259,
Thank you for reaching out to Microsoft Fabric Community Forum.
Load the data into Power BI and open Power Query Editor by selecting Transform Data.
You can proceed step by step in Power Query to achieve your expected output.Ensure the date column is converted to a time-only format. To do this, select the column and choose Time from the Data Type dropdown. This will display only the hourly values.
Select only the "Hourly" column, then go to Transform → Unpivot Other Columns. This will convert "A City" and "B City" into two columns: "Attribute" and "Value".
Rename "Attribute" to "City" and "Value" to "Visits".
Sort it from the filters dropdown in ascending or descending order.
Here is the output.
If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
Regards,
Vinay Pabbu
6 Replies
Hi ale259,
Thank you for reaching out to Microsoft Fabric Community Forum.
Load the data into Power BI and open Power Query Editor by selecting Transform Data.
You can proceed step by step in Power Query to achieve your expected output.Ensure the date column is converted to a time-only format. To do this, select the column and choose Time from the Data Type dropdown. This will display only the hourly values.
Select only the "Hourly" column, then go to Transform → Unpivot Other Columns. This will convert "A City" and "B City" into two columns: "Attribute" and "Value".
Rename "Attribute" to "City" and "Value" to "Visits".
Sort it from the filters dropdown in ascending or descending order.
Here is the output.
If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
Regards,
Vinay Pabbu
Thank you. This is a great answer!
Hi ale259, chek this:
Output
v1
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjSyMjBQcPRV0lEyNDAwAFJGQDJWBygDlzCFSJhAJRA6zCESZgZQGWO4jAVExhwmY4JiiwFEhVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Hourly = _t, #"A City" = _t, #"B City" = _t]), Unpivoted = Table.UnpivotOtherColumns(Source, {"Hourly"}, "City", "Visits"), SortedRows = Table.Sort(Unpivoted,{{"City", Order.Ascending}, {"Hourly", Order.Ascending}}), Reordered = Table.ReorderColumns(SortedRows,{"City", "Hourly", "Visits"}) in Reorderedv2
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjSyMjBQcPRV0lEyNDAwAFJGQDJWBygDlzCFSJhAJRA6zCESZgZQGWO4jAVExhwmY4JiiwFEhVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Hourly = _t, #"A City" = _t, #"B City" = _t]), Transformed = Table.FillDown(Table.Combine(List.Transform(List.Skip(Table.ColumnNames(Source)), each Table.FromColumns({{_}} & {Table.Column(Source, "Hourly"), Table.Column(Source, _)}, {"City", "Hourly", "Visits"}))), {"City"}) in TransformedHi, do I need this part if I have this data range already? I don't qute understand what this long code mean. If you could explain it, it would be great. Thank you!
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjSyMjBQcPRV0lEyNDAwAFJGQDJWBygDlzCFSJhAJRA6zCESZgZQGWO4jAVExhwmY4JiiwFEhVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Hourly = _t, #"A City" = _t, #"B City" = _t]),Read note below my posts.
Hi ale259
Writy click on Hourly column, and pick unpivot other columns.
