Forum Discussion
How to securely store and use token and secret for API request in Power Query M ?
Oh, sorry about that... didn't realize that POST didn't support authentication in Web.Contents. I don't know any use of keyvault, and you would likely run into the same issues you face here using it.
I think the only option you would have is to write your own connector, which is given much more freedom on how it operates. Note, that if you write your own connector you cannot publish your report online unless you use a gateway (in which case you might as well just store the secret on the gateway machine and configure access to that speratly). Also, note that this does not work with importing data into Excel.
Microsoft EmployeeYour best bet it to add the option: ApiKeyName = "Authorization". However, you would need to encode the encoded authorization by hand. When you are prompted for credentials, select ApiKey for login type.
let
url = "https://url_of_service/",
body = "{ ""profiles"": [ { ""id"": ""<PROFILE1>"", ""id2"": ""<PR1>"" }, { ""id"": ""<PROFILE2>"", ""id2"": ""<PR2>"" } ], ""date_start"": ""2023-01-01"", ""date_end"": ""2023-01-31"", ""metric"": ""metric1"", ""dimensions"": [ {""type"": ""date.day""}, {""type"": ""id""} ]}",
options = [
Headers = [
#"Content-Type" = "application/json; charset=utf-8"
],
Content = Text.ToBinary(body),
ApiKeyName = "Authorization"
],
response = Web.Contents(url, options),
data = Json.Document(response),
#"Converted to table" = Record.ToTable(data)
in
data
Thanks! Would this work with a scheduled refresh?
- artemus
It would last as until the token expires. When this happens you will get an email and need to provide a new updated token.
Thanks, I am going to test this and will let you know if it works!