Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Formula.Firewall: Query references other queries or steps

Hi Team,

 

Hope everyone is doing well!

 

Its been 2 months i have started using Power BI, but in the recent times i am hitting many road blocks while working with SharePoint data in Power BI desktop.. I am listing the issue i am facing on a very frequent basis. 

 

Issue #1: Formula.Firewall: Query references other queries or steps, so it may not directly access a data source. Please rebuild this data combination

 

Description:  I have two tables A and B which are directly accessed from datasource(sharepoint Online List). I am building the third one using Merge Queries as New (Inner Join). It gets successfully build and i can publish the same onto power bi online. Everything seems to be ok till this level. But when i open the same pbix file and click on  refresh, it fails with this problem. If power is proactively catching this issue, then y the hell are we given a option to build Merge query as new. Please advise.

 

Issue #2:  From past 1 week, my data refresh fails with the error "
Data source error: SharePoint: Request failed: The remote server returned an error: (429). (429 TOO MANY REQUESTS). The exception was raised by the IDataReader interface.
Cluster URI: WABI-NORTH-EUROPE-redirect.analysis.windows.net
Activity ID: 29b8a394-2a48-e36f-d222-0b30f6a44e5a
Request ID: 075325d6-c4dd-6c5f-f529-092adef3e127
Time: 2017-03-29 11:40:00Z "

 

Description: The account with which i am publishing the datasource is a pro account, still it fails with the above error. Very strange right

 

Issue 3: My data refresh fails with one more error "Processing error: The credentials provided for the SharePoint source are invalid. (Source at https://abc.com/sites/xyz)
Cluster URI: WABI-NORTH-EUROPE-redirect.analysis.windows.net
Activity ID: d2e62740-7b91-4c34-965c-5e2a12e11ae7
Request ID: 6afebd7c-354f-42a7-9948-ee5a28b14773
Time: 2017-03-29 10:42:46Z
"

 

Description: its the same credentials with which i am publishing the data set and scheduling the data refresh. stil it throws the above error. When i update it again using Oauth2, it says the credentials updated. but when i trigger on demand refresh it fails again with 429 bad request or some other stuff. Also, the account has full access on Sharepoint online site.This is seriously annoying

 

 

Pleased advise on how to handle these issues as our reports went live and its very annoying for the users to report why there is a warning symbbol next to data set.

 

 

 

18 REPLIES 18
AJZoerner
Frequent Visitor

I am also facing Issue #2 and Issue #3. Were you ever able to resolve this?

 

Thanks,

Amy Jo

Anonymous
Not applicable

HI Amy,

 

Microsoft claims it to be a know issue with SharePoint API for Power BI. The way API for power bi works is when ever the schedule refresh triggers, power bi runs query on each and every record against the datasource. so, if you have 2000 records, it will 2000 times the queries will be run against the datasource.

 

In the current implementation of Power BI, for any of the cloud hosted serverices like SharePoint Online, CRM tools like Salesforce the schedule refresh happens through token. So the current expiry of token is 1 hour. so if the sharepoint data is huge and if the dataset takes more than 1 hour to refresh, it will result in token expiry and we end up in above errors.

 

In future , this token time might increase to 2 hrs. but what if the sharepoint data size increases, even microsoft doesn't have a clue. so there are few workarounds to optimize the refresh time which i have published . Please try and let me know if this helps you

 

https://community.powerbi.com/t5/Desktop/Optimization-of-Queries-with-SharePoint-Online-List-as-Data...

 

 

 

 

@Anonymous,

 

Thanks for your reply. This is very useful information.

 

Amy Jo

Anonymous
Not applicable

Hi everyone!

I'm having similar error as you Issue #1.
[Unable to combine data] Section1/Consulta1/AutoRemovedColumns1 is accessing data sources that have privacy levels which cannot be used together. Please rebuild this data combination.

 

In my case I'm trying to conect to JIRA:

Query1 -> Connects to JIRA to get a list of issues.

Query2 -> Take the list generated in Query1 and connects to JIRA to look for info of every issue.

 

I've tried the link way above, I change the Privacy and gives me the same error.

Any other way?

Thanks!

 

 

Anonymous
Not applicable

HI,

 

In your queries , are you using "Merge Queries as New" by any chance ?

 

Is it possible to rebuild the data model by using relationships instead of creating a new query ? 

 

 

Anonymous
Not applicable

Hi @Anonymous!

No, I'm not using any kind of Merge.
I think that It's not possible ussing relations. It´s needed a JSON query for every issue to bring the info.

