topic Re: Power Query - Run one of the queries once in Desktop

Power Query - Run one of the queries once

AvPowerBI — Tue, 28 Dec 2021 22:53:06 GMT

Hi,

I have have 5 queries running in my Desktop. Four of the queries I require to run every hour but the one query does not need to run because the data will not change through out the day and pretty much will be static, it only needs to run when the first schedule runs on that day.

Is there way to skip that query and somehow hold the data results in cache to be used in the data model still from the first time it loaded and ran for that day? Otherwise it is just returning the same results each time the schedule runs every hour and the query is pretty big and takes about 20 minutes just for that query alone to return the results.

Thanks

Re: Power Query - Run one of the queries once

parry2k — Tue, 28 Dec 2021 23:47:02 GMT

AvPowerBI there is no way to have a different schedule for each query in the same model, you can try a few things:

- maybe set up incremental refresh for this query. tons of documents/blog posts available for it

- create a dataflow for this query which refreshes once a day and uses 4 queries as it is + dataflow as source for the 5th query. You can have the dataflow schedule run once a day and then power bi using that dataflow will just read the data from the dataflow without doing any transformation etc.

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

Re: Power Query - Run one of the queries once

AvPowerBI — Wed, 29 Dec 2021 17:01:03 GMT

Hi parry2k Thanks for your reply.

Yes I know that the queries can only have the one schedule, I want to know if that specific query when it runs the first time with the rest of the other queries the first time of the day for when it runs the second time an hour later it would have the results in cache so it doesn't need to look for the same results again.

Four out of the five queries ( including the one that only needs to be ran once ) are all combined into one query, so I'm not if the Dataflow option can do this?

I did have a quick look at the incremental refresh but will need to make a few changes to the underlining table on sql server and then add a new field i.e modified date to pick the latest results, which I can use this as the partition but it would be a lot of work to even get to that stage.

Re: Power Query - Run one of the queries once

smpa01 — Wed, 29 Dec 2021 17:19:25 GMT

AvPowerBI if you are one premium workspace,

create two dataflow;

one for the single query needs to refresh once a day, - refresh it once a day

second for the rest 4 queries need to refresh several times a day - refresh it every hour.

Build a power bi where you bring all the tables from all these DFs. - refresh it every hour with 30 minutes increament from the second dataflow.

Re: Power Query - Run one of the queries once

parry2k — Wed, 29 Dec 2021 18:02:06 GMT

AvPowerBI If incremental refresh will take time to implement (which is preferred), in the meantime, you can take advantage of dataflow, put your longest-running query to DF, and refresh it once a day. It is pretty easy to move PQ from Desktop to Dataflow and everything should work. It should be quick implementation and you will be able to achieve your goal.

No premium capacity is required.

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

Re: Power Query - Run one of the queries once

saj — Wed, 29 Dec 2021 18:27:54 GMT

Power bi allows you to bolt on multiple datasets. Theoretically you could have datesets with their own refresh schedules and then a Power bi that makes use of multiple datasets.

I've not proven it but I know you can set a preview feature to allow multiple datasets in a single pbix.