Power BI Dataset Experience in Excel

[bi_analytics]

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.

[Martaspg]

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]

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-con...

"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."

[foodd]

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

[Martaspg]

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,

[bi_analytics]

Does anyone know how to fix this problem?

[bi_analytics]

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.