Power Query Custom Column with Data Source

[Isambard]

Hi,

I'm hoping this is really easy for someone with more experience than me. I'm taking data from a few dozen excel spreadsheets, each with a 20 named tables. I have set this up in power query and the data is coming through - one power query table for each excel table which is what I want.

I would realy like to add a collumn to each table specifiying the name of the excel file it came from so I'll be able to slice this back out later on. Is there a simple function or toggle somewhere that would let me include the source file name with my data?

Thanks for any help.

[kleigh]

The name of the Excel file doesn't come at the step of Excel.Workbook, it's one before when you have your folder of Excel files. I think the usual thing is to combine by drilling into the Binary column, but it's also possible to create a new custom column with Excel.Workbook([Content]), remove the file information you don't need (including the raw Content), then expand your custom column. You'll also need to expand and combine your Excel tables in a way that preserves the file information as a column.

[Minseven07 · Accepted solution]

I see. Can you get your data in a folder then filter on that specific excel file? If yes, you will have access to the folder path and the file name.

[Isambard]

This works perfectly. Thank you.

[Minseven07]

in your query editor, when you get your source file, you have access to the folder path and the file name. You can maybe concatenate those column.
If this doesn't help, a pbix file whould be welcome

[Isambard]

Thank you. I don't need the file path, just the name would be fine. But I'm not seeing an option to load either of them as collumns. Should this be in the window when I load the data?

Sorry I can't post the pbix as its confidential but here are some screenshots:

The data then loads with all the fields from the excel table but not the source name.

Thank you.