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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
cthardwik
Frequent Visitor

Automatic Data Transformation

I have lots of different spreadsheets that are all the same format, but with different data in them. At the moment I have to upload each speadsheet individualy, carry out the transformations of the data in query editor and then appended them all together. Is there a way this can be done automatically?

 

For example is there a way with using Get Data from Folders?  Can Power BI select all the specific tables in the spreadsheets and then carry out a pre deturmined set of transformations on the data? And then perhaps even append them all together?

 

(All the spreadsheets have a lot of tables in that I am not interested in, but I only upload the same 3 tables from each spreadsheet, which are all in the same format)

 

The transformations I carry out on each table are:

 

let
    Source = Excel.CurrentWorkbook(){[Name="Table9"]}[Content]
,
   #"Transposed Table" = Table.Transpose(Source),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table"),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"Date", "Status"}, "Attribute", "Value"),
    #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Columns",{{"Date", type date}})
in
    #"Changed Type"

 

So basically, transposed table, promote first row as headers, unpivot a set amount of columns, change type of one of the rows to date.

 

Thanks

1 REPLY 1
Anonymous
Not applicable

If you have, or can create, a table of the workbooks you want (with the same Table in each) then you can process them all with your transformations set up as a function - e.g. see https://www.powerpivotpro.com/2015/07/consolidated-worksheets-with-power-query/

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

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

June 2025 community update carousel

Fabric Community Update - June 2025

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