Forum Discussion

Brian_M's avatar
Brian_M
Continued Contributor
10 years ago
Solved

Retain file name column when using Folder as a data source - Power Query M

Hi,   Am hoping for some help.  I will outline three steps below where I take a folder as a source, combine the binaries and then import. It all works fine, however, at the final step after I have ...
  • ImkeF's avatar
    ImkeF
    10 years ago

    Actually, it's pretty easy with this line of code:

     

    = Table.AddColumn(Source, "Custom", each Table.PromoteHeaders(Csv.Document([Content],[Delimiter=";", Encoding=1252])))

     

    Replace your Step 2&3 with it.

    It will retain all the metadata from the 1st step and add the content in an additional custom column.

    If your csv's have all the same headers, you simply expand this column and the headers of the 1st file will be shown - and expand all other files on these cols as well.

     

    If there are differnt cols - just come back & I'll post the code for the auto-expand of different headers.

     

sguess's avatar
sguess
Regular Visitor
4 years ago

Your help is greatly appreciated. I am trying to retain the file name with the contents of each csv file. My data is below. Help?

 

Source = Folder.Files("C:\Users\sjgue\OneDrive\Desktop\accounting\GilfordJ\Reports\Trial Bal\csv tb"),
#"Removed Other Columns" = Table.SelectColumns(Source,{"Content"}),
#"Filtered Hidden Files1" = Table.SelectRows(#"Removed Other Columns", each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
#"Removed Other Columns1" = Table.SelectColumns(#"Invoke Custom Function1", {"Transform File"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
#"Added Custom" = Table.AddColumn(#"Expanded Table Column1", "Custom", each Table.AddColumn(Source, "Custom", each Table.PromoteHeaders(Csv.Document([Content],[Delimiter=";", Encoding=1252])))),
Custom = #"Added Custom"{0}[Custom]
in
Custom