Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Scheduled refresh on Sharepoint file returns privacy

My report is to be refrshed from Power BI Online, but it's built from Power BI Desktop.

I have two data sources that I call to feed two tables in PBI :

_All worklogs after migration (JIRA Cloud)

Multiple calls made to JIRA API

Code: Result = Json.Document(Web.Contents(https://api.tempo.io,
  [
    RelativePath="2/worklogs/project/CAAS" ,
    Query=[from="2018-10-01",to="2018-11-01",limit="5000",offset="0"],
    Headers=[Authorization="Bearer 1j1sUPc4XkLXSEvpmSuTZqioP4OqLE"]
  ]
))

_All worklogs before migration (JIRA Server)

Excel file stored on Sharepoint

Code: Source= Excel.Workbook(Web.Contents("https://n0151c.sharepoint.com/sites/..../....xls")

 

 

Once I have loaded and transformed my two tables, I have created another one that merges the two tables into one called "Worklogs".

 

I refresh my data from power BI Desktop: it works. All good!

 

Then I publish my data to Power BI Online with an autorefrwsh schedueled.

Once it's uploaded this automatic rfresh starts and returns an error straight away: [Unable to combine data] Section1/_All worklogs after migration (JIRA Cloud)/Changed Type1 is accessing data sources that have privacy levels which cannot be used together. Please rebuild this data combination. Table: Worklogs.

 

You're probably going to tell me to check the privacies enabled on my file.

Both datasources have been set to Privacy level=Public

I ignore the Privacy Levels (File>Options and settings>Options>Privac>"Ignore")

 

ANd here, I'm stuck. I don't know what to do.

The Excel document is hosted on the workplace where my Power BI datasource is hosted. LOL there should not be any problems!

 

I want to add that the problenm appeared all of a sudden, after last Power BI Online update (I think).

 

If you have any idea how to fix the problem, I'm all ears.

 

Thanks in adna

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Anonymous,

    Do you connect to on-pemises JIRA or JIRA online? Do you use gateway?

    In addition, I note that you are connecting to .xls file, Power BI Service doesn't support refreshing .xls file, please store your Excel file as .xlsx file and check if the above error message still occurs.

    Regards,
    Lydia

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi There,

      I've got a similiar problem,

       

      A college has shared an excel document from their one drive for business and shared it with me. The link takes me to the sharePoint destination.

       

      The excel document has been successfully imported with a working custom function to find the addresses from lat's and long's ( 
      -22.338304000000001,30.042366999999999) | PowerBI Desktop

      I used the web connection to the excel document as per,
      https://www.youtube.com/watch?v=t4TzHu8THoA 


      This all works perfectly on the PowerBI desktop but keeps failing in the PowerBI cloud service.


      From my understanding both sources are external
      1.) SharePoint (which is the onedrive saved location)
      2.) Google API call to retieve  

      The following additional steps where test
      1.) Non Staged Query 
      Works perfectly on PowerBI desktop | Fails in the PowerBI Cloud Service

      This is the error from the PowerBI Cloud Service:
      ##[Unable to combine data] Section1/Query1/AutoRemovedColumns1 is accessing data sources that have privacy levels which cannot be used together. Please rebuild this data combination##

      2.) Staged Query as per https://www.excelguru.ca/blog/2015/03/11/power-query-errors-please-rebuild-this-data-combination/ 
      Works perfectly on PowerBI desktop | Fails in the PowerBI Cloud Service
      This is the error from the PowerBI Cloud Service:
      ##[Unable to combine data] Section1/Sheet1 (2)/AutoRemovedColumns1 references other queries or steps, so it may not directly access a data source. Please rebuild this data combination##

      3.) Excel document was moved to sharedfolder + to "c:\PorjectFolder\data.xlsx | Both results are as follows
      Works perfectly on PowerBI desktop | Fails in the PowerBI Cloud Service
      This is the error from the PowerBI Cloud Service: Note - A Personal gateway was used and this is a NON Staged data query.

      -2147467259 Table: Sheet1.
      Underlying error message:[Unable to combine data] Section1/Sheet1/AutoRemovedColumns1 is accessing data sources that have privacy levels which cannot be used together. Please rebuild this data combination.
      DM_ErrorDetailNameCode_UnderlyingHResult:-2147467259


      Conclusion:

      It all works via PowerBI Deskop and fails on PowerBI cloud.

      External sources                                                                 | Failed
      Moved source to a local folder via the personal gateway | Failed