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.
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!
Solved! Go to 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,
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,
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.
@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.
Hi @JirkaZ ,
Does the url in each Web.Contents function are different?
Best regards,
@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.
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.