Search code examples
powerbiazure-application-insightsazure-data-explorerkql

Exporting query from Azure Data Explorer to Power BI fails for Application Insights Cluster

I managed to query my AI telemetry data from Azure Data Explorer specifying the cluster as: https://ade.applicationinsights.io/subscriptions/MYSUBSCRIPTION-GUID/resourcegroups/MY-RG/providers/microsoft.insights/components/MY-AINAME

Successful query in Azure Data Explorer

When I use the Export to Power BI, I get the following script:

let 
    Source = AzureDataExplorer.Contents("https://ade.applicationinsights.io/subscriptions/MYSUBSCRIPTION-GUID/resourcegroups/MY-RG/providers/microsoft.insights/components/MY-AINAME", "uptakesim", "customEvents#(lf)| where timestamp > ago(1d)", [Timeout = #duration(0,0,4,0)])
in
    Source

When I try to run that exported query in Power BI I get the following error:

Power BI error message

For some reason the sign in fails and does not show any prompt to login.

If I end the url at /microsoft.insights ( https://ade.applicationinsights.io/subscriptions/MY_SUBSCRIPTION/resourcegroups/MY-RG/providers/microsoft.insights), removing /components/MY-AINAME the login works.

Solution
    • I tried the same and got the same issue while importing Application insights data from azure data explorer (Kusto). Refer the fourth point in the MS document for the cluster format to be used while connecting Data explorer cluster in Power BI. We can import only Kusto cluster data using azure data explorer in power BI. enter image description here

    Instead, you can follow the below approach to read data from application insights data in power BI.

    • Open the application insights log in azure portal.

    • Type the query and run it.

    • Then click on Export and select Power BI (as an M Query) enter image description here

    • This will give the query in a .txt file.

    • Then in Power BI desktop, Click get data-> Blank Query

    enter image description here

    • Click the Advanced editor. Replace the sample code with the power query from the exported file. enter image description here

    • Click Done in the advanced editor window.

    • Data will be loaded in the power BI Power Query editor.

    enter image description here

    • While exporting from application insights log, you can also choose Power BI (new Dataset). Refer the MS document for more details.

    Reference: Log Analytics integration with Power BI and Excel - Azure Monitor | Microsoft Learn