Power query source from folder - append data?

MLExcel · Nov 2, 2016

Hi everybody,

Sofar almost all showcases I could find of power query using files from a specified folder as a routine import process assume that the source data always contains all the historic data.

Example:
3 subdivisions report their monthly numbers in 3 csv files.
I setup a query to fetch the three csv files form a specific folder, perform some transformations and all the data is imported for further use.
Next month all I need to do is run the query and everything is updated.

And if the divisions allways add their new data to the old data I can also analyse historical developments:
E.g.:
Jan 2015 - 100 lines are submitted
Feb 2015 - 200 lines are submitted (100 from Jan, 100 from Feb)
...

So far so good.

However in my real life business experiance this doesn't happen that way.
What I receive are the monthly data only!

It is now my job to append this data to the allready existing data of the previous months.
So the task I would like Power Query to perform is an "append data" operation.

Here is one of my current setups without Power Query:
A table in Excel which is itself the source for the data modell (to use Power Pivot on the data).
This table contains the data of the last years.
Every months I receive 3 csv - files, perform some minor transformations and than manually copy/paste the data in the Excel table (this is the step I would like to automate)
As a last step I manually add category vales to the new data in two category columns (that will always stay a manual task).

I found some hints that one can create two queries and merge them
a query that picks up the new csv data (query1 )
and a second one that takes the old data (out of the excel table)

The output would than be a combination of the old and the new data. Howerver I don't really like the idea of my old data being reprocessed every month.

Is there no other solution? The need to append data must be a quite common one I would guess.

Any sugestions, or links to tutorials on the topic would be highly appreceated.

I am using:

Win10 Pro x64

Excel 2016 (MS Office 365 ProPlus)

Kind regards,

[FONT=&quot]MLE[/FONT]

ImkeF · Nov 2, 2016

I wouldn't distinguish between raw data and processed data. Instead I would collect all my raw data in a folder (in multiple files, so just add the new files).
Then I'd import them with power query using the "From-Folder"-Method.
Then perform all the transformations simultaniously on all data. Power Query should be fast enough.

MLExcel · Nov 4, 2016

Hi ImkeF,

thanks for your suggestion. However this has two limitaions:

1.) (Main issue): The imported data is further processed in the destination file. With that I mean that additonal data is entered in additonal columns: e.g. the raw data has a column "booking text", after importing there is a new (additional) column next to it called "booking text short" where a short discripition of the booking text is entered manually for each line.
This additonal data would be lost if I always import all the files again.

2.) If we are thinking about a process that is suppossed to last several years, I am usually preparing for the raw data input files to change once in a while in their format/structure etc.
In my system this is not an issue. If the source data changes I adapt the query a everything is fine again. The old raw data stays in the old format, but that is no issue because it is no longer imported since it is already stored in the final system.

BR
MLE

ImkeF · Nov 4, 2016

Although I blogged about an approach for incremental load: How to create a Load History or Load Log in Power Query or Power BI – The BIccountant (also see the little video: https://www.youtube.com/watch?v=xDVUUcbX13g ) I don't recommend it for accounting purposes like this.

For your scenario I'd recommend a variation of my 1st suggestion:

1) Store a copy of your current file and save sheet with the transformed data as a csv-file your new "Archive"-folder. Use the key number from the last GL-entry as part of the filename.
2) Take a copy of your first file and add a filter that retrieves the key from the last file in your Archive-folder (using the from-folder-method and regarding the "Date created" for the filter of the latest file).
3) Use that filter for the next import (>= last import)
4) Make your transformations and ammendments and store that (as xlsx for the next round and as a csv file into the Archive)
5) Create a new file who collects all files from the Archive-folders and creates your reports.

That way you end up with db-like solution where the risk of messing up your accounting data is adequately limited. Also you can maintain the "semantic layer": PowerBI Best Practice: Views for non-database data – The BIccountant

Hope you'll find sth that suit your need here

MLExcel · Nov 17, 2016

Thanks a lot for the link about the incremental load. I was able to set that up in a small test file. I will now try to get the real thing with all the transformations going as well.

Power query source from folder - append data?

MLExcel

New Member

Excel Facts

ImkeF

Well-known Member

MLExcel

New Member

ImkeF

Well-known Member

MLExcel

New Member

Similar threads

Forum statistics

Share this page

Power query source from folder - append data?

MLExcel

New Member

Excel Facts

ImkeF

Well-known Member

MLExcel

New Member

ImkeF

Well-known Member

MLExcel

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock