Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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
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
Oh, and the synatx of the vars in .txt file could be in any format to make this easier. E.g. "Hello "&var1
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),
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
Thanks @Nishantjain. Accepting your post as the solution as it put me in the right direction and answers the question.
Appreciate all the help!!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
99 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |