Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin 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.
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
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/
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
15 | |
11 | |
8 | |
8 | |
7 |
User | Count |
---|---|
15 | |
13 | |
9 | |
7 | |
6 |