Files from a folder - adding columns

[JohnHedges]

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 break something else. I have a few additional steps applied in PQE already after the files are appended, but I don't know if this is causing any of the issues.

Can anyone recommend a simple step by step guide to get a couple of extra columns from the source csv files?

Thanks 🙂

I'm not sure if screenshots are useful, but...

[JohnHedges]

Apologies for not coming back to this discussion sooner - the perils of having too many different projects on the go at once!

Thanks for all the help 🙂 

[v-shex-msft]

Hi @JohnHedges,

Did lbendlin 's suggestions help with your scenario? if that is the case, you can consider Kudo or accept his suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[lbendlin]

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.

[JohnHedges]

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 · Accepted solution]

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]

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]

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