cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
googlogmob Member
Member

Use calculated variable in web query with gateway

If use static web query gateway and report works well

(

   http://10.10.10.10/ERP-Orchids/hs/getting/20150101

)

But ufter I added calculated variable to link, PowerBI desktop report work well, but couldnt add gateway to report:

 

(

   v = Text.From( DateTime.LocalNow() )

   "http://10.10.10.10/ERP-Orchids/hs/getting/" & v

)

 

Is there way to use calculated variable in web query with gateway?

6 REPLIES 6
v-micsh-msft New Contributor
New Contributor

Re: Use calculated variable in web query with gateway

Hi googlogmob,

If I understanding clearly, you would like to have invoke function available with data refresh, right?

Currently this is not available. And there are already ideas submitted on this topic:

Make functions refreshable when the data source is a parameter of the function

Please check it and vote if necessary.

By the way, take a look at the thread below:

How to setting a dynamic URL connection

 

Please reply back if I have any misunderstandings.

Regards

googlogmob Member
Member

Re: Use calculated variable in web query with gateway

Hi Michael_Shao

This idea Make functions refreshable when the data source is a parameter of the function is about my problem..

My topic readers, please, help to make nessesary votes)

googlogmob Member
Member

Re: Use calculated variable in web query with gateway

I folowed this link and tried use binary converting

 

Here is my code below

 

Query "Link"

 

let
    
    query = "Валюты",
    
    v1 ="http://" & КлиентIP & "/hs/getting/PowerBI/@nAl1t1kx5$/start?storedprocedure=" & query & "&queryparameters=СтруктураПараметраЗапроса = 'НачалоАнализа = '",
    v2 = "'; ОкончаниеАнализа = '",
    v3 = "000000';"""";",
    l = v1 & ПериодС & v2 & ПериодПо & v3,

    b2 = Text.ToBinary(l, 1251),
    textFormat = BinaryFormat.Text(225, 1251) 
in
    textFormat(b2)

 

Query "Main":

 

let
   
    Запрос =  Json.Document(Web.Contents( link4 )),
    #"Преобразование" = Table.ExpandListColumn( Record.ToTable(#"Запрос") , "Value"),

    #"Разверачивание" = Table.ExpandRecordColumn(#"Преобразование", "Value", {"Валюта", "ВалютаID"}, {"Валюта", "ВалютаID"}),
    #"Источник" = Table.RemoveColumns(#"Разверачивание",{"Name"})
in
    #"Источник"

 

In PowerBI Desktop all works fine. But not in web servise - I cant select gateway for such reports

As I think cause is in ANY function (including Text.ToBinary) in source string

Maybe somebody can help me

 

 

googlogmob Member
Member

Re: Use calculated variable in web query with gateway

add ones more query

 

Query "ПериодС":

 

let
    Источник = Text.From( DateTime.LocalNow() )
in
    Источник

 

v-micsh-msft New Contributor
New Contributor

Re: Use calculated variable in web query with gateway

Hi googlogmob,

Have you checked the blog below?

Web.Contents(), M Functions And Dataset Refresh Errors In Power BI

Please take a look at the workaround and see if ti would help in your scenario.

Regards

v-haibl-msft Super Contributor
Super Contributor

Re: Use calculated variable in web query with gateway

@googlogmob

 

This is currently not supported. The pattern in question is not supported by static analysis, and cloud refresh does not support dynamic credential flows.

 

Best Regards,

Herbert