Forum Discussion
Need to implement incremental refresh where DataSource is kusto
Hi Team,
So I want to implement incremental refresh for power BI premium. This is because we have around 300M+ records in our telemetry and we need to fetch it all in order to generate reports. Now since the data source is kusto, in incremental steps given as per documentation, I get added a step as FilteredRows in M QUery which when I right click I do not see Native query being enabled. This says the query is not foldable. As a result I'm trying to add these parameters (aka RangeStart and RangeEnd) to the first step while querying from kusto itself.
I need to know if there;s a way I can formulate this query. I did not find any article regarding the same. Here's a snippet from my query
let KustoQuery =
let Source = Json.Document(Web.Contents("*****.show version",[Query=[#"csl"="traces
| where appName == ""****""
and timestamp > ago("&RangeStart&")
and timestamp < ago("&RangeEnd&")
| project timestamp, *****
**** are appropriate value as per logs. We have a column called timestamp of type DateTime in kusto. The requirement is to pass this parameter in the form of RangeStart and RangeEnd in the source query itself.
Any help would be appreciated.
Thanks
Kusto does not emit the native query. It does not mean it is not foldable. Add the step to see if it is folding:
Value.Metadata(PreviousStep)[QueryFolding]
Enable diagnonsis and I think you should be able to find the native kusto query being run.
Not sure if this helps, but this is what I'm using
let FormatArgument = (arg as any) => if arg is date then "datetime(" & Date.ToText(arg, "yyyy-MM-dd") & ")" else if arg is datetime then "datetime(" & DateTime.ToText(arg, "yyyy-MM-dd HH:mm:ss") & ")" else if arg is datetimezone then "datetime(" & DateTime.ToText(DateTimeZone.RemoveZone(DateTimeZone.ToUtc(arg)), "yyyy-MM-dd HH:mm:ss") & ")" else if arg is duration then "time(" & Duration.ToText(arg) & ")" else if arg = null then error "null value passed to function" else Text.From(arg) in FormatArgumentlet KustoFn = (fn as text, args as list, options as nullable record) => Kusto.Contents("yourcluster", "yourdb", fn & "(" & Text.Combine(List.Transform(args, #"FormatKustoArgs"), "," ) & ")" , options) in KustoFnLet me know if you see any bugs with using the kusto connector, I do have contributer access to this.
1 Reply
Kusto does not emit the native query. It does not mean it is not foldable. Add the step to see if it is folding:
Value.Metadata(PreviousStep)[QueryFolding]
Enable diagnonsis and I think you should be able to find the native kusto query being run.
Not sure if this helps, but this is what I'm using
let FormatArgument = (arg as any) => if arg is date then "datetime(" & Date.ToText(arg, "yyyy-MM-dd") & ")" else if arg is datetime then "datetime(" & DateTime.ToText(arg, "yyyy-MM-dd HH:mm:ss") & ")" else if arg is datetimezone then "datetime(" & DateTime.ToText(DateTimeZone.RemoveZone(DateTimeZone.ToUtc(arg)), "yyyy-MM-dd HH:mm:ss") & ")" else if arg is duration then "time(" & Duration.ToText(arg) & ")" else if arg = null then error "null value passed to function" else Text.From(arg) in FormatArgumentlet KustoFn = (fn as text, args as list, options as nullable record) => Kusto.Contents("yourcluster", "yourdb", fn & "(" & Text.Combine(List.Transform(args, #"FormatKustoArgs"), "," ) & ")" , options) in KustoFnLet me know if you see any bugs with using the kusto connector, I do have contributer access to this.