Connect to a Power BI dataset from Excel as a flat table, rather than a pivot table

[mwjones61]

I want my end users to be able to connect to my Power BI dataset from their Excel Desktop. The issue is that I want the data to appear in a regular flat table, as opposed to a pivot table. This is for end users, so I want the process to be as simple as possible. Ideally, I would like to configure something behind the scenes so it will work withouth them having to do anything. Is there a way to do this without making my users jump through hoops?

[v-binbinyu-msft]

Hi @mwjones61 ,

Please try to connect Power BI Dataset in the local excel desktop,

For more details, you can read related document: Power BI dataset experience in Excel - Power BI | Microsoft Learn

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mwjones61]

Thanks for your response! I don't seem to have the Insert Table option in my Excel desktop or Excel from the web.  My only option is to use a pivot table.  My build level for Excel desktop is 16.0.16731, which is higher than the required build level.  Would you be able to tell me why I am not seeing this Insert Table option? I assume it would allow my end users to connect to the Power BI Dataset as a flat file? Is that correct?

[v-binbinyu-msft · Accepted solution]

Hi @mwjones61 ,

I test it in my excel desktop, it works well.

Please refer the requirements and limitations:

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mwjones61]

I have checked with my tenant administrator and have verified that the first two requirements are fulfilled, but I still don't seem to have the Insert Table option in my Excel desktop or Excel from the web.  My only option is to use a pivot table.  

I have also verified that my build for Excel is above 16.0.

But I sill am not give the option of connecting to Excel as a Table.

[DennesTorres]

Hi,

I haven't tested this... but the first thing which comes to my mind is to connect from Excel to a datamart using the SQL Endpoint instead of  the default dataset.

Kind Regards,

Dennes

[mwjones61]

Thanks for your response. As I understand it, datamarts are a public preview feature in Power BI Premium (and Premium Per User). My company only has a Pro license, unfortunately.