Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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 des...
General Comment
Interesting Links
Need Help
Show and Tell
Tutorial Requests
Anonymous's avatar
Anonymous
Not applicable
9 years ago

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/m-p/204486

 

 

 

 

AJZoerner's avatar
AJZoerner
Frequent Visitor
9 years ago

Anonymous,

 

Thanks for your reply. This is very useful information.

 

Amy Jo

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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's avatar
      Anonymous
      Not applicable
      9 years ago

      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's avatar
        Anonymous
        Not applicable
        9 years ago

        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.