Forum Discussion
Multiple sheets with the same format
paste the code below in the query editor
replace file path with your file
then expand the tables using two arrows icon next to the Data column
let
Source = Excel.Workbook(File.Contents("C:\Users\stach_000\Desktop\data.xlsx"), null, true),
#"Removed Other Columns" = Table.SelectColumns(Source,{"Name", "Data"})
in
#"Removed Other Columns"it would still require some cleaning, but it's quite fast
alternatively if sheets are not in a single file you can create function where filename would be parameter, it appends data automaticaly then
You need 1 function where you perform all your transformation-steps that you then apply in an add-column step. You just have to expand that column then. No need for a merge or append. It mimics the From-Folder-method, just that you don't have to split up your existing xls-sheet into separate files:
Excel.Workbook(File.Contents(..YourFilePath..), null, true)
This will return the content of your Excel-file in a format where the content of every sheet sits in one row:
You then add a column where you pass the [Data]-column to your function - that should be it.
... oops - overlap with post from Stachu who suggested the same method
Anyway - put your function into a new column :-)
Thank you all for the great suggestions.
I've tried Stachu/Imke's suggestion and (of course) needs some work, but appears to be what is the right way in my specific case.