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

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?

Hi the code for the auto expand for different headers would be awesome!

When I try this solution for every entry in the csvs I get a different row in the data for every filepath in the folder regardless of whether the entry appeared in it or not. This makes it impossible to find entries by specifying the csv which is my ultimate goal.

Hello, thanks for your help so far.  I'm curious if this solution works in PowerPivot?  

I've tried several variations of your solution and I can't seem to get it to work.

I want to retain on each row of the Binary Data CLE/CVO and Actual/Budget.

My folder paths:

My Binary Data:

This is the code I tried:  

let
 Source = Folder.Files("C:\Users\User\OneDrive - Corporate\PowerPivot\TB"),
 #"Added Custom" = Table.AddColumn(Source, "Custom", each Table.PromoteHeaders(Csv.Document([Folder Path],[Delimiter="\", Encoding=1252])))
in
 #"Added Custom"

Hey Imke! 

Thanks for all the support. 

I have a slighly different problem and since I'm new to M I don't know how to proceed. 

In my case, I need to keep the creation date of the document, as I may benefit from it for some calculations.

How can I transform the creation date into a new column in Power Querry?

Thanks