Phidiax Tech Blog

Azure Application Insights provides a great set of logging, tracing, and troubleshooting tools. The data that can be surfaced from here obviously isn't just of interest at a low troubleshooting level, but can be used in all sorts of ways to tweak application use and performance. As such, you may want to surface this data in a PowerBI setting to upper management without having to add access for them to Azure, but the Analytics query exporter assumes the user will have authorization to the Azure subscription and the AI resource. We can update the generated query to be able to use an API Key and API endpoint instead to authenticate to Application Insights. First, let's take a look at the generated PowerBI query from Application Insights Analytics: 

/*
The exported Power Query Formula Language (M Language ) can be used with Power Query in Excel
and Power BI Desktop.
For Power BI Desktop follow the instructions below: 
1) Download Power BI Desktop from https://powerbi.microsoft.com/desktop/
2) In Power BI Desktop select: 'Get Data' -> 'Blank Query'->'Advanced Query Editor'
3) Paste the M Language script into the Advanced Query Editor and select 'Done'
*/


let AnalyticsQuery =
let Source = Json.Document(Web.Contents("https://management.azure.com/subscriptions/SUBSCRIPTION-GUID-GOES-HERE/resourcegroups/TCD/providers/microsoft.insights/components/AI-NAME-GOES-HERE/api/query?api-version=2014-12-01-preview", 
[Query=[#"query"="requests 
| where timestamp > ago(7d)
| summarize max(duration) by operation_Name
| project operation_Name, max_duration ",#"x-ms-app"="AAPBI"],Timeout=#duration(0,0,4,0)])),
TypeMap = #table(
{ "DataType", "Type" }, 
{ 
{ "Double",   Double.Type },
{ "Int64",    Int64.Type },
{ "Int32",    Int32.Type },
{ "Int16",    Int16.Type },
{ "UInt64",   Number.Type },
{ "UInt32",   Number.Type },
{ "UInt16",   Number.Type },
{ "Byte",     Byte.Type },
{ "Single",   Single.Type },
{ "Decimal",  Decimal.Type },
{ "TimeSpan", Duration.Type },
{ "DateTime", DateTimeZone.Type },
{ "String",   Text.Type },
{ "Boolean",  Logical.Type },
{ "SByte",    Logical.Type }
}),
DataTable = Source[Tables]{0},
Columns = Table.FromRecords(DataTable[Columns]),
ColumnsWithType = Table.Join(Columns, {"DataType"}, TypeMap , {"DataType"}),
Rows = Table.FromRows(DataTable[Rows], Columns[ColumnName]), 
Table = Table.TransformColumnTypes(Rows, Table.ToList(ColumnsWithType, (c) => { c{0 }, c{3}}))
in
Table
in AnalyticsQuery

The generated query tells PowerBI to download the provided link from the web as a JSON document, then accesses the first table within the JSON (the array of tables is named "Tables") pulling the columns (named "Columns" with the column name being "ColumnName" and the data type being "DataType") and rows (named "Rows") from the table.

Setup API Key

So first things first, we need an API Key added to the desired Applications Insight account. Open the desired Application Insights account in the Azure Portal, and open the "API Keys" blade. Take note of the Application ID (You'll need that), and click Create API Key.

It only needs to have Read Telemetry access, so give it a good description (not like this one) and generate the key:

SAVE THE KEY! You can't access that value again so copy it and save it someplace you can get to later.

Update PowerBI Query

/*
The exported Power Query Formula Language (M Language ) can be used with Power Query in Excel
and Power BI Desktop.
For Power BI Desktop follow the instructions below: 
1) Download Power BI Desktop from https://powerbi.microsoft.com/desktop/
2) In Power BI Desktop select: 'Get Data' -> 'Blank Query'->'Advanced Query Editor'
3) Paste the M Language script into the Advanced Query Editor and select 'Done'
*/


let AnalyticsQuery =
let Source = Json.Document(Web.Contents("https://api.applicationinsights.io/beta/apps/APP_INSIGHTS_APPLICATION_ID_GOES_HERE/query", 
[Query=[#"query"="requests 
| where timestamp > ago(7d)
| summarize max(duration) by operation_Name
| project operation_Name, max_duration ",#"x-ms-app"="AAPBI"],Timeout=#duration(0,0,4,0), 
Headers=[#"x-api-key"="APP_INSIGHTS_API_KEY_GOES_HERE", #"Prefer"="response-v1=true"]])),
TypeMap = #table(
{ "DataType", "Type" }, 
{ 
{ "Double",   Double.Type },
{ "Int64",    Int64.Type },
{ "Int32",    Int32.Type },
{ "Int16",    Int16.Type },
{ "UInt64",   Number.Type },
{ "UInt32",   Number.Type },
{ "UInt16",   Number.Type },
{ "Byte",     Byte.Type },
{ "Single",   Single.Type },
{ "Decimal",  Decimal.Type },
{ "TimeSpan", Duration.Type },
{ "DateTime", DateTimeZone.Type },
{ "String",   Text.Type },
{ "Boolean",  Logical.Type },
{ "SByte",    Logical.Type }
}),
DataTable = Source[tables]{0},
Columns = Table.FromRecords(DataTable[columns]),
ColumnsWithType = Table.Join(Columns, {"type"}, TypeMap , {"DataType"}),
Rows = Table.FromRows(DataTable[rows], Columns[name]), 
Table = Table.TransformColumnTypes(Rows, Table.ToList(ColumnsWithType, (c) => { c{0 }, c{3}}))
in
Table
in AnalyticsQuery