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

[Brian_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 combined and imported each binary I would like there to be a column which displays which file (e.g. Filename1.csv) that each row originated from. When you do the combine and import in Steps 2 & Step 3, the file metadata from Step1 (e.g. Filename, DateCreated) is no longer visible.

Has anyone found a strategy to add column(s) for file metadata, particularly the originating filename as a new column in the imported dataset?

The steps are outlined below.

Kind regards

Brian

Step 1:

Source = Folder.Files("C:\Users\XXX\YYYY"),

Content   Name

Binary     Filename1.csv

Binary     Filename2.csv

Binary     Filename3.csv

Binary     Filename4.csv

Binary     Filename5.csv

Binary     Filename6.csv

Step 2:

CombinedBinaries = Binary.Combine(#"Removed Other Columns"[Content])

-- Displays an CSV file icon  

-- 180159607 bytes

Step 3:

Imported = Csv.Document(#"Combined Binaries",[Delimiter=";", Columns=16, Encoding=1252, QuoteStyle=QuoteStyle.None])

--All the rows for the appropriate columns from each file are imported and appended, but I cannot

--see how to append a column for the originating filename.

[ImkeF]

Hi Margaryta,

 The error not recognize the name "Folder" refers to the name of the function (the name of the query):

So you either rename the function/query or edit your code to the name you gave it already.

[sljean17]

Hi I am trying  a variation of this unsuccessfully. I need to add columns 'Country' and 'Broker' to the tables which reside in the 'Promoted Headers' column. The step needs to be created between steps "Changed Type" and "Promote Headers" Any help greatly appreciate. 

let
Source = FolderPath,
Custom1 = Folder.Contents(Source),
#"Removed Other Columns1" = Table.SelectColumns(Custom1,{"Name", "Content"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns1",{{"Name", "FolderName"}, {"Content", "FolderContent"}}),
#"Expanded Content" = Table.ExpandTableColumn(#"Renamed Columns", "FolderContent", {"Content", "Name", "Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path"}, {"Content", "Name", "Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path"}),
#"Added Custom" = Table.AddColumn(#"Expanded Content", "Custom", each if [Extension] = ".csv" then Csv.Document([Content]) else Excel.Workbook([Content])),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"FolderName", "Name", "Extension", "Custom"}),
#"Added Custom1" = Table.AddColumn(#"Removed Other Columns", "PromoteHeaders", each if [Extension] = ".csv" then Table.PromoteHeaders([Custom]) else Table.PromoteHeaders([Custom]{0}[Data])),
#"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom1", "FolderName", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Country", "Broker"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Country", type text}, {"Broker", type text}}),
PromoteHeaders = #"Changed Type"[PromoteHeaders],
#"TableCombine" = Table.Combine(PromoteHeaders),
#"Removed Columns" = Table.RemoveColumns(TableCombine,{"", "Column9"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Gross Weight", type number}, {"Net Weight", type number}, {"Value AWB", type number}, {"Origin Total Freight", type number}, {"Non-EU Freight", type number}, {"EU Freight", type number}, {"Value Duty", type number}, {"Total Freight Eur", type number}, {"Import duty", type number}, {"PCS", type number}, {"Customs VAT", type number}}),
#"OutputTable" = Function.Invoke(ParentFunction,{#"Changed Type1",paramCountry,paramBroker})

in
#"OutputTable"

[ImkeF]

Hi @sljean17,

please post sample data of:

1) The source (step "Changed Type") and

2) Your desired result

Thanks.

[Morvaryt]

Thank you ImkeF, it works now. I appreciate your propmt response.

Regards,

Morvaryt

[Greg_Deckler]

See if a modification of this technique will get you there:

http://dutchdatadude.com/combining-excel-files-using-power-query-for-excel/