Mystery of missing data from AppInsights

[PareshDalvi]

I am pulling data from AppInsights to PowerBi Desktop

When I run following query from Azure portal I get x1545 as a count.

customEvents
| where timestamp between (datetime("2022-04-24 00:00:00") .. now())
| where customDimensions <> ""
| where name has "fix:RunFix:start"
| count 

However when I export the query to "M Query" and run it from there, it only returns x575 rows.

= let Source = Json.Document(Web.Contents("https://api.applicationinsights.io/v1/apps/107f81b5-7cc4-4a6f-832d-ce0f06581057/query", 
[Query=[#"query"="customEvents | where timestamp between (datetime(""2022-04-24 00:00:00"").. now())",#"x-ms-app"="AAPBI",#"prefer"="ai.response-thinning=true"],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  

[PareshDalvi]

Thanks. I will try this out.

[ZWilkinson]

I am having this same issue, 

when I query directly to Application Insights, it shows that there are 124,318 rows of data. 
When I import this same data into PowerBI using M Query, and then visualise/count the number of rows, it is only showing 85,210 rows, so there's a difference of around 40,000 rows that are being lost somewhere. 

Did you ever get a resolution to your issue? 

Thanks @PareshDalvi @v-jingzhang 

[PareshDalvi]

Hi @v-jingzhang 

Thanks for replying. I've been doing some more research on this issue.

I've found a discrepency with the way data is loaded. Regarding 500000 row limit, I am only importing 51k rows of data. Any help would be appreciated.

Take a look at this query which provides correct number of (1943) rows.

customEvents
| where timestamp between (datetime(""2022-04-24 00:00:00"") .. now())
| where customDimensions <> """"
| where name has ""fix:RunFix:start""

if I remove last line of filter and then apply filter on visual with same text "fix:RunFix:start" it shows only 670 rows.

customEvents
| where timestamp between (datetime(""2022-04-24 00:00:00"") .. now())
| where customDimensions <> """"

[v-jingzhang]

Hi @PareshDalvi 

It seems the query in the exported M code is different from the Kusto query. Below section is missing. 

| where customDimensions <> ""
| where name has "fix:RunFix:start"

Also try != for not equals operator. And check if there is any additional time range filter on the query. 

Additionally, there is a 500000 row limit. If your data size is larger than this limit, the following article may be helpful: Exceed the 500,000 row limit in Application Insights and Log Analytics with Power BI 

Other reference:

Feed Power BI from Application Insights 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.