Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Hi everyone, I'm new to the community and this is my 1st post, hopefully not my last. I'm looking for some help. I have a query in KQL that returns 1345 rows. This query is copied into Azure and run, again producing 1345 rows. The query is exported as Power BI (M query) and paste into PowerBI in the advanced query editor. So far so good. PowerBI runs the query, but returns zero rows. There are no errors and PowerBI obviuously assumes the query has run fine.
The query isn't very big so doesnt exceed any size limits as far as I know, all the security credentials are met and it only takes 122 seconds to run in azure. I wonder if I'm missing any settings in PowerBI ?
Any help or ideas would be massively appreciated.
Thank you in advance.
Would you mind showing (a sanitized version of ) the code?
Hi, here is the code with the server names changed to *********. This is exported as a PowerBI M Query and pastes into Advanced Editor of PowerBI. As above it runs fine but returns zero rows in the table.
let lastmonthNumber = getmonth(datetime(now)) - 1;
let lastmonth = iff(lastmonthNumber == 0, 12, lastmonthNumber);
let year = getyear(datetime(now)) - iff(lastmonth == 12, 1, 0);
let dateStart = make_datetime(year, lastmonth, 01);
let OperationIdInTraces = dynamic(['createSwitch']);
let traces =
app('***************').traces
| union app('***************').traces
| union app('***************').exceptions
| union app('***************').exceptions
| where timestamp > dateStart
| project timestamp, customDimensions, operation_Name, message
| extend timestamp_date = format_datetime(timestamp,"yyyy-MM-dd"),
CorrelationId = coalesce(tostring(customDimensions.prop__CorrelationId), tostring(customDimensions.CorrelationId)),
OperationId = coalesce(tostring(customDimensions.prop__OperationId),tostring(customDimensions.prop__operationId),tostring(customDimensions.OperationId)),
Source = coalesce(operation_Name,customDimensions.AzureFunctions_FunctionName ),
MessageStage = coalesce(customDimensions.prop__MessageStage, customDimensions.MessageStage),
Message = coalesce(message,customDimensions.prop__Message, customDimensions.Message),
Year = tostring(datetime_part("year", timestamp)),
Month = datetime_part("month", timestamp),
Day = datetime_part("day", timestamp),
HourOfDay = hourofday(timestamp),
MinuteOfDay = datetime_part("minute", timestamp),
qryAdditionalInfo = coalesce(customDimensions.prop__AdditionalInfo,customDimensions.AdditionalInfo)
| extend AdditionalInfoJson = parse_json(tostring(qryAdditionalInfo))
| extend Mpid = AdditionalInfoJson.mpid,
LogTimeStampMessage = iff(Message contains ",", split(Message, ",")[-1], "")
| extend LogTimestamp = coalesce(todatetime(customDimensions.prop__LogTimestamp), todatetime(customDimensions.LogTimestamp), todatetime(LogTimeStampMessage)),
EventType = AdditionalInfoJson.eventType,
EventDate = todatetime(AdditionalInfoJson.eventDate),
PayloadJson = parse_json(tostring(AdditionalInfoJson.payload))
| extend EventStatus = PayloadJson.eventStatus
;
let distinct_correlationid =
traces
| where OperationId == "createSwitch"
and Source == "ProcessScheduledWork"
and MessageStage == "OperationGetOperationData"
and Message startswith ("Requeuing duplicate request")
| distinct CorrelationId
;
let hours =
traces
| where Source == "VerifyInboundRequest"
| where isempty(['OperationIdInTraces']) or OperationId in (['OperationIdInTraces'])
| where Mpid != ""
| summarize make_set(tostring(CorrelationId)) by bin(timestamp, 1h)
| project
Year = tostring(datetime_part("year", timestamp))
,Month = datetime_part("month", timestamp)
, Day = datetime_part("day", timestamp)
, HourOfDay = datetime_part("hour", timestamp)
, CountTraces = array_length(set_CorrelationId)
, HourType = case(array_length(set_CorrelationId) <= 3500 , "Average",
array_length(set_CorrelationId) > 3500 and array_length(set_CorrelationId) <= 25300, "Peak",
"NoSLA")
| order by Year asc, Month asc, Day asc, HourOfDay asc
;
let count_end =
traces
| where CorrelationId !in (distinct_correlationid)
and Source == "ProcessOutboundQueue-10"
and MessageStage == "MessageDelivery"
and EventType == "RegistrationValidationNotification"
and isempty(['OperationIdInTraces']) or OperationId in (['OperationIdInTraces'])
and EventStatus == "Ok"
and Mpid != ""
| project timestamp_date,
EventDate,LogTimestamp, CorrelationId,Mpid ,Year, Month, Day,HourOfDay, MinuteOfDay
| where isnotempty(timestamp_date) and trim(" ", timestamp_date) != "";
let count_start =
traces
| where Source == "ProcessInboundRequest"
and MessageStage == "SchemaValidation"
and (isempty(['OperationIdInTraces']) or OperationId in (['OperationIdInTraces']))
| project LogTimestampSchema = todatetime(tostring(LogTimestamp)) , CorrelationId;
hours
| join kind=leftouter count_end on Month, Day, HourOfDay
| join kind=leftouter count_start on CorrelationId
| extend ThirdPartyResponseTimeb = datetime_diff('millisecond',LogTimestamp,EventDate),
ResponseTimeInc3b = datetime_diff('millisecond',LogTimestamp,todatetime(LogTimestampSchema))
| extend TrueResponseTime = ResponseTimeInc3b - ThirdPartyResponseTimeb
| summarize AvgExecutionTimeMs = round(avg(TrueResponseTime),2), TotalExecutionTimeMs = round(sum(TrueResponseTime),2)
, MinExecutionTimeMs = round(min(TrueResponseTime),2), MaxExecutionTimeMs = round(max(TrueResponseTime),2)
, VarExecutionTime = round(variance(TrueResponseTime),2), StdDevExecutionTime = round(stdev(TrueResponseTime),2)
,CountTraces = count(), Percentile90Response = round(percentile(TrueResponseTime,90),2)
by Year, Month, Day,HourOfDay, HourType,timestamp_date
| extend SLA = case(case(HourType == "Average" , 3000 - tolong(AvgExecutionTimeMs),
HourType == "Peak" , 5000 - tolong(AvgExecutionTimeMs), 1)
>= 0 , "Pass","Fail")
| extend SLA_90 = case(case(HourType == "Average" , 6000 - tolong(Percentile90Response),
HourType == "Peak" , 8000 - tolong(Percentile90Response), 1)
>= 0 , "Pass","Fail")
| where isnotempty(timestamp_date) and timestamp_date != ""
| project timestamp_date,HourOfDay,CountTraces,HourType,avg_TimeMs = AvgExecutionTimeMs,Percentile90Response,
SLA,SLA_90,TotalExecutionTimeMs, MinExecutionTimeMs, MaxExecutionTimeMs
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
10 | |
8 | |
6 | |
6 | |
6 |