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

 

 

Highlighted
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

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 306 members 2,940 guests
Please welcome our newest community members: