I have 2 files 1 is called SAP MB51 Historical Data and 1 is called MB51 Download data.
The Historical data file is huge like 6 million rows as it contains all stock movements back to 2010 i use this to hold my yearly movements. I combined all the files in the folder to make one huge file no problem with that. This file never changes so i turned off the refresh option.
The download folder is the folder i update daily and contains the most recent transactions. i have combined this also no issues. This folder is refresh enabled.
Now my problem is i want to append my Download data to the bottom of my historical data. I can do this via 2 ways that i have tried and tested and neither is perfect and has an issue.
Append the data in query editor. This works OK and can be done however when the new append is loaded to the model whenever i add to my download data and do a refresh the historical file also refreshes and takes forever as youd expect which i dont want.
Union the data by loading the both files to the model disable refresh on the historic and leave enabled on the download file then create a union as a new table in the model
SAP MB51 = UNION(‘SAP MB51 Historical Data’,‘SAP MB51 Download Data’) This is OK and works fine and is fast because the refresh of Historic is disabled. However once you do this you do not get the ability to edit the data in the query editor as the new table i made doesn’t appear in the editor.
What i am looking for is a way to append the data in the query editor but not refresh the historical data. This will allow me to keep the flexibility of the query editor and also the speed of the refresh.
I agree that is perfect and exactly what i am looking for however it requires a premium license to work (4,995 USD a month) i don`t have that so was looking for a alternative.
No personal experience with incremental refresh (because I’m also on a Pro licence).
But the article referenced earlier describes a work around to mimic an incremental refresh with dataflows, when you’re on a Pro licence - have you already tried that?
No looks to complex for me, its using Microsoft flow. I`m doing this from PC at work and getting another Microsoft product here is not worth the hassle.
Really annoying because append in Power query gives me all i need but refreshes that full data which i don`t want. Union works OK but no flexibility and cant see it in Power Query.
Guess ill have to use the union one and deal with the data set being huge!
This technique uses Dataflows which is not the same as Flow (or Power Automate as it’s been called now) it uses Power Query online to store data in azure data lake storage gen2.
Anyway here’s a link to the introducing Dataflows blog post and a link to the documentation:
I took a look last night and i see what you mean now, i never even knew about this so thanks for that.
Seems i need to set up a gateway in order to do it. im not sure how to do that will have to ask our IT team but sure they will block it for some security policy.