I'm modifying the JIRA content pack in which, first of all uses a instruction like this to bring the list of issues:

let
    FetchPage = (url as text, pageSize as number, skipRows as number) as table =>
    let
        contents = Web.Contents(URL,[RelativePath = "rest/api/2/search",Query = [maxResults= Text.From(pageSize), startAt = Text.From(skipRows)]]),
        json = Json.Document(contents),
        Value = json[issues],
        table = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
    in
        table meta [skipRows = skipRows + pageSize, total = 500]
in
    FetchPage

Once I have the list of all id issues, I need to do a query for every issue. Something like this:

 

let
    SearchIssue = () =>
    let 
        Source = FetchPages("", 500),
        table = Table.ExpandRecordColumn(Source, "Column1", {"id"}, {"id"}),
        Search = (id) => 
        let
            Issue = Json.Document(Web.Contents(URL,[RelativePath="rest/api/2/issue/"&id]))
        in
            Issue,
        Output = Table.AddColumn(table, "incidencia",each Search([id])) 
    in
        Output  
in
    SearchIssue

 

I don't find the solution.

 

Anonymous
Not applicable

HI,

 

I believe you are facing this issue with Power BI Desktop. Can you please try by un-check the "Parellel loading of Tables" and re run the refresh, hope it might avoid this issue.

 

Regards,

Sivapratap. 

Anonymous
Not applicable

Hi @Anonymous,

 

I have un-checked the "Parellel loading of Tables" and the online refresh gives me the same error:

[Unable to combine data] Section1/Datos/AutoRemovedColumns1 is accessing data sources that have privacy levels which cannot be used together. Please rebuild this data combination.

 

The refresh works on my desktop but online It doesn't work.
Thanks for the answer!

Hi Mozasf

 

Can you confirm if you managed to solve the issue re refreshing JIRA data via the Power BI service?

 

I have an identical situation using JIRA data - i.e. loads OK via PBI Desktop but not PBI Service - and am struggling to find a solution.

 

Any help appreciated.

 

 

Thanks

Anonymous
Not applicable

What is the error that is getting thrown when the refresh fails in service? Also, can you mention the duration once the refresh triggered and failed? you might get useful lnformation if you can do a fiddler trace once you start the refresh and share the details with Microsoft through a  support ticket.

 

FYI , If you are pro user, you can raise a support ticket .

 

 

Hi @Anonymous

 

Thanks for replying.  The error I'm getting is "Message[Unable to combine data] Section1/Issue List/AutoRemovedColumns1 is accessing data sources that have privacy levels which cannot be used together. Please rebuild this data combination."

 

The scenario I have is the same as @Anonymous where I make a first call to the JIRA API to retrieve the issue records I'm interested in; then loop through these records to make a second call to query additional info associated with each issue.  In the second call, the URL path is dynamic as it includes the id of the record being queried.

 

Based on the commentary provided by @mithrandir in the thread referenced by @Anonymous, it sounds like there's nothing that can be done to work around this other than manually refreshing data in PBI Desktop,

Anonymous
Not applicable

Hi @ayup,

For the moment I haven't found a solution.

I refer you to another post where we are looking for an answer:
https://community.powerbi.com/t5/Service/Unable-to-combine-data-accessing-data-sources-that-have-pri...

Hi @ayup @Anonymous,

 

Finally have you found a solution?

 

I have the same issue with JIRA.

 

Thnaks.

Hi @tuken

 

I'm afraid not...we're still having to refresh JIRA data manually in PBI Desktop and republish to PBI Service

 

Ayup

tuken
Frequent Visitor

OK, thank you for your answer. 

 

In case I find a solution or workaround, I'll let you know.

Anonymous
Not applicable

Hi @tuken!

 

I'm still thinking about the problem but I'm afraid there's no solution at the moment.

 

If you find any solution, please share it with us.

Thanks!

I'll do it.

 

Regards.

v-sihou-msft
Employee
Employee

@Anonymous

 

Issue #1: Formula.Firewall: Query references other queries or steps, so it may not directly access a data source. Please rebuild this data combination

 

Please try to ingore the Privacy in either Global or Current File setting under Option.

5.PNG

 

If it still doesn't work, I suggest you have your third query reference your existing query to do merge. Please refer to blog below:

 

Power Query Errors: Please Rebuild This Data Combination

 

 

For the second issue,

 

Data source error: SharePoint: Request failed: The remote server returned an error: (429).

 

I can't reproduce it. It might the issue with the Cluster server, you may try to refresh later.

 

Regards,

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.