Hey,
I have a bunch of Excel Workbooks which include uniquely named tables, which all appear on separate sheets. Each of these tables has the same prefix - “fVaccinations2021…”), so in my query I can easily filter the unwanted tables to combine their contents.
How can I also include the sheet name in this query which extracts and combines these tables’ data?
I am surely missing something very obvious here, but can’t for the life of me find the answer.
I have included the Workbook name as a separate column, but each workbook will have multiple sheets & tables, so I can’t use that as a merge of a second query.
Thanks in advance for any help,
Rod
Dear @rodwhiteley,
Thanks for sharing your experience. Would you please upload your data file, so that we can deeply consider your file and your database, and give you proper solutions?
Best
I can’t upload the actual files as these are personal medical information (COVID-19 vaccination records).
Each file has a number of sheets, the ones I have successfully combined all include the tables which are named “fVaccination01…”
In each workbook there are other sheets, for other purposes, as well as some other tables.
Attached are 2 trivial examples of the problem I’d like to solve.
How do I combine the information in the tables, along with the sheet name for each of the tables?
Dummy1.xlsx (11.8 KB) Dummy2.xlsx (11.8 KB)
@rodwhiteley
OK, I see. The two files you uploaded really helped me to understand your situation.
If the number of your tables are not so much, you can add another column in each sheet, determining the name of the sheet either in EXCEL OR Power BI, and then use Append Queries in power BI to combine your data. If you have a huge number of tables, I need to search more to help you.
Best
Thanks, but it’s not going to be possible to add new columns to the existing workbooks/sheets.
How do you think I could extract the sheet names and merge this with the table data?
I can’t figure out the workflow here.
Thanks for any help,
Rod
Hi @rodwhiteley,
Give this a go, set the FileFolderLocation parameter value to the folder contaning these two sample files.
eDNA - Extract and Combine.pbix (38.7 KB)
1 Like
Dear @Melissa
That is great. Is there any video or forum explaining the way you do this?
Hi @sedhosen,
Not exactly matching this requirement but this deals with transforming files from a folder.
https://forum.enterprisedna.co/t/turning-multiple-calendar-type-layouts-into-tabular-format-in-power-bi/8785
Anyway, here are the steps I took.
On the Home tab select: New Source, More, All, Folder
Create a New Parameter for the FileFolderLocation
Make sure “Required” is enabled and it’s set to “Type” Text and set the “Current Value” by pasting in the folder path.
Select: Combine & Transsform Data
Select: whatever you fancy, we’ll modify that in a moment
Go to the “Transform Sample File” query
Delete all other steps but the Source, you should see this:
Filter on Kind=Sheet
Add a Custom Column, you don’t have to change the new column name, with this logic.
Inside the formula bar add this column selection
And finally expand the Custom column with those sideward arrows, deselect “Use original column name as prefix” and press OK
Go to the “Query1” query to view the results
Remove the “Changed Type” step and set appropriate data types for each column
I hope this is helpful.
3 Likes
Thanks for your thorough description.
1 Like
Hi @rodwhiteley,
I think you did not miss anything here, it is just that you can either access the table content preserving the table name or access the sheet content preserving the sheet name.
Your problem is that there is no relation between the sheet names and the table names, there is no common name substring which you could use to match sheet and table name.
that means you are best served using the “fVaccinations…" table names to filter down to just the data you need.
If you have the table name it is very easy for you to find the table in the Excel workbook, by selecting the table name from the dropdown list on the left side of the function bar. So the benefit of having the additional sheet name would be neglectable.
1 Like
Hi @Matthias,
Oeps… I totally missed that, and you are 100% right. 
@rodwhiteley,
You can consider prefixing all your table names to indicate the sheetname that they originate from and split that text in PQ. But again Matthias rightfully states that the Name box (to the left of the formula bar) in Excel will allow you quick navigation to any table object within your workbook.
Thanks for sharing Matthias!
Thanks for everyone’s help, @Melissa 's approach worked a treat.
The additional wrinkle I had was pulling this from a Sharepoint site, so inserting a filter for filenames and filetypes earlier in the query solved that.
Thanks again, you guys have helped get us all closer to COVID-zero 
Rod
1 Like
I had thought of it, but was originally assured there would only be one sheet per day for the vaccination list.
Then an end user decided to make separate sheets for different groups (in the same workbook), and then they wanted this information recorded and reported as well.
