Forum Discussion

bi_analytics's avatar
bi_analytics
Frequent Visitor
3 years ago

Power BI Dataset Experience in Excel

Hi all.

 

I'm trying to help a colleague connect to a Power BI dataset via Excel.

 

We used this article from Microsoft as a guide:
https://learn.microsoft.com/en-us/power-bi/collaborate-share/service-connect-excel-power-bi-datasets.

 

We want to load a specific table to an Excel file as a table (not a PivotTable).

 

According to the Microsoft article linked above, this should be straightforward:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

However, when we try this, we only get the option to insert the whole dataset as a PivotTable:

 

 

 

 

 

 

 

 

I've checked that all the requirements listed in the article are in order:

  • Your organization’s tenant administrator must enable the "Users can work with Power BI datasets in Excel using a live connection tenant" setting. ✔️

  • You must have Excel Desktop (Builds greater than or equal to 16.0.15128) and Excel for the web. ✔️
    (We're currently on Microsoft® Excel® for Microsoft 365 MSO (Version 2306 Build 16.0.16529.20226) 64-bit)

  • You must have Build permission to the Power BI dataset or have at least a Contributor role in the Power BI workspace containing your dataset. ✔️

  • You must have a Power BI license: Free, Pro, or Premium Per User (PPU). ✔️
    (My colleague and I both have Pro licenses.)

  • You can use Excel workbooks containing refreshable Power BI data in both Excel Desktop and Excel for the web. ✔️

 

Do you know why our "Power BI dataset connection experience" differs from what the Microsoft article described above?


I appreciate any help you can provide.

Need Help

6 Replies

  • foodd's avatar
    foodd
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Do you have both Pivot Table or just Pivot Table, and what is the source of the Data Set?

     

    • bi_analytics's avatar
      bi_analytics
      Frequent Visitor
      3 years ago

      Hi foodd.


      I can only see PivotTable. (See the second screenshot in my original post.)

      The Power BI dataset is connected to multiple sources, a mixture of different Web APIs, and some Excel files.

    • Martaspg's avatar
      Martaspg
      Icon for Advocate I rankAdvocate I
      2 years ago

      Many thanks! I only see "pivot tables" and not "inser tables" option - couldn't clarify the reason for this.

      The workaround you indicated works for me, it's not the ideal solution but at least it works,

  • Martaspg's avatar
    Martaspg
    Icon for Advocate I rankAdvocate I
    2 years ago

    I have the same problem, when trying to connect to any dataset, I only get the "insert pivot table" and not the "insert table" option. All the requirements fulfilled

     

    • bi_analytics's avatar
      bi_analytics
      Frequent Visitor
      2 years ago

      Hi Martaspg.

      I've not yet found a solution. However, I've found two workarounds to achieve the same goal.

       

      1. Connect to Excel using a live connection through a Power BI visual.

      2. Connect to Excel using a live connection via OneLake. This is my preferred method, allowing me to select the tables and columns I want to connect to.

      It's described in this article:
      https://powerbi.microsoft.com/nb-no/blog/announcing-new-ways-to-create-connected-tables-in-excel-connected-to-power-bi/

      "Create a connect workbook from the OneLake Data Hub

      We’re also starting to roll out a new Export with live connection (.xlsx) option for the OneLake Data Hub experiences.

      When you navigate to a dataset in Power BI, you can use the Tables side pane to pick a table.

      We’re adding to the Export menu a new Export with Live connection (.xlsx) option. This creates a connected Excel workbook that shows a Connected Table just like we showed above, or with the Export to Excel capability when viewing an interactive reports."