Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
AvPowerBI
Post Patron
Post Patron

Power Query - Run one of the queries once

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 

1 ACCEPTED SOLUTION
parry2k
Super User
Super User

@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

or

- 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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

5 REPLIES 5
saj
Helper III
Helper III

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.

smpa01
Super User
Super User

@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.

 

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
parry2k
Super User
Super User

@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

or

- 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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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.

 

@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

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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.