Need to implement incremental refresh where DataSource is kusto

[tekulka]

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

[artemus · Accepted solution]

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
    FormatArgument

let 
    KustoFn = (fn as text, args as list, options as nullable record) =>
        Kusto.Contents("yourcluster", "yourdb",
        fn & "(" & Text.Combine(List.Transform(args, #"FormatKustoArgs"), "," ) & ")"
        , options)
in
    KustoFn

Let me know if you see any bugs with using the kusto connector, I do have contributer access to this.