Power query environment variable to distinguish Power BI Desktop from Power BI Service

[alban_alev]

Hello,

In order to choose the data sources depending on being executed in Power BI Desktop (dev) or Power BI Service (test, prod), we need an environment variable for power query editor.

Use case: When developping under Power BI Desktop, I need to use development data sources (with known data, in small volume). When I publish to Power Bi Service, I want the service to use production data source from the very first refresh.

I tried with parameters, but it's not satisfying:

- My M code uses env paramter to select right sources (if env = A then use dev data sources...)

- My pbix has env = A

- I publish the pbix, it overwrites the PBI service parameter & triggers a refresh

- As soon as the refresh is finished, I can modify the PBI service parameter to env = B

I'm looking for a way without having to update PBI service parameter each time I publish.

Thanks,

Alban

[darius_liktoriu]

There is a very good, applicable use case for this and it should be implemented.

Consider an Import mode dataset being authored within Power BI Desktop. The source of data has millions of records. If you save this PBIX out, it will be large in size, containing a relatively large sampling (1m records) of the source data, resulting in a PBIX file of 50MB+

Just before saving the PBIX, it would be nice to be able to add a filter to the query, based on a system variable that determines whether the query is running within PBI Desktop and only return the Top 1 row if running in Desktop so you can save the PBIX and only take up 50 Kilobytes, not megabytes. In turn, this filter would NOT apply after being published into the Power BI Service when the Dataset is refreshed with production data.

[dana_frost]

This is not exactly what you want but could work...

Since Power BI Service hosts are always UTC time zone and most PCs (i.e. Power BI Desktop) will not be set to UTC, you could use TimeZone.Current() to optionally run Power Query Steps.

If TimeZone.Current() = 'UTC' then (running on service) else (running on desktop).

[OTellio]

Until Microsoft provides such a functionality (not sure event if they will), you could use Parameters. I created 2 parameters for myself. One of them is a boolean and is called "LocalDevelopment" and one of them is a date called "LocalDevelopmentStartDate". I am using these 2 within my power query (advanced editor) to build dynamic dates for queries, filters so that I don't load all my dataset but only some that i can use to do local development. Once I publish the semantic model, I change "LocalDevelopment" parameter to False from Semantic Model settings and refresh the data afterwards. Since it is built in the transformation step, it will request all data in the server.