Parameter dependent on Location (service vs desktop)

[jaap_olsthoorn]

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

[jaap_olsthoorn]

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

[v-zhangti · Accepted solution]

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.