Power Query editing csv import - saving query changes

[Ave663]

Hi All,

I'm new here so i apologise if i'm not posting as needed - please let me know if not...Ive done a search of previous threads and cant find anything that i can use.

Ok - so here's my scenario
- I'm importing a csv file (its bank statements so i'll be doing the same process monthly)
- I go to "transform data" when the Excel preview loads up and it takes me to Power Query
- I make basic changes to the query structure of the data which i need to make, which gets recorded in the Applied settings window of Power Query
- So now i have the structure i am after and i can import the data into the worksheet...
- Following month i want to append this data with a new csv file which is for the next statement
- Now i could go through all the changes to the query structure again during import and drop the data to the bottom of the previous data but is there anyway of "saving" the initial query changes and just applying them next time around?

I am not familiar with VBA (yet...) and so was looking for something "save and apply" based to get me through for the moment...

 

[pjmorris]

Rather than importing 'From File' import from Folder. You'll get a list of files in the folder and can filter them to ensure that they only import the correct ones. You then invoke a custom step (I think, from memory, you just click on the symbol at the head of the filenames column) and you'll be looking at the same query editor you did previously. Next month you simply add the new statement to the folder and refresh your data - its unbelievably easy. Its how I manage my statements!

 

[Ave663]

Thanks Peter... will see if i can work it through...

 

[Ave663]

Well it all seems to be working now "in principle" Thanks for that Peter... learnt something valuable there... definately my 1% of learning ticked for the day!

Just got (as usual) the next little quirk that's arisen through this process

• Does the first file fine... as expected
• add in some changes to create a second file
• refresh the data query in the workbook... it imports all the date (first and second file) it but arrives with an error
• Error due to it trying to order the second files' header row in the complete data set - If i apply a step to ignore the errors, it removes it and i get the full data set as required however if i keep that rule in i might be missing real errors in the future...

Running the full query action list on all files will force every file to perform the same action in creating a header (and cause an error)... Is there any way of "appending" subsequent files through the query so it doesnt need to address additional column headers for every additional statement file being added? or is it just a "fiddle" to remove any additional column headers through "ignore" statements (or something similar)?

I hope i've explained that well enough?!

Thanks again for the help
Ian

 

[pjmorris]

A simple answer is, before promoting a row to Headings, select all columns and then delete duplicates. Its extremely unlikely that any valid entry will be identical in every column of data, but the headings will be.

For info, when I've done it with my data the 'Expanded Table Column1' step (yours may be named slightly different) takes account of the column headings from each file automatically. After the 'Invoke Custom Function1' step I have two columns: the file name, and a column of tables with 'Transform File from ????' as the heading. I would expect you to have something different. I'm then clicking on the little symbol at the right hand side of the 'Transform File From ???' heading and then expanding all.

HTH

 

[Ave663]

Perfecto! Works a treat!

 

[pjmorris]

delighted it works, many thanks for the feedback