Merge/Join multiple files in the same folder when some files may be missing
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.