Currently I'm looking to enhance my power bi query which returns data regarding service availability. At present my requirement is 1, 7, 30 day views but that will be changing. Unfortunately it looks like need to create multiple queries to handle this, or a single query to return 30 days and then add a slicer to get customer results which gets tricky with the volume of data for 30 days. Is there a way to dynamically query App Insights from within a power bi report? The continous stream and stream analytics weren't helping with this issue.
From the advanced editor my current query looks like:
let AnalyticsQuery =
let Source = Json.Document(Web.Contents("https://management.azure.com/subscriptions/b708823d-fdd6-b05cae02cfdd/resourcegroups/AvailAppInsight...",
[Query=[#"query"="let timerange = 1d;
requests
| where timestamp > ago(timerange)
| summarize allRequests = count() , successes= countif(success == true or toint(resultCode) < 500), failures=countif(success != true and ( isempty(resultCode) or toint(resultCode) >= 500)) by operation_Name, ""Service Name""
| extend availability = 100.0*successes / allRequests
| order by availability asc nulls last",#"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,
#"Renamed Columns" = Table.RenameColumns(AnalyticsQuery,{{"Column1", "Service"}})
in #"Renamed Columns"
