Combine files from folder with some different columns

[foyiq]

I have just read the following article: http://www.excelguru.ca/blog/2015/02/25/combine-multiple-excel-workbooks-in-power-query/

Now the problem is to adapt this solution to my case, which is as follows.
I have multiple files in a folder. The issue is that I have not a single row, which defines headers, but three of them. If I promote only the first row to headers the column names will not be consistent, as the source file has merged entries inside that row. If I promote the second or third, the main (unchanging) columns will not have names, as fields in those rows are just null.View attachment 6895
I also have some details from upper rows that need to be kept, too. Here I just highlighted which rows matter in my case. Further columns contain other entities.
The only difference in files in the folder (apart from quarters and types - actual/budget or quarterly/cumulative) is that the entities may change slightly (some may no longer exist or others may appear).

So any chance to construct the query so that different columns will still match across files?

Thanks for your inputs beforehand.

Best regards,
Foyiq

 

[Ken Puls]

Hey there,

To get multiple rows combined you need to:

-Transpose the table
-Merge the first few columns (I use a delimiter like | so that I can split by that later)
-Transpose it back
-Promote to headers

We actually cover this in Chapter 15 of M is for Data Monkey: http://excelguru.ca/monkey

 

[foyiq]

Hey there,

To get multiple rows combined you need to:

-Transpose the table
-Merge the first few columns (I use a delimiter like | so that I can split by that later)
-Transpose it back
-Promote to headers

We actually cover this in Chapter 15 of M is for Data Monkey: http://excelguru.ca/monkey

Ken, thanks!
I already do that in my query. I was just wondering if this would be applicable because when I transpose and transpose back the structure is not the same.
Anyway, I will try this now.

Thanks.

 

[foyiq]

Yeah, as I feared, it seems not okay to transpose files w/ different column numbers. It returns error as soon as add the function column after getting data from folder.


The second file is the one with different columns

 

[foyiq]

I tried modifying the query and removing unnecessary steps like automatic data type change, which refers to all column names, and it now seems to work!