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 for security reasons. PowerBI is currently loading these in, and then performing the query like so.

 

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.FromBinary(Visits,1252),
        
    Source = Json.Document(Web.Contents(url, 
    [
        Headers = 
            [
            #"Authorization"= token,
            #"Content-Type"="application/json", 
            #"Accept"="application/json", 
            #"x-proxy-global-company-id"=clientID,
            #"x-api-key"=clientSecret 
            ],
        Content = Text.ToBinary(body)
    ]
    )),

Things are working great, except for one thing - I cannot figure out how put PowerBI parameter names within my 'body' text file, to then dynamically adjust my API request when I load the text file in.

 

If I take the 'body' out of the text file and put it in my main query, it all works fine e.g. using 'test_p' as my param

 

    body = "{
    ""rsid"": ""xxxxxxxxxxxxxxxxxxxxxxx"",
    ""globalFilters"": [
        {
            ""type"": ""dateRange"",
            ""dateRange"": ""2019-03-01T00:00:00.000/2019-04-01T00:00:00.000""
        }
    ],
    ""metricContainer"": {
        ""metrics"": [
            {
                ""columnId"": ""0"",
                ""id"": ""metrics/"&test_p&"""
            }
        ]
    },

...but this kinda defeats what I am trying to do with keeping a whole bunch of queries seperately. 

 

Is there any way to do this?

 

 

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

7 Replies

  • 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