Combine sheets of the same format across different files into as many queries as sheets' formats

[Spigaw]

Hello there,

I have a folder with a file per month, containing multiple sheets inside of it (the contained data is not formatted as a table). These different sheet always come respectively with the same format, each month.

I will have a new file each month.

I need to be able to combine each sheet with its own format from each monthly file, combine it with the others of the same format from the other sheets in a query, and so on with every sheet.

For example, I have the following tables from 2 sample files:

If I take the first row as an example, I need to combine the sheets Absences from both files and combine them in a dedicated query, then do the same for Autres anomalies, then for DEJDED, and so forth.

I firstly tried to use the tables suggested by PBI, but I can't use them all at once:

I tried filtering on the name of the sheet then developing the Table then duplicating the query and do the same for every different kind of table, but PowerBI does not detect the first row as headers and outputs this:

I know I could filter the data on something like "<> [Name of header]", but I want to do something better than tinkering and take the risk of running into errors.

Thanks for any help!

[Spigaw]

I found this tutorial in which Step 11 is used to filter the extra header rows, but I am sure there is a way better solution.

[v-xiaotang · Accepted solution]

Hi @Spigaw 

Thanks for reaching out to us.

How about removing irrelevant columns and then removing duplicate rows? And what are you worried about if you use step 11 in the tutorial?

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.