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.
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:
| C:\Users\User\OneDrive - Corporate\PowerPivot\TB\CLE\Actual\ |
| C:\Users\User\OneDrive - Corporate\PowerPivot\TB\CVO\Actual\ |
| C:\Users\User\OneDrive - Corporate\PowerPivot\TB\CVO\Budget\ |
| C:\Users\User\OneDrive - Corporate\PowerPivot\TB\CVO\Budget\ |
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"