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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
under
New Member

Transpose tables before combining

I have a folder with a growing number of files. How can I transpose each file, then combine them? Thanks!

 

File 1:

Date1/1/20231/2/2023
Amount$50$60

 

File 2:

Date1/3/20231/4/2023
Amount$70$80

 

Desired end result:

DateAmount
1/1/2023$50
1/2/2023$60
1/3/2023$70
1/4/2023$80
1 ACCEPTED SOLUTION
wdx223_Daniel
Super User
Super User

Table.Combine(List.Transform(Folder.Files("your folder")[Content],each Table.PromoteHeaders(Table.Transpose(Excel.Workbook(_)[Data]{0}))))

View solution in original post

2 REPLIES 2
wdx223_Daniel
Super User
Super User

Table.Combine(List.Transform(Folder.Files("your folder")[Content],each Table.PromoteHeaders(Table.Transpose(Excel.Workbook(_)[Data]{0}))))

ppm1
Solution Sage
Solution Sage

Use the Combine & Transform feature and in the transform sample query select your non-date column(s), right click and choose "Unpivot other columns".

 

Pat

 

Microsoft Employee

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors