Power Query - Sharepoint Folder - Combining Multiple Workbooks - Need Column with File Names

Hi Nakor84 ,

Are you using the folder source 'Transform and Load' function? If you are, then there will be a step in your output query called 'Invoke Custom Function' or similar. Click on this step and you should see your source file names, something like this:

Extract the date from the filename at this point, then this date will be duplicated over all relevant rows when the binary tables are expanded in a later step.

Pete

Yes, sometimes (gui button click,) it hides it, when you click combine & transform.

Launch Power Query Editor. click the query where it transformed, click advanced editor (under home tab), adjust one line ... "Name" (or) "Source.Name" , depends on your previous line you can add as below:

#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),

Lines of the context, what I am talking about ...

     #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Column1", type text}})
in
    #"Changed Type"

You can do the same using Query Applied steps, either way the same: