Hello,
My source sales data is in two databases, order summary (41,000 rows, of which I only need 1,000 for my department) and order detail (210,000 rows, of which I need about 8,000 for my department) going back to 2017. I need the historical data for comparisons but I know that once the year and month is over, it will never change. Yet I still connect to it and process it all at every refresh so it takes 4 or 5 minutes every time. (The data only changes once a day)
1) Is there a way to import and process all the data from 2017 to 2020 , set that query to not refresh (even on a refresh all), and then just get 2021 data every morning and append the 2021 query to the static 2017-to-2020 query?
2) Or, better, is there a query format that only loads changed (since the last refresh) data?
3) Also, in the same vein, is there a way to have the database perform part of the query (just my department) on the server prior to loading and further processing on the local computer?
This is particularly relevant in times of working from home over VPN which encrypts/decrypts the data.
Peter Warburton
Hi Peter,
Presumably you're filtering out the data you don't need that you mention in paragraph 1 before closing and loading the query, so Power Query is actually only bringing in 1000 + 8000 rows of data.
1 & 2. If you're referring to Power Query in Power BI then you have incremental refresh available, but this isn't yet available in Excel, so there is no workaround.
3. If your source data supports query folding (e.g. SQL servers etc.) then Power Query will already be shifting the processing back to the database.
Mynda
Thanks Mynda!
I'll go through my PQ steps and put query foldable ones first. I haven't got onto my Power BI course yet, so I'll check out incremental refresh later.
Peter
