Forum Discussion
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 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.
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.
25 Replies
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.
Perfect thanks Imke. At all my data came through in one column but after noticing the [Delimiter=";" and changing it to [Delimiter="," then I was sorted.
For anyone else reading, I'll try to describe the difference between my first attempt and Imke's solution. Imke avoids steo where I combined the binaries (and lost the filename), instead she adds a new column that contains the data from each file.My Attempt - loose filename...
let
//Lists all the files in the folder
Source = Folder.Files("C:\Users\Brian\Desktop\FolderName")
/*Code generated for me by clicking "Combine binaries" double down arrow on Content column
Combines all the files into one binary, (NB: here I lose the filename which I wanted to keep :-( ...) */
MyStep1 = Binary.Combine(Source[Content]),
/*Code generate for me by right clicking binary object and choosing Csv.
Converts single binary object from Step1 above, expands all the data - but I am missing the filename that I wanted to keep.
*/
MyStep2 = Csv.Document(MyStep1,[Delimiter=",", Columns=12, Encoding=1252, QuoteStyle=QuoteStyle.None])
in
MyStep2
Imke's Solution - keep filename...
let
//Lists all the files in the folder
Source = Folder.Files("C:\Users\Brian\Desktop\FolderName")
/*Instead of combining binaries into one single binary and losing all the file metadata (like filename), use the
Table.AddColumn - best of both worlds, keep the file metadata plus add a new column containing the data from
each file.*/
ImkeStep1 = Table.AddColumn(Source, "Custom", each Table.PromoteHeaders(Csv.Document([Content],[Delimiter=",", Encoding=1252])))
in
ImkeStep1
I am using the folder type for get data, how can I show the file name or file create date for each row of the merged data?
It is not clear to me what you are actually doing. Could you please share the M-code that has been created so far (from the advanced editor).
Hi the code for the auto expand for different headers would be awesome!
See if a modification of this technique will get you there:
http://dutchdatadude.com/combining-excel-files-using-power-query-for-excel/
What would be the code if you were connecting to a folder with fixed width text files rather than CSV files?
Sorry, no idea as I haven't done this already. But you can easily find out by using the UI: Start the import from folder process and you will see a table popping up with all files listed with different metadata-field. Choose "Content" and click into one of its fields. A further dialogue might start but at the end you will be able to see the full code in the advanced editor.
Hi,
Can you advise me on using the Table name in the new column of the table, when we use the data from websource with multiple tables
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
