Combine multiple files with different sheet names and different sheet index

Question

I have a folder with one .xslm for each day of the month (named February 1, February 2, ..., February X), within each workbook, there's one sheet named after the day of the month in the same manner as the file names (February 1, February 2, ..., February X). The structure of the sheets is identical for all of them. The sheets have different index numbers, so using the "Source{0}[Data]" method is not working.

Is there a way to use something like Text.Contains or similar to pull all the worksheets ?

I'm not very familiar with Power Query code, but conceptually this is what I would like to do:

Where XYZ = "February".

Thank you!

wdx223_daniel · Answer

=Table.SelectRows(Origen,each Text.Contains([Item],"February") and [Kind]="Sheet")

wdx223_daniel · Answer

this error came from your file have not the column name of "Column1"
strongly suggest you never use fixed column name in your code, unless you can keep the name exists in all your source files.

lf2606 · Answer

Thanks for the reply, after running that query for the example file, I got this outcome:

But I get this error for the other query:

Would you happen to know what's causing it?

lf2606 · Answer

I'm sorry for making such basic questions, but I'm not able to resolve it:&nbsp;&nbsp;&nbsp;If I start from scratch and pull the files from the folder and click on combine and transform, then select the desired sheet from example file this is what it looks like:&nbsp;&nbsp;&nbsp;The key error related to the sheet name at the bottom, but the query at the top shows that code for creating column names automatically.&nbsp;&nbsp;If I change the query from the navigation step at the Transform sample file to the one you kindly provided it looks like this:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;and at the final load I see this:&nbsp;&nbsp;&nbsp;And I am unable to load the data.&nbsp;&nbsp;&nbsp;I didn't exactly hardcode those column names, I believe they were created automatically from the moment I loaded the files from the folder.&nbsp;&nbsp;How can I go around this so I can get the desired result of loading all data from the desired sheets?&nbsp;Thank you!

lf2606 · Answer

Would anybody happen to know what would be the appropriate next steps to solve this issue?&nbsp;&nbsp;Thanks!

Forum Discussion

Combine multiple files with different sheet names and different sheet index

6 Replies

Related content

Different schema into different destination

Date difference with multiple criteria

How to avoid people creating multiple times the same table in different workspaces

Multiple PDFs Different Tables

Page Level filter for different groups