Hi everyone!
I am willing to dynamize API REST Queries inside the report or Power Automate (the one that it is easier) to make daily reports of several data. I would like to make a template as easy for the user as possible.
I have got a localhost server that connects to API REST. The link is the following:
https://localhost/reporting/Dps?from=2023-07-14T09:57:00.000Z&to=2023-07-14T13:29:00.000Z&&System1:d...
&&System1:d2.response.actvalue&&System1:d3.response.actvalue
You have to know that:
https://localhost/reporting/Dps?from= --> Is a fixed parameter
2023-07-14T09:57:00.000Z&to=2023-07-14T13:29:00.000Z --> Is a variable parameter
&&System1:d1.response.actvalue --> Is a variable parameter
&&System1:d2.response.actvalue --> Is a variable parameter
&&System1:d3.response.actvalue --> Is a variable parameter
What I am trying to do is to automate a daily report.
So, if today is 2023-07-20, to create the daily report of 2023-07-20 and send it to my email without making myself the query manually and entering inside the Power BI. I tried to parameterize but it only worked for the visual, not for the query.
Here is the PowerQuery code I already have to get the data:
let
Origen = Json.Document(Web.Contents("https://localhost/reporting/Dps?from=2000-01-01T10:00:00.000Z&to=2099-07-14T13:29:00.000Z&&System1:d...")),
#"Convertida en tabla" = Table.FromRecords({Origen}),
#"Se expandió dps" = Table.ExpandListColumn(#"Convertida en tabla", "dps"),
#"Se expandió dps1" = Table.ExpandRecordColumn(#"Se expandió dps", "dps", {"DP", "result"}, {"dps.DP", "dps.result"}),
#"Se expandió dps.result" = Table.ExpandRecordColumn(#"Se expandió dps1", "dps.result", {"returnMessage", "returnCode", "data"}, {"dps.result.returnMessage", "dps.result.returnCode", "dps.result.data"}),
#"Se expandió dps.result.data" = Table.ExpandListColumn(#"Se expandió dps.result", "dps.result.data"),
#"Se expandió dps.result.data1" = Table.ExpandRecordColumn(#"Se expandió dps.result.data", "dps.result.data", {"value", "timestamp", "statusBits", "manager"}, {"dps.result.data.value", "dps.result.data.timestamp", "dps.result.data.statusBits", "dps.result.data.manager"}),
#"Tipo cambiado" = Table.TransformColumnTypes(#"Se expandió dps.result.data1",{{"generatedBy", type text}, {"formatVersionMajor", Int64.Type}, {"formatVersionMinor", Int64.Type}, {"from", type datetime}, {"to", type datetime}, {"dps.DP", type text}, {"dps.result.returnMessage", type text}, {"dps.result.returnCode", Int64.Type}, {"dps.result.data.value", type number}, {"dps.result.data.timestamp", type datetime}, {"dps.result.data.statusBits", type number}, {"dps.result.data.manager", type text}}),
Do you know how to do it, or if it is even possible?
Thank you in advance,
Ignacio Such
