Skip to main content

    Power BI

    New

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

    Vote (34) Share
    Alban A's profile image

    Alban A on 22 Jan 2023 11:56:12

    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

    Comments (3)
    Alban A's profile image Profile Picture

    Ozan Tellioglu on 10 Sep 2024 09:56:16

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

    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.

    Alban A's profile image Profile Picture

    Dana Frost on 14 Mar 2024 18:56:10

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

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

    Alban A's profile image Profile Picture

    Darius Liktorius on 09 Nov 2023 20:35:27

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

    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.