CODEWITHSUNDEEP
powerbiazure-application-insightsm
Nick Heidke
Nick Heidke

Reputation: 2847

Application Insights Data Limited Importing Into PowerBI

I'm attempting to import data from Azure Application Insights into PowerBI. The issue is that, regardless of the timespan I set, I seem to only be pulling about a week's worth of data. Here's what the M query looks like:

let AnalyticsQuery =
let Source = Json.Document(Web.Contents("https://api.applicationinsights.io/v1/apps/<uuid>/query", 
[Query=[#"query"="customEvents
| project customDimensions
",#"x-ms-app"="AAPBI",#"timespan"="P30D"],Timeout=#duration(0,0,60,0)])),
TypeMap = #table(
{ "AnalyticsTypes", "Type" }, 
{ 
{ "string",   Text.Type },
{ "int",      Int32.Type },
{ "long",     Int64.Type },
{ "real",     Double.Type },
{ "timespan", Duration.Type },
{ "datetime", DateTimeZone.Type },
{ "bool",     Logical.Type },
{ "guid",     Text.Type },
{ "dynamic",  Text.Type }
}),
DataTable = Source[tables]{0},
Columns = Table.FromRecords(DataTable[columns]),
ColumnsWithType = Table.Join(Columns, {"type"}, TypeMap , {"AnalyticsTypes"}),
Rows = Table.FromRows(DataTable[rows], Columns[name]), 
Table = Table.TransformColumnTypes(Rows, Table.ToList(ColumnsWithType, (c) => { c{0}, c{3}}))
in
Table
in
    AnalyticsQuery

I was thinking this was a size issue, but I've already narrowed it down to a single column (albeit a wide one) and it's still not returning any more data.

Narrowing the returned dataset to two columns has increased the dataset to include a few weeks instead of less than a week, but I'm still looking for a bigger dataset. Here's the latest query:

let AnalyticsQuery =
let Source = Json.Document(Web.Contents("https://api.applicationinsights.io/v1/apps/<uuid>/query", 
[Query=[#"query"="customEvents
| extend d=parse_json(customDimensions)
| project timestamp, d[""Properties""]
| order by timestamp desc
| where timestamp <= now() and d_Properties <> """"
",#"x-ms-app"="AAPBI"],Timeout=#duration(0,0,4,0)])),
TypeMap = #table(
{ "AnalyticsTypes", "Type" }, 
{ 
{ "string",   Text.Type },
{ "int",      Int32.Type },
{ "long",     Int64.Type },
{ "real",     Double.Type },
{ "timespan", Duration.Type },
{ "datetime", DateTimeZone.Type },
{ "bool",     Logical.Type },
{ "guid",     Text.Type },
{ "dynamic",  Text.Type }
}),
DataTable = Source[tables]{0},
Columns = Table.FromRecords(DataTable[columns]),
ColumnsWithType = Table.Join(Columns, {"type"}, TypeMap , {"AnalyticsTypes"}),
Rows = Table.FromRows(DataTable[rows], Columns[name]), 
Table = Table.TransformColumnTypes(Rows, Table.ToList(ColumnsWithType, (c) => { c{0}, c{3}}))
in
Table,
    #"Sorted Rows" = Table.Sort(AnalyticsQuery,{{"timestamp", Order.Ascending}})
in
    #"Sorted Rows"

Upvotes: 5

Views: 533

Answers (1)

intrixius
intrixius

Reputation: 1136

You should look into either table buffering or directquery: see this discussion

Upvotes: 1

Related Questions