cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Brian_M Responsive Resident
Responsive Resident

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User III
Super User III

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

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.

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

24 REPLIES 24
Super User IV
Super User IV

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

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

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

 


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Super User III
Super User III

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

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.

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

Highlighted
pbakaric
Frequent Visitor

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

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?

Super User III
Super User III

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

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).

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

pbakaric
Frequent Visitor

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

Here is where I am at:

 

let
Source = Folder.Files("C:\Users\pbakaric\Google Drive\DFM Dashboard\Reports"),
#"Combined Binaries" = Binary.Combine(Source[Content]),
#"Imported CSV" = Csv.Document(#"Combined Binaries",[Delimiter=";", Columns=21, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Changed Type" = Table.TransformColumnTypes(#"Imported CSV",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}, {"Column17", type text}, {"Column18", type text}, {"Column19", type text}, {"Column20", type text}, {"Column21", type text}}),
#"Removed Top Rows" = Table.Skip(#"Changed Type",1),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows"),
#"Removed Blank Rows" = Table.SelectRows(#"Promoted Headers", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})))
in
#"Removed Blank Rows"

 

I tried incorporating your script but I couldn't seem to get it to work. I appreciate the help!!! 

Super User III
Super User III

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

Your code should look like this:

 

 

let 
Source = Folder.Files("C:\Users\pbakaric\Google Drive\DFM Dashboard\Reports"),
#"Added Custom" = Table.AddColumn(Source, "Custom", each Table.PromoteHeaders(Table.Skip(Csv.Document([Content], [Delimiter=";", Columns=4, Encoding=1252, QuoteStyle=QuoteStyle.None]),1))), 
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Name", "Custom"}), 
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Datum", "Menge", "Wert", "Kommentar"}, {"Datum", "Menge", "Wert", "Kommentar"}) 
in 
#"Expanded Custom"


Check out the video as well, as there you'll find another method, showing how to use an explicit function to do that. This might be more convenient if there are more steps to perform an the nesting into the #"Added Custom"-steps becomes too confusing.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Brian_M Responsive Resident
Responsive Resident

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

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

 

Morvaryt
Frequent Visitor

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

Hi ImkeF,

 

I'm following the steps outlined in the video to retain the file names and I'm receiving an error at the very last step, when I add a custom column:

= Table.AddColumn(Source, "Custom", each Folder([Content]))

 

The error I am receiving is as follows: "Expression error: The name 'Folder" wasn't recognized. Make sure it's spelled correctly."

 

Here's the function I created:

 

= (Content) =>
let
// Source = Folder.Files("C:XXXContentDrilldown"),
// #"C:XXXContentDrilldown csv" = Source{[#"Folder Path"="C:\XXXContentDrilldown\",Name="XXXContent Drilldown 20160501-20160531.csv"]}[Content],
#"Imported CSV" = Csv.Document(Content,[Delimiter=",", Columns=7, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Changed Type" = Table.TransformColumnTypes(#"Imported CSV",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}}),
#"Removed Top Rows" = Table.Skip(#"Changed Type",6),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows")

in #"Promoted Headers"

 

I would appreciate any insight.

Thanks!

Margaryta

Super User III
Super User III

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

Hi Margaryta,

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

 

PBI_ImageFolder.png

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

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Helpful resources

Announcements
‘Better Together’ T-Shirt Contest – Winner Announced!

‘Better Together’ T-Shirt Contest – Winner Announced!

And the winner is...

Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors