Forum Discussion
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.
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