Forum Discussion

chriswragge's avatar
chriswragge
Icon for Helper I rankHelper 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
chriswragge's avatar
chriswragge
Icon for Helper I rankHelper 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
Icon for Continued Contributor rankContinued 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
    Icon for Helper I rankHelper 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
      Icon for Continued Contributor rankContinued 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
      • chriswragge's avatar
        chriswragge
        Icon for Helper I rankHelper I
        7 years ago

        Thanks Nishantjain. Accepting your post as the solution as it put me in the right direction and answers the question.

        Appreciate all the help!!