Forum Discussion

Brian_M's avatar
Brian_M
Continued Contributor
10 years ago
Solved

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 ...
  • ImkeF's avatar
    ImkeF
    10 years ago

    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.

     

jadams0304's avatar
jadams0304
Frequent Visitor
9 years ago

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"

 

jadams0304's avatar
jadams0304
Frequent Visitor
9 years ago

I ended up solving this myself.  I finally decided to use my head and look up table.expandtablecolumn which made it immediately obvious what I was doing wrong.  I'm new to this so I had no reference point for what was actually happening when I used table.expandtablecolumn.  

 

 

let
    Source = Folder.Files("C:\Users\User\OneDrive - Corporate\PowerPivot\TB"),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Table.PromoteHeaders(Csv.Document([Content], [Delimiter=",", Encoding=1252]))),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom", "Folder Path"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Period", "No", "Current Debit/(Credit)", "YTD Debit/(Credit)"}, {"Custom.Period", "Custom.No", "Custom.Current Debit/(Credit)", "Custom.YTD Debit/(Credit)"})
in
    #"Expanded Custom"

 

From here I'll split the Folder Path on "\" and then remove all the Folder Path columns besides the two containing CLE/CVO and Actual/Budget.