Reply
ale259
Helper I
Helper I

Transpose data

‎03-18-2025 01:05 PM

Hi, 

 

I am constantly have to transpose several combined data from rows to columns, would anyone share with me a way to automate this step? I include the original file, and the file I have done manually transpose. Any insights given would be really appreciated. Thank you! 

 

This is the original file. All number are fiction.

 

 Catawba BROOKFORD Long ViewMaiden 
12:00 AM5962224882308717332106
1:00 AM5216823444300474318413
2:00 AM4981022999284503311975
3:00 AM4883622810275210311887
4:00 AM5084322506274961309800
5:00 AM5396622446284749335413
6:00 AM6631422996315933416965
7:00 AM9182328273371370555093
8:00 AM10984531579416492617615
9:00 AM12869036008479083714802
10:00 AM14675444975550097765870
11:00 AM15518245830605371802899
12:00 PM15644942320636233794496
1:00 PM15695239533595909730753
2:00 PM16178240682583663735026
3:00 PM15727641274592827655151
4:00 PM14106242670589399663802
5:00 PM14733443483592857744612
6:00 PM16335244215605375802784
7:00 PM16366543416571766734993
8:00 PM14609841127498608578622
9:00 PM11749037188407164454785
10:00 PM9188833271350620368028
11:00 PM7326827624320509331855

 

This is what I wanted:

Catawba12:00 AM59622
Catawba1:00 AM52168
Catawba2:00 AM49810
Catawba3:00 AM48836
Catawba4:00 AM50843
Catawba5:00 AM53966
Catawba6:00 AM66314
Catawba7:00 AM91823
Catawba8:00 AM109845
Catawba9:00 AM128690
Catawba10:00 AM146754
Catawba11:00 AM155182
Catawba12:00 PM156449
Catawba1:00 PM156952
Catawba2:00 PM161782
Catawba3:00 PM157276
Catawba4:00 PM141062
Catawba5:00 PM147334
Catawba6:00 PM163352
Catawba7:00 PM163665
Catawba8:00 PM146098
Catawba9:00 PM117490
Catawba10:00 PM91888
Catawba11:00 PM73268
BROOKFORD12:00 AM24882
BROOKFORD1:00 AM23444
BROOKFORD2:00 AM22999
BROOKFORD3:00 AM22810
BROOKFORD4:00 AM22506
BROOKFORD5:00 AM22446
BROOKFORD6:00 AM22996
BROOKFORD7:00 AM28273
BROOKFORD8:00 AM31579
BROOKFORD9:00 AM36008
BROOKFORD10:00 AM44975
BROOKFORD11:00 AM45830
BROOKFORD12:00 PM42320
BROOKFORD1:00 PM39533
BROOKFORD2:00 PM40682
BROOKFORD3:00 PM41274
BROOKFORD4:00 PM42670
BROOKFORD5:00 PM43483
BROOKFORD6:00 PM44215
BROOKFORD7:00 PM43416
BROOKFORD8:00 PM41127
BROOKFORD9:00 PM37188
BROOKFORD10:00 PM33271
BROOKFORD11:00 PM27624
Long View12:00 AM308717
Long View1:00 AM300474
Long View2:00 AM284503
Long View3:00 AM275210
Long View4:00 AM274961
Long View5:00 AM284749
Long View6:00 AM315933
Long View7:00 AM371370
Long View8:00 AM416492
Long View9:00 AM479083
Long View10:00 AM550097
Long View11:00 AM605371
Long View12:00 PM636233
Long View1:00 PM595909
Long View2:00 PM583663
Long View3:00 PM592827
Long View4:00 PM589399
Long View5:00 PM592857
Long View6:00 PM605375
Long View7:00 PM571766
Long View8:00 PM498608
Long View9:00 PM407164
Long View10:00 PM350620
Long View11:00 PM320509
Maiden12:00 AM332106
Maiden1:00 AM318413
Maiden2:00 AM311975
Maiden3:00 AM311887
Maiden4:00 AM309800
Maiden5:00 AM335413
Maiden6:00 AM416965
Maiden7:00 AM555093
Maiden8:00 AM617615
Maiden9:00 AM714802
Maiden10:00 AM765870
Maiden11:00 AM802899
Maiden12:00 PM794496
Maiden1:00 PM730753
Maiden2:00 PM735026
Maiden3:00 PM655151
Maiden4:00 PM663802
Maiden5:00 PM744612
Maiden6:00 PM802784
Maiden7:00 PM734993
Maiden8:00 PM578622
Maiden9:00 PM454785
Maiden10:00 PM368028
Maiden11:00 PM331855
Labels:
Message 1 of 9
305 Views
0
1 ACCEPTED SOLUTION
V-yubandi-msft
Community Support
Community Support

