Query from Power BI to AI suddenly fails with 502 Bad Gateway

Question

In Power BI (Desktop) we use a Power BI Query (M) to get data from Application Insights Analytics. We published the Power BI Report to Power BI online configured with a daily refresh. It worked fine until it stopped working on 25-1-2017 (UTC).

The error we get is:

DataSource.Error: Web.Contents failed to get contents from '.....' (502): Bad Gateway

This is the complete error:

DataSource.Error: Web.Contents failed to get contents from 'https://management.azure.com/subscriptions/<subscriptionId>/resourcegroups/fps.fsa/providers/microsoft.insights/components/4PS%20Field%20Service%20iOS%20-%20iOS/api/query?api-version=2014-12-01-preview&csl=customEvents%0A%7C%20where%20timestamp%20%3E%20ago%2830d%29%0A%7C%20order%20by%20timestamp%20desc%0A%7C%20extend%20dimensionUserId%20%3D%20tostring%28customDimensions.%5B%27userId%27%5D%29%0A%7C%20extend%20dimensionHost%20%3D%20tostring%28customDimensions.%5B%27url%27%5D%29%0A%7C%20extend%20measurementQuantity%20%3D%20iff%28%20isnotempty%28customMeasurements.%5B%27value%27%5D%29%2C%20todouble%28customMeasurements.%5B%27value%27%5D%29%2C%200.0%29%0A%7C%20extend%20measurementKey%20%3D%20tostring%28customDimensions.%5B%27key%27%5D%29%0A%7C%20extend%20platform%20%3D%20%27iOS%27%0A&x-ms-app=AAPBI' (502): Bad Gateway
Details:
    DataSourceKind=Web
    DataSourcePath=https://management.azure.com/subscriptions/<subscriptionId>/resourcegroups/fps.fsa/providers/microsoft.insights/components/4PS%20Field%20Service%20iOS%20-%20iOS/api/query
    Url=https://management.azure.com/subscriptions/<subscriptionId>/resourcegroups/fps.fsa/providers/microsoft.insights/components/4PS%20Field%20Service%20iOS%20-%20iOS/api/query?api-version=2014-12-01-preview&amp;csl=customEvents%0A%7C%20where%20timestamp%20%3E%20ago%2830d%29%0A%7C%20order%20by%20timestamp%20desc%0A%7C%20extend%20dimensionUserId%20%3D%20tostring%28customDimensions.%5B%27userId%27%5D%29%0A%7C%20extend%20dimensionHost%20%3D%20tostring%28customDimensions.%5B%27url%27%5D%29%0A%7C%20extend%20measurementQuantity%20%3D%20iff%28%20isnotempty%28customMeasurements.%5B%27value%27%5D%29%2C%20todouble%28customMeasurements.%5B%27value%27%5D%29%2C%200.0%29%0A%7C%20extend%20measurementKey%20%3D%20tostring%28customDimensions.%5B%27key%27%5D%29%0A%7C%20extend%20platform%20%3D%20%27iOS%27%0A&amp;x-ms-app=AAPBI

Does anyone know how to solve this?

Answer 1

The AA Query frequently returns too much data, resulting in a 502 Bad Gateway Message. The gateway can only handle 8MB of data at a time.

Example
You built a dashboard that operated in December 2016 and provided you with all requests dating back to the beginning of the month. It's already January 2017, and it's failing miserably. Using a query like the one below, you utilise the PowerBI Dashboard to derive various metrics from the raw data.

requests | where timestamp > datetime(2016-12-01)

The solution
Decide how many days are important to you. Limiting the time range to this month AND only projecting the columns you need will help you cut out a lot of additional data if your goal is to obtain all requests and the timing from the first of the month.

requests | where timestamp > startofmonth(now()) | project name, duration

Another solution
If you're computing averages and percentiles, you could just let Analytics handle it and PowerBI display the results.

requests | where timestamp > startofmonth(now()) | summarize count(), avg(duration), min(duration), max(duration), stdev(duration), percentiles(duration, 50, 75, 90, 95, 99) by name

If you are interested in learning Power BI, then check out Power BI Online Training now!