Forum Discussion
Helper IVCombining files with different field names from Sharepoint folder
Hello,
Please can you tell me how to combine files from the same sharepoint folder if these files have different field names? (and I don't know what field names will be added in the future). I'd like all fields to be present in the final table, with nulls where a field isn't present for a given input file.
Checking google and copilot for this seems to give very complicated solutions using direct M code editing, are there any 'standard' ways of working (I'd like to cross-train non-expert power bi users and they won't be able to code M code directly)
Thank you for your help!
CM
Hi PowerWhy ,
To do this you need to change the last step of the query when you do the merge.
In my example I have 4 files all of them with different headers:
The last step will expand the table and you get a similar result to this:
Has you can see what is hapenning is that the "automatic" transformation forces only the columns from the first file to be expanded, you need to change the last part of the code:
Replace the highlited part by
List.Distinct(List.Combine(List.Transform (#"Removed Other Columns1"[Transform File],each Table.ColumnNames(_))))So your full code will be:
= Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", List.Distinct(List.Combine(List.Transform (#"Removed Other Columns1"[Transform File],each Table.ColumnNames(_)))))Has you can see now the result is expanding all the columns:
Hope this helps if you need additional information please let me know.
10 Replies
PowerWhyHi Felix,
For example:
File 1 may have column headers A, B, CFile 2 may have column headers A, C, D
So for these 2 files the output table should include column headers A, B, C, DBut there will be more files in the future and we don't know what column headers they will have, but they will also need including in the output file.
Does that make sense?
CM
- MFelix
Super User
What type of files are you working with? Csv, Excel, json
- Ashish_Mathur
Hi,
Watch this video to get started - Power Query #08: Import Multiple Excel Sheets From Multiple Excel Workbooks - YouTube
- PowerWhy
OK but I think the files in that video all have the same column structure within each worksheet?
- MFelix
Hi PowerWhy ,
To do this you need to change the last step of the query when you do the merge.
In my example I have 4 files all of them with different headers:
The last step will expand the table and you get a similar result to this:
Has you can see what is hapenning is that the "automatic" transformation forces only the columns from the first file to be expanded, you need to change the last part of the code:
Replace the highlited part by
List.Distinct(List.Combine(List.Transform (#"Removed Other Columns1"[Transform File],each Table.ColumnNames(_))))So your full code will be:
= Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", List.Distinct(List.Combine(List.Transform (#"Removed Other Columns1"[Transform File],each Table.ColumnNames(_)))))Has you can see now the result is expanding all the columns:
Hope this helps if you need additional information please let me know.
V-yubandi-msftCommunity Support
- V-yubandi-msft
Hello PowerWhy ,
If your issue is resolved, that’s good to hear. But if you're still facing any difficulties or running into new challenges, feel free to let me know.
Thank You.
- V-yubandi-msft
Hi PowerWhy ,
Could you let me know if your issue is resolved, or if you need any additional details.
Thanks.