‎03-19-2025 02:06 AM

Hi @ale259 ,

 

Thank you for your inputs @Cookistador  @FreemanZ @Deku .

The Unpivot Columns feature in Power Query is indeed the most suitable approach for this transformation.

1️. Select the first column (Time) and unpivot the remaining columns using Transform → Unpivot Columns in Power Query.
2️. This will restructure the table into a City | Time | Value format.
3️.  If necessary, rename the columns for clarity and sort by city name for better organization.

 

FYI:

 

Vyubandimsft_0-1742375032852.png

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

Message 5 of 9
187 Views
8 REPLIES 8
V-yubandi-msft
Community Support
Community Support

‎03-27-2025 11:38 AM

Hi @ale259 ,

Has your issue been resolved, or do you require any further information? Your feedback is valuable to us. If the solution was effective, please mark it as 'Accepted Solution' to assist other community members experiencing the same issue.

Message 9 of 9
63 Views
0
V-yubandi-msft
Community Support
Community Support

‎03-24-2025 02:22 AM

Hi @ale259 ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank You.

Message 8 of 9
110 Views
0
ale259
Helper I
Helper I

‎03-19-2025 05:43 AM

 Thank you all!

Message 6 of 9
174 Views
0
V-yubandi-msft
Community Support
Community Support
In response to ale259

‎03-20-2025 12:32 AM

If your issue is resolved, please consider marking it as the Accepted solution. This helps other community members who may face a similar issue find the solution more easily.

 

Regards,

Yugandhar.

Message 7 of 9
141 Views
0
V-yubandi-msft
Community Support
Community Support

‎03-19-2025 02:06 AM

Hi @ale259 ,

 

Thank you for your inputs @Cookistador  @FreemanZ @Deku .

The Unpivot Columns feature in Power Query is indeed the most suitable approach for this transformation.

1️. Select the first column (Time) and unpivot the remaining columns using Transform → Unpivot Columns in Power Query.
2️. This will restructure the table into a City | Time | Value format.
3️.  If necessary, rename the columns for clarity and sort by city name for better organization.

 

FYI:

 

Vyubandimsft_0-1742375032852.png

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Message 5 of 9
188 Views
FreemanZ
Super User
Super User

‎03-18-2025 11:17 PM

hi @ale259 ,

 

this is standard unpivot.

Right click the header of first column and choose "Unpivot Other Columns"

then you shall get what you want. 

Message 4 of 9
210 Views
Deku
Community Champion
Community Champion

‎03-18-2025 03:55 PM 
let
    Source = yourSource,
    unpivot = Table.UnpivotOtherColumns(Source , {"Time"}, "Attribute", "Value")
in
    unpivot

Did I answer your question?
Please help by clicking the thumbs up button and mark my post as a solution!
Message 3 of 9
248 Views
Cookistador
Solution Supplier
Solution Supplier

‎03-18-2025 01:19 PM

To do that,
Go in Power Query and select the 4 columns 

Cookistador_0-1742329052956.png

 

Then, go in Transform --> Unpivot columns and select Unpivot Columns

Cookistador_1-1742329116496.png

And tadam:

Cookistador_2-1742329160220.png

 

 

You can sort by name if you need 🙂

Message 2 of 9
287 Views
avatar user

Helpful resources

Announcements
March PBI video - carousel

Power BI Monthly Update - March 2025

Check out the March 2025 Power BI update to learn about new features.

March2025 Carousel

Fabric Community Update - March 2025

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

Stop
View All
Recommendations
Top Solution Authors (Last Month)
View All
Top Kudoed Authors (Last Month)
View All