Dear Colleagues,
I have the following Issue when I try to append more than one Table together in a single Query.
A little to my data:
I become each quarter 4 Excel Tables with the following format (example):
| Product Titel | January | February | March | Total |
| Product A | 12 | 14 | 15 | 41 |
| Total | 12 | 14 | 15 | 41 |
The Month columns would then be pivoted, and become a Month and Value Columns.
The thing about these tables is, the titel from column A changes from Excel to Excel. Whenever I try to combine the tables, I get back the first table and then an "Error" Row at the end of the table.
I receive the Data each quarter in a SharePoint Folder. I have been naming the excels with the following format: YearMonth_ProductCategory
Where YearMonth is the current Year + Last Year Month of the Quarter + 1. The Product Category will be needed later as each product in the excels will have a parent Category with the Name of the Product Category (in the file name).
So my questions really are:
How can I combine the 4 tables, so that the column names don't affect the merged query (therefore not getting an error)?
How would it be easier to automate, data tables for each quarter, or an ever expanding table, updated each quarter horizontally adding the months?
I uploaded the input data on we transfer as well as the expected result: https://we.tl/t-JlLTGM6T5s
Thank you in advance!
Regards,
Robert
