Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
foyiq
Advocate I
Advocate I

Different columns order

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:

23.05.png

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.

1 ACCEPTED SOLUTION
v-sihou-msft
Microsoft Employee
Microsoft Employee

@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,

 

 

View solution in original post

3 REPLIES 3
v-sihou-msft
Microsoft Employee
Microsoft Employee

@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,

 

 

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

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,

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.