Pivot Columns Excel/ Power Query

[ale259]

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!

[Anonymous · Accepted solution]

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

[ale259]

Thank you. This is a great answer!

[Omid_Motamedise]

Hi @ale259 

Writy click on Hourly column, and pick unpivot other columns.

[dufoq3]

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
    Reordered

v2

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
    Transformed

[ale259]

Hi, 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]),

[dufoq3]

Read note below my posts.