none
Refreshing excel pivot table connected to a tabular cube in PowerBI.com RRS feed

  • Question

  • Hello everyone, 

    I exhausted my searches online, getting me to solutions not relevant to my problem. The subject of refreshing excel through PowerBI.com can take you to many places. 

    We currently have an excel, with one worksheet, with one pivot table, connected to a tabular model deployed to a server. 


    We want to collaborate/share that spreadsheet with other users across the company using PowerBI.com, but we are hitting a wall while attempting to do that. 

    The pivot table on the worksheet has drill through capabilities, allowing us to drill from a product category to children products underneath it. This will naturally generate a query to the tabular server to get the products underneath a given product category. That's where the problem starts. 

    What I have tried so far:

    • I placed the spreadsheet on my One Drive for Business, opened through PowerBI.com using the "Conect, Manage and View Excel in Power BI" option
    • The spreadsheet now shows up under the "Reports" section, with an Excel-like button next to it
    • When I try to open it, it gives me a warining that says: Be careful. This workbook contains one or more queries that might be unsafe. Do you want to enable these queries? The options are "Yes" and "No", I click on the "Yes" button. 
    • Once I click on the "Yes" button, an error message this time pops up: External Data Refresh Failed. We were unable to refresh one or more data connections in this workbook. The following connections failed to refresh: NameOfMyConnectionToTabularModelInExcel. I click on the only option: OK. 

    The spreadsheet now shows up just like it would be in excel or excel online. I can even pin sections of this worksheet to a dashboard if I want. 

    But I cannot drill through products as this spreadsheet apparently is not able to get to the server with the tabular model. 

    I do have a gateway setup between PowerBI.com and my tabular model server. I can consume models from that same server through PowerBI.com, but I cannot refresh this spreadsheet. 

    Is the drill-through something possible? Anyway I can make this work? Can I change/manipulate the connection behind this excel that I just uploaded?

    Thanks in advance for your help. 

    Friday, January 22, 2016 6:54 PM

Answers

  • I've gone through this for one of my customers, and it appears this is a limitation of Excel Online, which is what Power BI uses to render Excel files. The SSAS connector works great, but not with Excel Online. The only options I see are:

    • have the data in a Power Pivot model in the Excel file (but in this you will introduce a new problem of refreshing the data, and you loose the ability to do role-based security)
    • report the results in native Power BI reports instead of Excel (but you loose the capabilities of Excel)

    Both are not what you want. My advice: go to the Power BI feedback forum and ask for something better (like native Excel reporting inside Power BI).

    Saturday, January 23, 2016 7:52 AM
    Answerer

All replies

  • I've gone through this for one of my customers, and it appears this is a limitation of Excel Online, which is what Power BI uses to render Excel files. The SSAS connector works great, but not with Excel Online. The only options I see are:

    • have the data in a Power Pivot model in the Excel file (but in this you will introduce a new problem of refreshing the data, and you loose the ability to do role-based security)
    • report the results in native Power BI reports instead of Excel (but you loose the capabilities of Excel)

    Both are not what you want. My advice: go to the Power BI feedback forum and ask for something better (like native Excel reporting inside Power BI).

    Saturday, January 23, 2016 7:52 AM
    Answerer
  • Is this still a limitation or has anything changed for this? I am seeing this exact same issue - first I get prompted for unsafe queries and then get external data refresh failed error. I thought external data sources in workbooks are supported in Power BI service.

    Thanks,

    Sonal

    Monday, September 23, 2019 2:59 PM