How to derive static tables from PowerBI queries without data connection

[DorFey]

Hi there!

I got some problems to generate a static table from a PowerBI query (Azure Maps route directions API).

My query produced the expected result for a list of points (response contains : distance, travel time and polyline-points for a list of origins * list of destinations, specified by latitute/longitute point coordinates. Basically i want the same table, but without the live-data connection, because it triggers billing and can´t be deployed that way (Azure Maps primary API-key can´t be in a deployed version of the .pbix).

So i basically ended up with:

My script:

let
    apiKey =  "deleted" , //deleted for security reasons
    url = "https://atlas.microsoft.com/route/directions/json", // Azure Maps Route Directions API

    getRouteInfo = (queryString as text) =>

        let
            queryParams = [
                #"api-version" = "1.0",
                query = queryString,
                geometry = "true",
                routeType = "fastest",
                #"subscription-key" = apiKey
            ],

            response = Web.Contents(url, [Query = queryParams, ApiKeyName = "subscription-key"]),
            jsonResponse = Json.Document(response)

        in 

            jsonResponse

in getRouteInfo

Now the problem is - everytime i refresh the query or try to load it into my visuals, i trigger the query another time (and Azure Maps billing costs occur). I want to create a static table, but it isn´t possible by "copy entire table" -> .csv / .xls , because after unpacking the list, where the polyline data is stored, i end up with 10^6 rows or smth (fields "points_polylinie") and some data gets shredded.

I tried to simply copy it into a new table, but the data connection stays active and as soon as i delete the api-key (to stop billable transactions), the preview gets lost. So this approach also doesn´t work:

let
    newTable = (workplace_list as table) as table =>
        let
            azureQuery = workplace_list,
            azureToRecord = Table.ToRecords(azureQuery),
            recordToTable = Table.FromRecords(azureToRecord)

        in
            recordToTable
in
    newTable

Any hints?

[GilbertQ · Accepted solution]

Hi @DorFey 

That is correct that this will happen when you are working in power. Bi desktop, but once you upload it to the service, you should no longer see in your requests coming through because it is now a static table.

[DorFey]

Problem for that case is - i can´t deploy this version of the .pbix, because the query script contains the Azure maps primary key. I would need to delete the key before uploading it to the web environment (for security reasons), which usually breaks the data model.

Maybe it can be an approach if i replace the primary key with an azure key vault secret. 

[DorFey]

Hi there @GilbertQ 

The problem is - as soon as i close the PowerBI query editor with "close and apply", PowerBI makes a report refresh for all tables, which got the attribute "enable to load", although "include in report refresh" is deactivated for them.
That leads to ~400 billable transactions for every "close and apply", which is necessary from time to time, if i want to use these tables directly in visuals.

So i basically tried to make a copy of these tables (7 values test-table), which are referencing the columns of the (live-data) table. Deactivated both "enable to load" & "include in report refresh" for the live-data tables and only enable "enable to load" for the referencing ones (and deactivate "include in ...")

-> That also leads to billable transactions in my Azure Maps instance, for both cases.

This setting also made no difference in that behaviour:

[GilbertQ]

Hi @DorFey 

Go into power query editor, right click on the table and deselect the option for include in report refresh. This will not refresh the table.