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
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
    Источник