Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Jamesgu
New Member

Azure Application Insights queries export to Power BI

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"

1 REPLY 1
v-jiascu-msft
Employee
Employee

Hi @Jamesgu,

 

Usually, we create 1, 7, 30 views in the reports. We get all the data in and then we can create 1, 7, 30 views by formulas, filters, or slicers. You can create reports or pages for n day view from the same dataset. Simple question, if we already get 30 days data, why should we get 1 day data again. So let's do it with DAX formula.

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.