Power Query - Add a custom column with the file name as the data in the column?-CodePudding

I would like to add the name of the source file as a value for each row in a new custom column in Power Query as the file name as relevant unique identifiers. Is this possible? I have been through all the functions in the custom column formula but I can't find one that has the path of the source returned. The path is just a local windows folder and file name. Ideally I just need the file name but the full path would work too. Thanks!

I have tried to add this code:

#"Added Custom" = Table.AddColumn(#"Changed Type1", "Filename", each ????) but can't find a value that will return the filename where the ??? are.

CodePudding user response：

You probably need to explain better, but on the off chance this helps

Normal code might look like this reading in a file

let Source = Excel.Workbook(File.Contents("Z:\Temp\list.xlsx"), null, true),
FPT_Sheet = Source{[Item="FPT",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(FPT_Sheet, [PromoteAllScalars=true])
in #"Promoted Headers"

you could change it to look like this

let filename="Z:\Temp\list.xlsx",
Source = Excel.Workbook(File.Contents(filename), null, true),
FPT_Sheet = Source{[Item="FPT",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(FPT_Sheet, [PromoteAllScalars=true]),
#"Added Custom" = Table.AddColumn(#"Promoted Headers", "filename", each filename)
in #"Added Custom"

perhaps your alternate question is you are reading a range, and want to know the filename of the source range?

create a cell in the file with formula

=MID(CELL("filename"),SEARCH("[",CELL("filename")) 1,SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-1)

and give the cell a range name like filename

read that into powerquery with

filenamehere = Excel.CurrentWorkbook(){[Name="filename"]}[Content]{0}[Column1]

then add a custom column with formula

= filenamehere