Search code examples
csvpowerquery

How do i get both file name & binary content col from .CSV files?

Power Query

I am trying to extract csv data to table by using below m code but unable to get file Name column while extracting.

M-code

  let    
    Source = Folder.Files(#"Folder Path"),
    #"CombinedBinaries" = Binary.Combine (Source [Content]),
    #"ImportedText" = Lines.FromText(Text.FromBinary(CombinedBinaries)),
    #"TableFromList" = Table.FromList(ImportedText, 
    Splitter.SplitTextByDelimiter("# 
    (tab)"), null, null, ExtraValues.Error)

Are there any possibilities to get file name as well with CSV data?

Solution

  • Sample code to combine all CSV files in a directory that you can modify

    let Source = Folder.Files("C:\temp"),
#"Filtered Rows" = Table.SelectRows(Source, each [Extension] = ".csv"),
#"Added CSVdata" = Table.AddColumn(#"Filtered Rows", "CSVdata", each Table.PromoteHeaders(Csv.Document([Content])), type table),
#"Added Custom" =  Table.AddColumn(#"Added CSVdata", "Custom", each let name = [Name] in Table.AddColumn([CSVdata],"SourceFile", each name)),
#"Combined CSVdata" = Table.Combine(#"Added Custom"[Custom])
in #"Combined CSVdata"

    or

    let Source = Folder.Files("C:\temp"),
#"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".csv")),
#"Added Custom" = Table.AddColumn(#"Filtered Rows", "CSV", each Table.PromoteHeaders(Csv.Document([Content]))),
List = List.Union(List.Transform(#"Added Custom"[CSV], each Table.ColumnNames(_))),
#"Expanded Data" = Table.ExpandTableColumn(#"Added Custom", "CSV", List,List)
in #"Expanded Data"