Column with different data types in Power BI

0
votes

I would need your support if possible..

Currently within my dataset there is a table "SUM" like below, where columns "Jan" and "Feb" contains both decimal and whole numbers data types.

Is it possible to convert some of the whole number to currency and some other to decimal with 2 decimal places ?

SUM TABLE

title Jan Feb
New users 80 90
users 200 150
BV 74562.62 82617.93
Charge 0.063629 0.061740
______________________________________

desired output

title Jan Feb
New users 80 90
users 200 150
BV $745,626 $826,179
Charge 0.06 0.06
______________________________________

I think that in Power BI a column has only one data type.

Is there any way to achieve the desired output?

Thanks in advance

3
powerbipowerbi-desktop
You need to re-structure your data. In a proper data model, there is no such thing as different data types in the same column. The answers that suggest to use DAX to address the problem will take you in a very wrong direction. Power BI requires a proper star schema: en.wikipedia.org/wiki/Star_schema – RADO

3 Answers

1
votes

It would be best to structure your data, into a format that it can be used for example, change data types and creating measures.

In this example, using the query editor, you need to first Unpivot your Jan/Feb columns from columns to rows

Original Data

With unpivot it becomes this:

Unpivoted

You then need to Pivot your data on the Title, select the column 'Title' Pivot on the Values column created in the previous step

Final data

This now allows you to correct your formatting, create measures, and also map by month better, without the need for complicated DAX formatting when creating measures

0
votes

To display several data type within a column, you need add new measure for each month you prepare to display and accept the answer if helping :)

Here is the dax formula for Jan, you may need calcuate for Feb if necessary:

Measure = IF(SELECTEDVALUE(Sheet1[title])="BV",FORMAT(SUM(Sheet1[Jan]),"Currency"),
             IF(SELECTEDVALUE(Sheet1[title])="Charge",FIXED(SUM(Sheet1[Jan]),2),
                   FORMAT(SUM(Sheet1[Jan]),"General Number")))

enter image description here

By display the measure on the table, you will have the result:

enter image description here

0
votes

To display different data types in a table, you can create a measure with the following DAX for the Feb column, you can do the same for other columns too.

    Measure = 
    VAR VAL = SUMMARIZE('Table','Table'[Feb])
    RETURN SWITCH( SELECTEDVALUE('Table'[Title]), 
                 "Users", FORMAT(VAL, "0"),
                 "BV", FORMAT(VAL, "$0"),
                 "Charge", FORMAT(VAL, "0.00"),
                 "New Users", FORMAT(VAL , "0")
                )

Output