Combining files with different field names from Sharepoint folder

[PowerWhy]

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

[V-yubandi-msft]

Hi @PowerWhy ,

Could you let me know if your issue is resolved, or if you need any additional details.

Thanks.

[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]

Hello @PowerWhy ,

Just checking in does @MFelix , response address your needs? If you need further assistance or clarification, please let us know. We're here to help.

Thanks.

[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 · Accepted solution]

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.

[MFelix]

Hi @PowerWhy ,

This depends on the type of file you have and the settings.

Can you please give some additional information about the setup of the files, and how it can change in the future.

[PowerWhy]

Hi Felix,

For example:
File 1 may have column headers A, B, C

File 2 may have column headers A, C, D
So for these 2 files the output table should include column headers A, B, C, D

But 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]

What type of files are you working with? Csv, Excel, json

[PowerWhy]

Excel