Missing columns in Excel PowerPivot

[ustas55]

Good Day,

We are observing/experiencing an interesting behavior of MS Excel when sourcing data from the PBI model published to the service.

We have PBI model created/sourced from SQL Server, all tables are in Import Mode. Model has been published to workspace in Premium Capacity. 

While creating a report in the service, you can see that table "Invoice Type" contains 6 columns (see picture below). However, when pointing Excel to this model via "Get Data -> From Power BI" only 2 columns are visible in the table/field list within PivotTable Fields block. Similar behavior observed with other models. 

Version of Excel is current: 2110 (Build 14527.20276).

Does anybody else experiencing (or had come across) same/similar issues?

Best Regards,

Sam

[Petsnell]

My issue was solved by changing the setting "Available in MDX" from False to True which makes sense as Excel-queries to the dataset uses MDX instead of DAX. Why it was False in the first place I have no idea. 

The picture below is from Tabular Editor. 

[Petsnell]

Was there a fix to this? I have the exact same problem now. 

[ustas55]

Yes, it's working as intented right not. However, I'm not sure how this issue was resolved.

[Petsnell]

Interesting. In my case I am missing only one column, it is very strange. Thanks for your reply. 

[v-zhangti]

Hi, @ustas55 

Please look at your permissions on the dataset and check if this is relevant?

Please refer to the following documents to see if they help you with your problem.

Analyze in Excel for Power BI - Power BI | Microsoft Docs

Create a PivotTable from Power BI datasets (microsoft.com)

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ustas55]

Good Day,

Thank you for your comment. The dataset is configured correctly and I'm able to access it and view data.

The issue I'm facing is that number of columns in the Excel Pivot Table is different (less) than in the filed list in the service. I did doublecheck, and all columns are set to be visible within dataset.

Best Regards,

Sam