Refreshing a web source with an API Key and a dynamic Authorization Token fails

[Anonymous]

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!

[v-lid-msft · Accepted solution]

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,

[Anonymous]

Hi @v-lid-msft , thank you, it now also works in the service for me. 

[JirkaZ]

@v-lid-msft This will work in the Desktop, but not in service!

[Anonymous]

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.

[JirkaZ]

@Anonymous Yep - that's not a problem. But sometimes it won't allow you to have 2 Web.Content calls within one query and will throw an formula firewall exception, because it's not able to validate the second call. And yes - I use relative path at all times. 

[v-lid-msft]

Hi @JirkaZ ,

Does the url in each Web.Contents function are different?

Best regards,

[JirkaZ]

@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.