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
freelensia
Advocate II
Advocate II

Merge/Join multiple files in the same folder when some files may be missing

Hi,

I have these language files in the same folder.
en.csv
fr.csv
ja.csv
etc.

 

I have pulled them into Power Query and now each of them is a separate query.

I want to merge them such that the ID from EN becomes the ID for the final table. Then I will merge FR into EN matching LEFTOUTER, then expanding the merge. I then merge JA into the resulting query matching LEFTOUTER nd expanding the merge again. I will repeat this until there are no more csv files to merge.

The final output should be like this:
ID   ENValue FRValue JAValue ...

..        ..            ..         ..       ...

 

However, I have a problem. Some of the language file may be in that folder at some point in time. In other times, they may be missing. I want return an empty XXValue when the language file XX is missing.

 

For example, the file JA.csv is missing in that folder. As a result, the JA query in PQ is blank.

I tried to merge the JA query to the final table, but PQ gives me error, "The Column ID of the table was not found".

 

I have also looked into another approach of combining all files from a folder. But this will only allow me to combine, not merge.

 

Any ideas to accomplish my goal?

 

Thanks!

1 ACCEPTED SOLUTION
v-chuncz-msft
Community Support
Community Support

@freelensia ,

 

It seems that you may try Pivot Column in Query Editor.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
v-chuncz-msft
Community Support
Community Support

@freelensia ,

 

It seems that you may try Pivot Column in Query Editor.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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