Forum Discussion

JohnHedges's avatar
JohnHedges
Frequent Visitor
5 years ago
Solved

Files from a folder - adding columns

Hi,   I have three csv files in a folder that Power BI is magically appending as a data source.   I want to add a couple of columns to the files, but everything I've tried to add them seems to br...
Need Help
  • lbendlin's avatar
    lbendlin
    5 years ago

    Here is an example that combines all .csv files in C:\temp.  It assumes that the files have the same structure, and provides dummy column headers - you may want to provide your own.  It also doesn't strip out potentially existing column headers inside the files - you can filter those out if needed, or modify the code for the AddColumn accordingly.

     

    let
    Source = Folder.Files("C:\Temp"),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".csv")),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each Csv.Document([Content])),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6"})
in
    #"Expanded Custom"
JohnHedges's avatar
JohnHedges
Frequent Visitor
5 years ago

Thanks for taking the time to reply 🙂

 

An example would be fantastic. It's a rather large project using a lot of different folders as sources and I'm terrified of breaking it (also I'm trying to avoid having to kill part of model and rebuild from scratch, just to get two extra columns!)

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
5 years ago

Here is an example that combines all .csv files in C:\temp.  It assumes that the files have the same structure, and provides dummy column headers - you may want to provide your own.  It also doesn't strip out potentially existing column headers inside the files - you can filter those out if needed, or modify the code for the AddColumn accordingly.

 

let
    Source = Folder.Files("C:\Temp"),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".csv")),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each Csv.Document([Content])),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6"})
in
    #"Expanded Custom"
  • JohnHedges's avatar
    JohnHedges
    Frequent Visitor
    5 years ago

    Thanks for taking the time to create an example.

     

    At the risk of looking like an idiot, I just don't know where to put it. I'll have a fiddle around with my back up file and see if I can work it out!

    • lbendlin's avatar
      lbendlin
      Icon for Super User rankSuper User
      5 years ago

      Go to Power Query by selecting Get Data...Blank Query.  Then click Advanced Editor and replace the code with the one I posted.