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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors