Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
sadhnabatra
Frequent Visitor

Importing Data models from power BI service into excel and using pivot tables

Hey,

 

I have created a report in Power Bi Desktop using direct query.In this report I have created few measures , columns and hierarchy.After publishing it online in Power BI service I connect to the data using Power Bi publisher for Excel .

 

In excel I get the dataset to be used in pivot table. But I notice that in the pivot table fields-- 

 

1)The hierarcy disappears.

2)I can only put measures in the Values section

3)"Add to Values" option is grayed out if I right click on a calculated column.

 

I would like to use these option in Excel when I import report from Power BI service online into Excel.

4 REPLIES 4
sadhnabatra
Frequent Visitor

Hey,

 

I have created a report in Power BI Desktop using direct query.It contains few measures, calculated columns and hierarchies in addition to thoses columns coming from SQL database. After publishing it online on Power BI Service  I connect to the data in Excel using the Power BI publisher for Excel.

 

In excel I see all the fields in the pivottable field list. But I am unable to do the following:

 

1) I cannot put calculated columns in the values section. The option "Move to Values" is grayed out when I right click.

2)Hierarchies disappear.

3)Can only add measure to the values section.

 

I would like to use all these in excel when I import a report from Power BI service. 

@sadhnabatra That could be limitation of using DirectQuery as when used with Import I can see hierarchies and calculated columns. Only measures can be added to Values section that is standard pivottable feature and so regardless of Import or DirectQuery. So change your method of connecting to data source from DirectQuery to Import. Also you can use Analyze in Excel option that you get once your dataset is published to power bi service.

@ankitpatira Thank you for the response.

 

By using import I am able to see hierarchies in excel but I cannot put calculated columns in the values section. Even the numeric columns that come from sql server cannot be put in the values section. 

 

My client wants to be able to form their own reports in excel using model imported from Power BI. How will this be possible if none of the numeric columns can be put in the values section?

Hi @sadhnabatra,

In excel, I am not able to put calculated columns in the values section, however, I am able to put numeric columns that come from SQL Server in the values section. Could you please post sample data of your numeric columns?

in addition, according to my knowledge, calculated column are always used in the ROWS, COLUMNS, or FILTERS area of PivotTable but not in the values section.

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.