Forum Discussion

googlogmob's avatar
googlogmob
Icon for Advocate II rankAdvocate II
10 years ago

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, bu...
Need Help
v-micsh-msft's avatar
v-micsh-msft
Icon for Microsoft Employee rankMicrosoft Employee
10 years ago

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's avatar
      googlogmob
      Icon for Advocate II rankAdvocate II
      10 years ago

      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's avatar
        googlogmob
        Icon for Advocate II rankAdvocate II
        10 years ago

        add ones more query

         

        Query "ПериодС":

         

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