How to add a new column with file source name

[Anonymous]

Hi Community,

can you help me with this issue??

i want to add a new column in my appended / merge table showing the name of the file that the raws come from.

Regards

[v-kkf-msft]

Hi @Anonymous ,

This is the result of directly append queries. 

You can try adding custom columns to add the paths to each table, or you can also refer to this video to create your functions.

= Table.Combine({Table.AddColumn(#"Changed Type","FileName",each "Table A"), Table.AddColumn(#"Table B","FileName",each "Table B")})

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

HI, thanks for your response, but it is not usefull

as i need the filename ( excell file name) into a custom column

Thanks

[lbendlin]

Yes, this is possible. Please provide more details about your setup, and maybe some sample files.

[Anonymous]

need to add in this custom column

the name of the excel file

thanks

[v-kkf-msft · Accepted solution]

Hi @Anonymous ,

1. If you are connecting to these excel files directly, then you may have to use parameters to enter the path of the excel files. As shown below, create parameters for each excel files, and the parameter value is the path to the corresponding excel file, which can then be referenced in the data source to import the excel table. 

Source = Excel.Workbook(File.Contents(#"Excel1 path"), null, true)

Source = Excel.Workbook(File.Contents(#"Excel2 path"), null, true)

  

Then create the following custom columns for each table, i.e. the column value is equal to the parameter value. Then split the column to get the file name.

2. A simpler way is to put all excel files in the same folder and then use the Folder connector, which will include the name and path of each excel file. 

If the excel files you want to append have the same structure, then you can just click on "Combine Files" and you will get the following result.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.