Forum Discussion
Refreshing a web source with an API Key and a dynamic Authorization Token fails
Hello,
i have the two following tables:
Table1:
let
Source = Json.Document(Web.Contents("http://my.url/", [Headers=[#"x-api-key"="XXXXXXXXXXXXXXXXXXXXX"], RelativePath="the/restofmyurl"])),
#"Converted to Table" = Record.ToTable(Source),
#"Removed Columns" = Table.RemoveColumns(#"Converted to Table",{"Name"})
in
#"Removed Columns"
Table2:
let
Source = Json.Document(Web.Contents("http://my.url/", [Headers=[#"x-api-key"="XXXXXXXXXXXXXXXXXXX", Authorization="Bearer "&Table.FirstValue(Table1)], RelativePath="another/partoftheurl12345"])),
#"Converted to Table" = Record.ToTable(Source),
#"Expanded {0}" = Table.ExpandListColumn(#"Converted to Table", "Value"),
#"Expanded {0}1" = Table.ExpandRecordColumn(#"Expanded {0}", "Value", {"nummer", "kennzeichen", "anschaffungsdatum", "vorname", "nachname", "einstellungsdatum"}, {"Value.nummer", "Value.kennzeichen", "Value.anschaffungsdatum", "Value.vorname", "Value.nachname", "Value.einstellungsdatum"})
in
#"Expanded {0}1"
the first Table is getting an JW-Token and the second Table is using that Token in its query as a dynamic field and an API-Key to get Data. This solution is working fine in Power BI Desktop but when I want to publish it to the service I am having trouble refreshing the data.
If i try to refresh the Data I'm getting the following error message: [Unable to combine data] Section1/6bcfd33a-514e-4578-b592-7eb7fd8c4c72 (2)/AutoRemovedColumns1 references other queries or steps, so it may not directly access a data source. Please rebuild this data combination. Table: 6bcfd33a-514e-4578-b592-7eb7fd8c4c72 (2).
Thanks for the help!
Hi Anonymous ,
Could you please try to verify the result if combine them into one query?
let TokenSource = Json.Document(Web.Contents("http://my.url/", [Headers=[#"x-api-key"="XXXXXXXXXXXXXXXXXXXXX"], RelativePath="the/restofmyurl"])), TokenValue = Table.FirstValue(Table.RemoveColumns(Record.ToTable(TokenSource),{"Name"})), Source = Json.Document(Web.Contents("http://my.url/", [Headers=[#"x-api-key"="XXXXXXXXXXXXXXXXXXX", Authorization="Bearer "& TokenValue], RelativePath="another/partoftheurl12345"])), #"Converted to Table" = Record.ToTable(Source), #"Expanded {0}" = Table.ExpandListColumn(#"Converted to Table", "Value"), #"Expanded {0}1" = Table.ExpandRecordColumn(#"Expanded {0}", "Value", {"nummer", "kennzeichen", "anschaffungsdatum", "vorname", "nachname", "einstellungsdatum"}, {"Value.nummer", "Value.kennzeichen", "Value.anschaffungsdatum", "Value.vorname", "Value.nachname", "Value.einstellungsdatum"}) in #"Expanded {0}1"
Best regards,
7 Replies
Hi Anonymous ,
Could you please try to verify the result if combine them into one query?
let TokenSource = Json.Document(Web.Contents("http://my.url/", [Headers=[#"x-api-key"="XXXXXXXXXXXXXXXXXXXXX"], RelativePath="the/restofmyurl"])), TokenValue = Table.FirstValue(Table.RemoveColumns(Record.ToTable(TokenSource),{"Name"})), Source = Json.Document(Web.Contents("http://my.url/", [Headers=[#"x-api-key"="XXXXXXXXXXXXXXXXXXX", Authorization="Bearer "& TokenValue], RelativePath="another/partoftheurl12345"])), #"Converted to Table" = Record.ToTable(Source), #"Expanded {0}" = Table.ExpandListColumn(#"Converted to Table", "Value"), #"Expanded {0}1" = Table.ExpandRecordColumn(#"Expanded {0}", "Value", {"nummer", "kennzeichen", "anschaffungsdatum", "vorname", "nachname", "einstellungsdatum"}, {"Value.nummer", "Value.kennzeichen", "Value.anschaffungsdatum", "Value.vorname", "Value.nachname", "Value.einstellungsdatum"}) in #"Expanded {0}1"
Best regards,v-lid-msft This will work in the Desktop, but not in service!
Hi JirkaZ , did you use RelativePath and is your Base URL returning a HTTP 200 answer without an API-Key? I think otherwise it wont work. I also set the privacy level in the desktop on None and in the Service on Public.
Hi v-lid-msft , thank you, it now also works in the service for me.
Anonymous This is one of the formula firewall "features". Unfortunately (or as far as I was able to find out when working with APIs) the only way around is to create a custom connector.
I'd be more than happy if somebody from MSFT stepped in and proved me wrong.
