Forum Discussion

chriswragge's avatar
chriswragge
Helper I
7 years ago
Solved

Using PowerBI Parameters Names in .txt files, to build dynamic API queries

Hi there,   I have a query that uses the PowerBI 'Web' source to pull reporting data from an API (POST method). I also have the client ID, client secrect, body, and token into seperate .txt files ...
Need Help
  • Nishantjain's avatar
    Nishantjain
    7 years ago

    I just worked it out myself using the same exact logic. You can use this in a function too. Glad you were able to get this working

     

    let
Source = (samplebinary as binary) =>
let
Source = samplebinary,
ConvertToText= Text.FromBinary(Source,1252),
ReplaceParam1 = Text.Replace(ConvertToText,"var1", param1),
ReplaceParam2 = Text.Replace(ReplaceParam1 ,"var2", param2)
in
ReplaceParam2
in
Source
Nishantjain's avatar
Nishantjain
Continued Contributor
7 years ago
I would suggest you create a function to dynamically adjust the body. In the function, I will pass the binary content and then edit it using the parameters

If you don't know how to create functions, let me know and I can help you with it.

Thanks
chriswragge's avatar
chriswragge
Helper I
7 years ago

Thanks so much for the help Nishantjain!!

So if i had a .txt file with the text 

Hello [var1]
Foo [var2]

What would my PowerBI code look like to read this .txt file in and replace

  • [var1] with a my PowerBI "Param1" value ("World")
  • [var2] with a my PowerBI "Param2" value ("Bar")

 

Oh, and the synatx of the vars in .txt file could be in any format to make this easier. E.g. "Hello "&var1

  • Nishantjain's avatar
    Nishantjain
    Continued Contributor
    7 years ago
    Chris

    I dont have my laptop with me so cant write the whole code from my mobile. But lets try something

    Do the following and send me the code

    1. Import the text file in a new query
    2. Carry out all the steps in that query to create the logic of replacing the values in the text file. For example, use the "replace value" function to replace the values in the text file

    Send me the code and I will change it into a function

    Thanks
    Nishant
  • Nishantjain's avatar
    Nishantjain
    Continued Contributor
    7 years ago
    Hey Chris

    Ignore my previous message. I just realised that you sre trying to manipulate the binary. I am not sure if my suggestion would work
    • chriswragge's avatar
      chriswragge
      Helper I
      7 years ago

      Actually, I think I have it. It's really just as simple as

       

      let
    token = Text.FromBinary(Token,1252),    
    clientID = Text.FromBinary(ClientID,1252),
    clientSecret = Text.FromBinary(ClientSecret,1252),
    url = "https://analytics.adobe.io/api/"&clientID&"/reports",
    body = Text.Replace(Text.Replace(Text.FromBinary(Visits,1252),"[var1]",Param1),"[var2]",Param2),
      • Nishantjain's avatar
        Nishantjain
        Continued Contributor
        7 years ago

        I just worked it out myself using the same exact logic. You can use this in a function too. Glad you were able to get this working

         

        let
Source = (samplebinary as binary) =>
let
Source = samplebinary,
ConvertToText= Text.FromBinary(Source,1252),
ReplaceParam1 = Text.Replace(ConvertToText,"var1", param1),
ReplaceParam2 = Text.Replace(ReplaceParam1 ,"var2", param2)
in
ReplaceParam2
in
Source