cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
curtisp Regular Visitor
Regular Visitor

power bi service data's field data type

In Power BI Free Service I did Get Data from a Office 365 E3 Sharepoint site to retrieve a csv file as new dataset.

 

 

I exported the dataset to Excel. It exports odc file that when I open in Excel 2013 creates new workbook, creates new pivot table with the exported dataset as source.

 

Problem is that none of the numerical values can be used as pivot values eg to sum, avg, etc.

 

I guess this is because the numerical values are strings not formatted as numbers.

 

However I can't see anywhere to set the data types for this dataset either in Power BI Service.

 

Where can I change the data types?

6 REPLIES 6
Super User IV
Super User IV

Re: power bi service data's field data type

Data modelling can be done in PowerBI Desktop or with Power Query in excel.

 

Here are some useful links for PowerBI Desktop

 

https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-shape-and-combine-data/

 

https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-data-view/

 

 

 






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





curtisp Regular Visitor
Regular Visitor

Re: power bi service data's field data type

@parry2k  yes that is true, but doesn't answer my question.

 

I had already modelled my data in Power BI Desktop report which was then published to Power BI Service.

 

Using the Power BI Service feature "Analyze in Excel" which exports odc file when opened in Excel creates connection to Power BI Service dataset and retrieves data into Pivot table.

 

In that process it appears to have lost data type for numerical values as they appear to be strings (my guess though it could be something else).

 

This is unexpected behaviour for a feature called  "Analyze in Excel".

 

How can I ensure values retain numerical data types when dataset is retrieved from Power BI Service dataset?

 

 

Highlighted
Vicky_Song Established Member
Established Member

Re: power bi service data's field data type

@curtisp, firstly, what you get is as expected. If you would like to do some aggregation operations for these numerical type data, you need to:

1). Create one measure column (sum, avg, min ..) in Power BI Desktop. For example, one measure called TotalSalesAmountTotalSalesAmount = Sum(Sales[SalesAmount])

Please check this article: https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-tutorial-create-measures/

 

2). When you click "Analyze in Excel", you will find the TotalSalesAmount measure in the ∑ pane in PivotTable fields in the.odc file. You can put it in the ∑ Value column. 

 

PivotTable1.png

curtisp Regular Visitor
Regular Visitor

Re: power bi service data's field data type

@Vicky_Song Thanks. I hadn't seen this in documentation.

 

Not your fault : ) but this is still unexpected, or at least undesirable : (  

 

Power BI Desktop and in Power BI Service both treat these numerical columns as if they were measures eg allow aggregations etc on them without first using them to create Measures.

 

But as you point out the "Analyze in Excel" feature needs to have numerical values explicitly defined as measures in order to retain their numerical data type when used by odc connection. Or maybe its better to say that Excel's Pivot Table data model needs this.

 

In order to cater to "Analyze in Excel" feature/Excel pivot table data model requirements, if I want to use the "Analyze in Excel" feature I have to create a Measure for each of my (many) numerical columns in Power BI that I want to use as Value in Excel Pivot Table via odc connection.

 

That is a lot of extra work and duplication of numerical values in Power BI (eg the original numerical value and its corresponding Measure).

 

Is anyone aware of a way to bypass or 'trick'  "Analyze in Excel" feature/Excel pivot table to simply accept columns as numerical or treat them as if they were Measures?

 

Edited to add:


Also a subtle yet important distinction here is that the "Analyze in Excel" feature is about analyzing the Power BI data model (eg Excel Power Pivot data model) not the dataset (eg Excel Power Query query). The Power BI report's data model is what is connected into Excel Pivot Table. I was expecting that I would be analysing the dataset (Power Query), not the data model (Power Pivot), because the  "Analyze in Excel" feature is accessed in Datasets.

 

This distinction impacts how the developer/architect will create the Power BI report if they want users to have the  "Analyze in Excel" feature eg will have to create Measures.

 

Also what is interesting to note is that in Excel a Power Query query model can be used as a Pivot Table data source, and it's numerical values (defined as numerical in Power Query) are treated as such in the Pivot Table, without need to invoke Power Pivot to create model Measures.

Re: power bi service data's field data type

I'm glad to see it's not just me complaining about this. Smiley Happy  I posted this over the weekend, same issue. http://community.powerbi.com/t5/Service/Analyze-in-Excel-oddity/m-p/37609

 

I don't yet have a workaround for this.

 

Treb Gatte, Power BI Red Carpet Partner | Microsoft MVP | Twitter | Blog | Blog 2

curtisp Regular Visitor
Regular Visitor

Re: power bi service data's field data type

The immediate workaround is to use Excel's OLAP Tools to create Measures in the Excel's new Pivot Table cube.

 

At least that way they don't have to be created in the Power BI model and just in Excel where they are needed. Still duplication of effort though.

 

I guess there is no way around this as Power BI Report data is stored in SSAS database which is specified in the .odc connection file and which creates the Excel data connection used by Pivot Table.

 

I have tried to use the SSAS database connection string in the .odc file to use Power Query to connect to the Power BI report's SSAS database. That Power Query could be used as Pivot Table data source.  No luck with this yet. The SSAS string is pretty long and Power Query doesn't accept longer than 128 characters for SSAS database url. 

 

Are there any configuration options in the SSAS connection that might ignore using Measures.  Perhaps  'TypedMeasureColumns = false])' ?

 

 

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors