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

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.