Forum Discussion

Cado_one's avatar
Cado_one
Resolver III
5 years ago
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 record is qualified either as PV or EOL in the third column.

The next step is to invoke a function to each id from the first column. This works well on a general query but my wish is to send with the function a different query to the API, based on the third column value. I want one query for PV records and another one for EOL records.

How and where would you write this if statement in the advanced editor ? Something like if 'Table'[EOL/PV]="PV" then Query1 else Query2

Here is my function code in the advanced editor :

(idSite as text) =>
let
Source =
Function.InvokeAfter(
()=>Json.Document(Web.Contents("https://eekixe.api.qantum.net/v2/",
[
RelativePath = "sensors/measurements/monthly?include=sensor&filter[sites.id]="& idSite &"&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"

 

Any help or idea will be welcomed !

Thanks in adavance,

Cado

Need Help
Tips and Tricks
  • Smauro's avatar
    Smauro
    5 years ago

    Cado_one,

     

    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

6 Replies

  • AlB's avatar
    AlB
    Community Champion
    5 years ago

    Hi Cado_one :

    I'm not sure I understand, what is the problem if you add a custome column with the code

    if [EOL/PV]="PV" then Query1 else Query2

     as you're more or less already suggesting? Where Query1 and Query2 are the specific operations you need

    Please mark the question solved when done and consider giving kudos if posts are helpful.

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

    Cheers 

     

    • Cado_one's avatar
      Cado_one
      Resolver III
      5 years ago

      Hi AlB ,

       

      thank you for answering.

      It's hard to explain and my english is not very good sorry for that.

      I can't do it in a conditionnal column because the queries to the API are done by a function invoked on the first column of the table (it executes the query for each id in the column and retrieve many data, not juste one). So I need to do it in the function's code pasted in my first message to change the RelativePath based on the table's third colum value. Something like 

      if [EOL/PV]="PV" then 
[
RelativePath = "..." ] else [ "..."
]

      But I don't manage to catch the EOL/PV value

      • Cado_one's avatar
        Cado_one
        Resolver III
        5 years ago

        @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