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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
jaap_olsthoorn
Advocate II
Advocate II

Parameter dependent on Location (service vs desktop)

Hi all, I am trying to find a way to make the value of one of my parameters (my date cutoff) dependent on the location that powerquery is running:

 

For example:

I have a large dataset that I only load the last 3 months of in powerBI desktop, so I am able to perform updates to my powerquery code and refresh the data without waiting too long. However, once online, I want to load the last 2 years worth of data instead of the last 3 months.

 

I can do this manually by changing the "date cutoff" parameter in the powerBI service, but I was wondering if there was a way for powerquery to recognise where it is being run (desktop or service), and depending on the outcome load the last 3 months or 2 years worth of data.

 

Maybe it's not possible, but maybe someone knows a trick.

 

Thanks a lot!

 

Jaap

1 ACCEPTED SOLUTION
v-zhangti
Community Support
Community Support

Hi, @jaap_olsthoorn 

 

Maybe you can try incremental refresh. When you publish a Power BI Desktop model to the service, each table in the new dataset has a single partition. That single partition contains all rows for that table. If the table is large, say with tens of millions of rows or even more, a refresh for that table can take a long time and consume an excessive amount of resources.

 

With incremental refresh, the service dynamically partitions and separates data that needs to be refreshed frequently from data that can be refreshed less frequently. Table data is filtered by using Power Query date/time parameters with the reserved, case-sensitive names RangeStart and RangeEnd. When initially configuring incremental refresh in Power BI Desktop, the parameters are used to filter only a small period of data to be loaded into the model.

 

When published to the service, with the first refresh operation, the service creates incremental refresh and historical partitions and optionally a real-time DirectQuery partition based on incremental refresh policy settings, and then overrides the parameter values to filter and query data for each partition based on date/time values for each row.

 

This is the relevant document, hope to help you: 

https://docs.microsoft.com/power-bi/connect-data/incremental-refresh-overview 

https://docs.microsoft.com/power-bi/connect-data/incremental-refresh-configure 

 

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
jaap_olsthoorn
Advocate II
Advocate II

This is a nice solution! I jsut tested it, and it seems to work great! Thanks!

v-zhangti
Community Support
Community Support

Hi, @jaap_olsthoorn 

 

Maybe you can try incremental refresh. When you publish a Power BI Desktop model to the service, each table in the new dataset has a single partition. That single partition contains all rows for that table. If the table is large, say with tens of millions of rows or even more, a refresh for that table can take a long time and consume an excessive amount of resources.

 

With incremental refresh, the service dynamically partitions and separates data that needs to be refreshed frequently from data that can be refreshed less frequently. Table data is filtered by using Power Query date/time parameters with the reserved, case-sensitive names RangeStart and RangeEnd. When initially configuring incremental refresh in Power BI Desktop, the parameters are used to filter only a small period of data to be loaded into the model.

 

When published to the service, with the first refresh operation, the service creates incremental refresh and historical partitions and optionally a real-time DirectQuery partition based on incremental refresh policy settings, and then overrides the parameter values to filter and query data for each partition based on date/time values for each row.

 

This is the relevant document, hope to help you: 

https://docs.microsoft.com/power-bi/connect-data/incremental-refresh-overview 

https://docs.microsoft.com/power-bi/connect-data/incremental-refresh-configure 

 

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors