Forum Discussion
Files from a folder - adding columns
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"
Don't worry, this is a common pattern. Power Query is trying to be helpful and when you click on the "Combine binaries" icon it will add all kinds of helper functions and backlinks and what have you.
You don't need any of that. Instead of clicking the "combine" button you can add a custom column that contains the same transformation for each row's binary field, like for example Csv.Document([Content]). Then you can expand that column and keep your existing columns or add new ones etc. One thing you need to have ready is a list of columns that you want to expand into (that's what the helper function would normally do)
Let me know if you manage to do it based on this description or if you like a code sample.
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!)
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"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!
Go to Power Query by selecting Get Data...Blank Query. Then click Advanced Editor and replace the code with the one I posted.