Forum Discussion
Solved
if statement for conditional query in advanced editor
Hi, my problem sould be simple but I don't manage to find the right way to do it. I have a table which contains 3 columns retrieved from an API. As you can see on the below screenshot each recor...
Haven't really checked everything, but read that you call it for each row based on [idSite], so, why don't you just call it based on the other column as well?
e.g.(idSite as text, check as any) => let Source = if check = "PV" then Function.InvokeAfter( ()=>Json.Document(Web.Contents("https://eekixe.api.qantum.net/v2/", [ RelativePath = "sensors/measurements/monthly?include=sensor&filter[sites.id]="& idSite &"filter[sensors.referentHolder.type]=plants&filter[sensors.referent]=EnergyExpected, WindSpeedBudget, CapacityFactorFullLoadHours, PerformanceRatioContractor, PerformanceRatioContractor, TimeBasedAvailabilityContractor, DailyIrradiation, SolarIrradiationBudget, PowerActiveMeter, WindSpeed, EnergyInvoiced, EnergyBudget&filter[measurements.datetime][range]=2020-01-01,2020-12-01&fields[sensors]=referent,unit", Headers=[Authorization="Bearer "& #"POST AccessToken"()] ] )) , #duration(0,0,0,2)) else Function.InvokeAfter( ()=>Json.Document(Web.Contents("https://eekixe.api.qantum.net/v2/", [ RelativePath = "sensors/measurements/monthly?include=sensor&filter[sites.id]="& idSite &"filter[sensors.referentHolder.type]=sites&filter[sensors.referent]=EnergyExpected, WindSpeedBudget, CapacityFactorFullLoadHours, PerformanceRatioContractor, PerformanceRatioContractor, TimeBasedAvailabilityContractor, DailyIrradiation, SolarIrradiationBudget, PowerActiveMeter, WindSpeed, EnergyInvoiced, EnergyBudget&filter[measurements.datetime][range]=2020-01-01,2020-12-01&fields[sensors]=referent,unit", Headers=[Authorization="Bearer "& #"POST AccessToken"()] ] )) , #duration(0,0,0,2)), getData = Source[data], #"Converted to Table" = Table.FromList(getData, Splitter.SplitByNothing(), null, null, ExtraValues.Error) in #"Converted to Table"and then
.. each function([idSite], [#"EOL/PV"]) ..
I'd also add a third parameter for the access token to call it only once, supposing it doesn't change for every subsequent call, something like:.. check as any, accToken as text) ... "Bearer " & accToken ...calling it like
.. each function([idSite], [#"EOL/PV"], #"POST AccessToken"()) ..
Cheers,
Spyros
@aib
If it can help, the result I'd like to have would look like that. Here the code seems correct but I got an "Expression.Error: We cannot convert a value of type Function to type Record" when invoking it to the table.
(idSite as text) =>
let
Source =
Function.InvokeAfter(
()=>Json.Document(Web.Contents("https://eekixe.api.qantum.net/v2/",
each if [#"EOL/PV"] = "PV" then
[
RelativePath = "sensors/measurements/monthly?include=sensor&filter[sites.id]="& idSite &"filter[sensors.referentHolder.type]=plants&filter[sensors.referent]=EnergyExpected, WindSpeedBudget, CapacityFactorFullLoadHours, PerformanceRatioContractor, PerformanceRatioContractor, TimeBasedAvailabilityContractor, DailyIrradiation, SolarIrradiationBudget, PowerActiveMeter, WindSpeed, EnergyInvoiced, EnergyBudget&filter[measurements.datetime][range]=2020-01-01,2020-12-01&fields[sensors]=referent,unit",
Headers=[Authorization="Bearer "& #"POST AccessToken"()]
]
else
[
RelativePath = "sensors/measurements/monthly?include=sensor&filter[sites.id]="& idSite &"filter[sensors.referentHolder.type]=sites&filter[sensors.referent]=EnergyExpected, WindSpeedBudget, CapacityFactorFullLoadHours, PerformanceRatioContractor, PerformanceRatioContractor, TimeBasedAvailabilityContractor, DailyIrradiation, SolarIrradiationBudget, PowerActiveMeter, WindSpeed, EnergyInvoiced, EnergyBudget&filter[measurements.datetime][range]=2020-01-01,2020-12-01&fields[sensors]=referent,unit",
Headers=[Authorization="Bearer "& #"POST AccessToken"()]
])),
#duration(0,0,0,2)),
data = Source[data],
#"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
#"Converted to Table"
Cado
Haven't really checked everything, but read that you call it for each row based on [idSite], so, why don't you just call it based on the other column as well?
e.g.
(idSite as text, check as any) =>
let
Source = if check = "PV" then
Function.InvokeAfter(
()=>Json.Document(Web.Contents("https://eekixe.api.qantum.net/v2/",
[
RelativePath = "sensors/measurements/monthly?include=sensor&filter[sites.id]="& idSite &"filter[sensors.referentHolder.type]=plants&filter[sensors.referent]=EnergyExpected, WindSpeedBudget, CapacityFactorFullLoadHours, PerformanceRatioContractor, PerformanceRatioContractor, TimeBasedAvailabilityContractor, DailyIrradiation, SolarIrradiationBudget, PowerActiveMeter, WindSpeed, EnergyInvoiced, EnergyBudget&filter[measurements.datetime][range]=2020-01-01,2020-12-01&fields[sensors]=referent,unit",
Headers=[Authorization="Bearer "& #"POST AccessToken"()]
] ))
,
#duration(0,0,0,2))
else
Function.InvokeAfter(
()=>Json.Document(Web.Contents("https://eekixe.api.qantum.net/v2/",
[
RelativePath = "sensors/measurements/monthly?include=sensor&filter[sites.id]="& idSite &"filter[sensors.referentHolder.type]=sites&filter[sensors.referent]=EnergyExpected, WindSpeedBudget, CapacityFactorFullLoadHours, PerformanceRatioContractor, PerformanceRatioContractor, TimeBasedAvailabilityContractor, DailyIrradiation, SolarIrradiationBudget, PowerActiveMeter, WindSpeed, EnergyInvoiced, EnergyBudget&filter[measurements.datetime][range]=2020-01-01,2020-12-01&fields[sensors]=referent,unit",
Headers=[Authorization="Bearer "& #"POST AccessToken"()]
] ))
,
#duration(0,0,0,2)),
getData = Source[data],
#"Converted to Table" = Table.FromList(getData, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
#"Converted to Table"and then
.. each function([idSite], [#"EOL/PV"]) ..
I'd also add a third parameter for the access token to call it only once, supposing it doesn't change for every subsequent call, something like:
.. check as any, accToken as text)
...
"Bearer " & accToken ...calling it like
.. each function([idSite], [#"EOL/PV"], #"POST AccessToken"()) ..
Cheers,
Spyros