Forum Discussion
Isssue Adding Web.Content Column to Web Data
Thank you for this link. When I first read the article, it sounded exactly like the issue I was experiencing but, sadly, when I tried their solution, it gave me the same error. 😥 Here are the 2 queries I tried following the instructions in the article (where UPS API Site = https://onlinetools.ups.com😞
//Query Called Solution
let
Source = Excel.Workbook(Web.Contents("https://SHAREPOINT ADDRESS/Sample%20UPS%20Data.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data]
in
Sheet1_Sheet
//Second query referencing the first query called Solution
let
Source = Solution,
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Added Custom" = Table.AddColumn(#"Promoted Headers", "Custom", each let
Local_Access_Token = ACCESS_TOKEN,
response = Web.Contents(#"UPS API Site", [
RelativePath = "api/track/v1/details/" & [Ups Tracking Number] & "?locale=en_US&returnSignature=false&returnMilestones=false&returnPOD=false",
Headers=[Authorization="Bearer " & Local_Access_Token, transId="1", transactionsrc="PowerBI UPS Tracker"]]),
jsonResponse = Json.Document(response)
in
jsonResponse)
in
#"Added Custom"
That probably means they closed that loophole. Have you considered using Power Automate instead to collect the tracking status?
Note that you still use the web connector. Unrelated, but something you'll want to correct.
We have always used the Web Connector to read our files on SharePoint. Not quite sure I understand the SharePoint Folder Connector for opening a single file.
Anyway, there are other solutions such as PowerAutomate or even using some Excel VBA I've written, but it frustrates me that this solution does not work in the PowerBI service (especially when it appears to work fine on the PowerBI Desktop). There is some strange thing occurring when trying to refresh the published dashboard that does not exist when refreshing on the desktop. I have eliminated it being a "Privacy" issue, despite reading several posts that imply that is the solution (I am completely ignoring Privacy settings). Very odd...
We have always used the Web Connector to read our files on SharePoint. Not quite sure I understand the SharePoint Folder Connector for opening a single file.It will become clearer when you add another file source, and then one more etc. You will end up with all these individual file sources and have to maintain them individually (when the token expires or the file disappears etc)
Instead, use the SharePoint Folder connector, filter for your file (or files) and consume their content via a single connection.