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

[chriswragge]

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?

[Nishantjain]

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]

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]

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]

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 · Accepted solution]

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]

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

Appreciate all the help!!

[Nishantjain]

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