Isssue Adding Web.Content Column to Web Data

[directvman67]

I am hoping someone will read this and have a solution.  I will try to keep my post as brief as possible, but also with enough detail to understand the current situation.

• I have a PowerBI dashboard that has, as one of its data sources, a table with a column called UPS Tracking Number. 
  • This table is read from a tab of an Excel file on a SharePoint site.
  • As such, this table is loaded using a Web Data Source.
  • The table is read using Organizational Account for the credentials (M365) and a Privacy Level of Organizational (although, as I will mention below in what I have tried, I have used other settings on the Privacy to resolve my issue.).
• I want to use the UPS APIs to load the tracking details for each UPS Tracking Number in to this table.
  • I tried defining functions that used the PowerQuery API calls and then adding columns to the aforementioned table  using the Invoke Custom Function, but this led to errors when I published about not being able to refresh dynamic dynamic data sources in the Power BI service. 😥
• So, after much time and effort scouring the internet, I found another solution that avoided using Functions: that is, to add a column to the aforementioned table using Web.Contents in PowerQuery. 😊
  • So, I put together the PowerQuery shown in the code below.  I set the Credentials for onlinetools.ups.com to Anonymous (the only option that works) and the Privacy Level to Organization (to match that of the aforementioned table).
  • This added column works perfectly fine when used in the PowerBI Desktop application. 😁
  • However, when I publish the dashboard to the PowerBI Service and try to refresh it, I get the error shown in the image below. 😥
• So, here is what I have tried so far...
  • If I use the code below in a stand-alone query (enter it via the Advanced Editor) and publish the dashboard, there are no errors with the refresh. 😊
  • I have tried aligning all the Privacy to the same value, as well as setting the global Privacy to "Always Ignore Privacy Level settings"...but to no avail. 😥
  • I tried replacing the call to another query (AccessTokenQuery) with a hard coded Bearer Token, but still nothing. 😥
  • I tried replacing the dynamic use of [Ups Tracking Number] with a hard-coded UPS Tracking Number, but that failed too.

let

Local_Access_Token = AccessTokenQuery,

    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

Does anyone have any idea what I am doing wrong?  Any guidance is greatly appreciated!

[cpwebb]

First and most importantly: it looks like you're trying to connect to an API that requires OAuth2 authentication, and in order to do that you're going to need to create a custom connector as discussed here https://blog.crossjoin.co.uk/2021/08/29/connecting-to-rest-apis-with-oauth2-authentication-in-power-... Even if you get past this particular issue, you might well run into other issues later on you can't work around. I suspect that building a custom connector might also help with this issue too.

For this specific error, this section on partitions in a presentation on data privacy that I gave a few years ago will explain the cause: https://youtu.be/RbqCA6srkmM?t=2002 (you'll probably want to watch the whole presentation for context though). Your response step is indeed accessing a data source and accessing data from other partitions so generating the string used for RelativePath and the record used for Headers in separate steps might help. You can also try setting the Skip Test Connection option on the data source in the Service (see https://blog.crossjoin.co.uk/2019/04/25/skip-test-connection-power-bi-refresh-failures/) as well. 

Chris 

[directvman67]

Thank you for your video.  Very interesting and well done.  That being said, here are some comments and my results trying all the things in your video:

I am not sure why you say I need a custom connector.  I have a single query that returns the API key and simply use that as my bearer token in the next query that calls for the status data.  That being said, as I said in a previous comment, even if I hard code the Bearer token (to one that is fresh and active), I still get this same PowerBI error.

I also tried all these things (many mentioned in your video), and none succeeded. 😥

• Break things into multiple steps
• Set Privacy Levels all same
• Ignore Privacy Levels
• Set Skip Test Connections on all data sources
• Try using functions
• Try using one big query
• Try splitting into smaller queries

[lbendlin]

I am not sure why you say I need a custom connector.

You need that if you want to refresh that semantic model in the Power BI service.

[cpwebb]

Just to add to that (and to reiterate some of the points made in my blog post linked to above): if I'm correct that you're trying to connect to an API that requires OAuth2 authentication, the reason you need to build a custom connector is that Power Query/M as it exists today in Power BI and Excel do not have the functionality required to support doing this in a reliable and secure way. The functionality only exists in custom connectors.

[directvman67]

Thank  you for your feedback.  I did try some of the steps you mentioned, for example setting Skip Test Connection option, but that did not solve the issue.  I will continue to work on it and try some of your other suggestions.

[directvman67]

Other things I have tried:

• I tried separating the queries into 2, as per this article (https://excelguru.ca/power-query-errors-please-rebuild-this-data-combination/), but to no avail
• I tried making one large PowerQuery that does everything: loads the external file, calls the API for the Access Token, and calls the API for the tracking data, but that also failed
• I tried disabling other settings per this posting (https://community.fabric.microsoft.com/t5/Desktop/Query-references-other-queries-or-steps-so-it-may-...), but that didn't work either.  
• Tried setting both data sources to Skip Test Connection on the Power BI Service settings, but that didn't solve anything either.

So far, nothing has worked. 😭

[v-menakakota]

Hi @directvman67 ,

Thanks for reaching out to the Microsoft fabric community forum. 

Thanks for trying all those steps and sharing the results. Since the query works in Desktop but fails only in the Power BI Service, and the usual privacy/connector workarounds didn’t resolve it, the next best step would be to open a Microsoft support ticket so the engineering team can check what’s happening on the backend.

Please refer below link on how to raise a contact support or support ticket. 
How to create a Fabric and Power BI Support ticket - Power BI | Microsoft Learn 

Best Regards, 
Community Support Team 

[directvman67]

Thank you.  I did open a support ticket: TrackingID#2511100010001281.  I have sent the Support Professional from Microsoft Power BI support team several messages and I am awaiting a response.

[v-menakakota]

Hi @directvman67 ,

Since you've already submitted the ticket, the support team will handle the issue and resolve it soon. They will get back to you.

Thank you

[lbendlin]

As such, this table is loaded using a Web Data Source.

Very much not recommended. Use the SharePoint Folder connector instead.

[Jai-Rathinavel]

@directvman67 Please disable the privacy level setting to resolve the above issue. 

Thanks,
Jai

[directvman67]

[lbendlin]

Power Query Errors: Please Rebuild This Data Combination

[directvman67]

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"

 

[lbendlin]

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.

[directvman67]

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

[lbendlin]

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.

[directvman67]

Sorry, as I mentioned, I tried that and it did NOT solve the issue. 😥