cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills 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
Microsoft
Microsoft

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

@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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors