Power query and loading a column into multiple files in a folder before importing them from the to folder

[seancsn]

I have 36 large csv files (each around 3GB in size) that I have a in a folder on my c drive. I would like to use the folder import option in Power Query. Before they are imported, I need to add a column eg. Monthdate, which inserts a date into all rows for a file, which is specific to the name of the file eg. if the file is named "Jan 15" then I need the date to be inserted to be "15/01/2015" etc.

Would this be possible or should I import each file individually and add the column manually?

Thanks in advance

Sean

 

[scottsen]

I suspect this *is* possible, but I usually go cry for help with Power Query from Chris Webb or Ken Puls.

I have to say, that is an *impressive* amount of data for csv files. I'd be trying to get that into SQL!

Anyway, I will see if I can't get one of the PQ ninjas to give an assist here

 

[Ken Puls]

Hi there,

Only had time for a quick read, but see if this helps: Merge Multiple Files With Properties in Power QueryThe Ken Puls (Excelguru) Blog

 

[seancsn]

Thanks you both. Ill take a look at the link now.

 

[PentaGalCXO]

I suspect it can be accomplished in Power Query, but I use Power Shell scripts for the pre-processing of CSV files. Gives me the flexibility to add a date column as you describe, but also gives me the opportunity to scrub for dirty records by counting delimiters, filtering non-printable characters, etc. Power Shell syntax is not for the faint of heart but there are plenty of examples on sites like www.stackoverflow.com.

 

[seancsn]

Kens guidance worked wonderfully. Thank you.

Thanks also for the power shell suggestion.

 

[Ken Puls]

PentaGalCXO, we can do all the other things you mention very easily with Power Query as well. The big selling point to Power Query (imo) is that adding columns, scrubbing data, etc.. is fairly intuitive, and the solution ends up being packaged in the workbook. I think the Power Shell idea is cool, but it does take a level of expertise not found in the normal Excel user.

I am curious what your though is on learning to deployment time frames. How much time do you think a complete newbie to PowerShell would realistically need to invest in order to do the things you describe? Is it a matter of hours/days/weeks? (I keep threatening to learn Power Shell, but haven't got a clue how much time I'd need to dedicate to it.)

 

[scottsen]

I find Powershell to be more intuitive than the M language ... by far But the PQ UI is the easiest.

Where Powershell falls down is in being able to repeat/refresh. PQ is at least kinda mostly integrated into the excel and powerbi experiences... Powershell, not at all.