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 recor...
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

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

    • Smauro's avatar
      Smauro
      Solution Sage
      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

      • Cado_one's avatar
        Cado_one
        Resolver III
        5 years ago

        AlB Smauro 

         

        Thank you very much for the help provided, I tried Smauro's solution and it works perfectly ! I didn't know we could invoke a function with more than one parameter.

         

        Have a nice day !

        Cado

    • AlB's avatar
      AlB
      Community Champion
      5 years ago

      Cado_one 

      It's difficult to stop the problem like that. Can you share the pbix perhaps (or an anonymized version that reproduces the issue)?

      I suspect it has to do with 

             #"POST AccessToken"()

      What is that exactly?

      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