if statement for conditional query in advanced editor

[Cado_one]

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

[AlB]

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]

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]

@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 · Accepted solution]

@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]

@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]

@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