Different columns order

[foyiq]

Hi,

I import Balance Sheet and Profit and Loss reports from folders (in two different queries). The main columns remain the same - BS/PL item name, its code, dates and some other data. However, when it comes to values, there might be changes, as the value columns contain entities of the group, some of which may no longer exist.
While searching for answers, I found out that it might be possible to append separate queries, but this is quite problematic, as the number of files is quite large (one for every period). Basically,  the files look like the following:

So is there any way to extract columns in a right way and still use the whole folder as a source?

For notice, I further transform the data by unpivoting these columns, so that entities go in rows and only two value columns (beginning and ending balance) are left.

Thank you.

[v-sihou-msft · Accepted solution]

@foyiq

As you said, those different queries have same columns. You only need to care abouth underlying data instead of the rendering part in pivot table during data retrieval. If the underlying data included in those files is under same structure, you should be able to combine them into a single query via Power Query. Please refer to blog below:

Combine Multiple Excel Workbooks in Power Query

Regards,

[foyiq]

Now after I re-read the article more thoroughly, it seems the solution might prove useful, let me try this.

[foyiq]

Thanks for reply.

Sorry, forget about two queries. To clarify, say, I have one query for Balance Sheet, which downloads data from folder. Inside this folder, most of the files have identical columns, but there are some with different columns, as shown in the sample screenshot (i.e. missing entity or new entity). The problem is that I use the entity names and codes from the first file. And if I use, say, Europe, Asia, North America, South America, Africa, and Australia, and in another file Asia is missing, then data moves and North America data columns will contain data from that file on what actually is South America.

I hope I explained the situation.

As far as I understood from the link you provided, the solution proposed there is not quite applicable to my case.

Best regards,