Incremental refresh jsonrpc API Zabbix

[adamfingol]

Hello I try implement incremental refresh in Zabbix API.

My query in API is more than 5M lines in 3 month.
I wanto do incremental refresh with 10days.
I have date colum but my query in apy dont use date/time but use timestamp.

my query is

let
    data = DateTime.LocalNow(),
    data_inicio_coleta = Number.ToText(Duration.TotalSeconds(#datetime(Date.Year(data), Date.Month(data), Date.Day(data), Time.Hour(data), Time.Minute(data), Number.Round(Time.Second(data)))-#datetime(1970, 1, 1, 0, 0, 0)) - (Pdias * 24 * 60 * 60)),
	//data_inicio_coleta = Number.ToText(Duration.TotalSeconds(#datetime(Date.Year(PData_Inicial), Date.Month(PData_Inicial), Date.Day(PData_Inicial), 0, 0, 0)-#datetime(1970, 1, 1, 0, 0, 0))),
    body= "{
        ""params"": {
            ""password"": ""xxx"", 
            ""user"": ""xxx""},
            ""jsonrpc"": ""2.0"", 
            ""method"": ""user.login"", 
            ""auth"": null, ""id"": 1}",
	url = "http://www.myzabbix.com/zabbix",
	token = Json.Document(Web.Contents(url, [Headers=[#"Content-Type"="application/json"], RelativePath="zabbix/api_jsonrpc.php", Content=Text.ToBinary(body)]))[result],
	id_host = fConsulta_Zabbix(token, "host.get", "{""output"": [""host"", ""hostid""], ""groupids"" : ""1672""}"),
    transformado_em_tabela = Table.FromList(id_host, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    coluna_host_expandida = Table.ExpandRecordColumn(transformado_em_tabela, "Column1", {"host", "hostid"}, {"host", "hostid"}),
    frota = Table.AddColumn(coluna_host_expandida, "Frota", each Text.Middle([host], 9, 1), type text),
    trem = Table.AddColumn(frota, "Trem", each Text.AfterDelimiter([host], "_", 1), type text),
    consulta_itens = Table.AddColumn(trem, "fConsulta_Zabbix", each fConsulta_Zabbix(token, "item.get", "{""output"": [""name"", ""itemid""], ""hostids"" : """ & [hostid] & """, ""application"" : ""Log Completo""}")),
    transforma_lista_em_coluna = Table.ExpandListColumn(consulta_itens, "fConsulta_Zabbix"),
    expande_coluna_item = Table.ExpandRecordColumn(transforma_lista_em_coluna, "fConsulta_Zabbix", {"name", "itemid"}, {"name", "itemid"}),
    historico_itens = Table.AddColumn(expande_coluna_item, "fConsulta_Zabbix", each fConsulta_Zabbix(token, "history.get", "{""output"": [""clock"", ""value""], ""history"": 1, ""itemids"": """ & [itemid] & """, ""hostids"": """ & [hostid] & """, ""sortfield"": ""clock"", ""sortorder"": ""DESC"", ""time_from"": """& data_inicio_coleta &"""}")),
    transforma_lista_de_hist_em_coluna = Table.ExpandListColumn(historico_itens, "fConsulta_Zabbix"),
    coluna_expandida_log = Table.ExpandRecordColumn(transforma_lista_de_hist_em_coluna, "fConsulta_Zabbix", {"clock", "value"}, {"timestamp", "Log"}),
    #"Tipo Alterado" = Table.TransformColumnTypes(coluna_expandida_log,{{"host", type text}, {"hostid", type text}, {"Frota", type text}, {"Trem", type text}, {"name", type text}, {"itemid", type text}, {"timestamp", type number}, {"Log", type text}}),
    retirado_nulls = Table.SelectRows(#"Tipo Alterado", each ([timestamp] <> null)),
    AddedUTC = Table.AddColumn(retirado_nulls, "data", each #datetimezone(1970,1,1,0,0,0,0,0)+#duration(0,0,0,[timestamp]), type datetime),
    SKU = Table.AddColumn(AddedUTC, "SKU", each Text.Combine({[Frota], if Text.EndsWith([name],[Frota]) then "T" else Text.End([name], 1), [Log]}, " "), type text),
    selecionado_colunas = Table.SelectColumns(SKU,{"Frota", "Trem", "Log", "data", "SKU"}),
    //exit = fConsulta_Zabbix(token, "user.logout", "[]"),
    ajustado_tipo_data = Table.TransformColumnTypes(selecionado_colunas,{{"data", type datetime}}),
    #"Linhas Filtradas" = Table.SelectRows(ajustado_tipo_data, each [data] >= RangeStart and [data] < RangeEnd)
in
    #"Linhas Filtradas"

The line "historico_itens" is my query in api.

Here """time_from"": """& data_inicio_coleta &"""}"" is where i pass timestamp.
So the date colunm is dont participate of query.

There is a way i do incremental refresh?

link for zabbix api documentation

https://www.zabbix.com/documentation/5.0/manual/api/reference/history/get

[v-easonf-msft · Accepted solution]

Hi,   @adamfingol 

Please check if this tutorial could help.

POWER BI INCREMENTAL REFRESH WITH WEB API DATA SOURCE 

Best Regards,
Community Support Team _ Eason