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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
Employee
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
Employee
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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.