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 dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Solved! Go to 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
Hi, @adamfingol
Please check if this tutorial could help.
POWER BI INCREMENTAL REFRESH WITH WEB API DATA SOURCE
Best Regards,
Community Support Team _ Eason
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
24 | |
20 | |
18 | |
15 | |
13 |
User | Count |
---|---|
37 | |
22 | |
21 | |
18 | |
12 |