Forum Discussion

curtisp's avatar
curtisp
Helper II
10 years ago

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

    • curtisp's avatar
      curtisp
      Helper II
      10 years ago

      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?

       

       

      • Vicky_Song's avatar
        Vicky_Song
        Impactful Individual
        10 years ago

        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 TotalSalesAmount, TotalSalesAmount = 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.