COMBINING FILES IN POWER QUERY - I WANT TO KEEP THE NAMES OF THE FILES IN EACH ROW OF THE FINAL QUER

[Chema_Ortega]

Hello everybody. I hope all of you are well.

I am combining several files from a sharepoint folder, but I need to keep the name of the files, because that name contains the worker, and I have to keep it in all rows of the final file. I remember there was a way to do it, but I cannot remember HOW to do it... Does anyone remember it?

Thanks a lot. Best regards!!!

[Chema_Ortega]

GRRREEEEAAAATTTTT!!!! Thanks a lot, Servipa!!! That was SOOO USEFUL!!! 

[serpiva64 · Accepted solution]

Hi,

you can use this pattern

let
Source = Folder.Files("C:\Users\serpi\OneDrive - Studio Piva\Documenti\V\Power Bi\Community\SampleFolder1"),
#"Added Custom" = Table.AddColumn(Source, "Custom", each Excel.Workbook([Content])),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Name", "Data"}, {"Name.1", "Data"}),
#"Removed Other Columns" = Table.SelectColumns(#"Expanded Custom",{"Name.1", "Data", "Name"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"Name.1", "SheetName"}, {"Name", "FileName"}}),
#"Added Custom1" = Table.AddColumn(#"Renamed Columns", "Name1", each let name=[FileName] in Table.AddColumn([Data],"FileName",each name)),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom", each let name=[SheetName] in Table.AddColumn([Name1],"SheetName",each name)),
#"Removed Other Columns1" = Table.SelectColumns(#"Added Custom2",{"SheetName", "FileName", "Custom"}),
#"Expanded Custom1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Custom", {"Column1", "Column2", "Column3", "Filename", "Sheetname", "Column4"}, {"Column1", "Column2", "Column3", "Filename", "Sheetname", "Column4"})
in
#"Expanded Custom1"

If this post is useful to help you to solve your issue, consider giving the post a thumbs up and accepting it as a solution!