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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Convert whole number to decimal number in Power BI

Hi everybody!

 

I am completely new to Power BI and this is officially my first day of using it 🙂

 

I am currently doing an onboarding tutorial for the programme, in which I have been given some fictional sales data. However, in the tutorial, when they load their excel file into power BI, the unit price, sales amount and total product cost is given as a decimal. For me on the other hand, these were for some reason loaded as whole numbers instead.

 

This means that my current data looks like this:

 

Jyggalag77_0-1660122117581.png

 

As you can see, I have converted the type from "Whole number" to "Decimal number". However, the numbers have not changed, and they effectively need to have a comma after the first two numbers (so the values on the first line will be 20,99 for unit price, 83,98 for sales amount and 52,3452 for total product cost).

 

Does anybody know of a way in which I can change this, so my data is converted? I would prefer to avoid entering the excel CSV file if possible, but if I have to, I am open to that solution as well 🙂

 

Thank you so much all!

 

Kind regards,

Jyggalag

 

 

1 ACCEPTED SOLUTION
mattkocak
Kudo Kingpin
Kudo Kingpin

The easiest way would probably be to click on the column that you are trying to update from the side menu, go to the Column tools tab in the top ribbon, and change the Data type to decimal there.

mattkocak_0-1660122576740.png

 

Additionally, you could update the data type in the Power Query Editor (Transform data selection), as oulined in the following blog post:

https://sqlskull.com/2022/01/05/change-column-data-type-in-power-bi/

 

If I answered your question, it would be helpful to me to mark my post as a solution and give me a thumbs up.

 

Best,

Matt

View solution in original post

12 REPLIES 12
v-yanjiang-msft
Community Support
Community Support

Hi @Anonymous ,

According to your snapshot, the Profit column already has a delimiter and a currency identifier, isn't this your expected result?

vkalyjmsft_0-1660642946780.png

If you only want to keep the delimiter and remove the currency identifier in a number data type column, it's not available now. Decimal number only means decimals can be reserved, for example 1.23, if it's a whole number, it will be 1 instead of 1.23. Only number of the Currency format has delimiter.

vkalyjmsft_3-1660643813473.png

Best Regards,
Community Support Team _ kalyj

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

 

 

 

Anonymous
Not applicable

Hi!

 

I think I found out what the issue was.

 

In Europe, we often use a comma to separate numbers, so 1,25 + 1,25 = 2,50 for example

 

whereas 10.000 is ten thousand in value, not ten point 000

 

if that makes sense?

 

I think the issue is that my data set is American, but my Power BI has been set to European, so it recognizes the values in a different way

 

I had this issue in Tableau as well, where it is quite easy to fix luckily

 

is there a similar fix in POWER BI available?

Hi @Anonymous ,

Do you expect the result to have a comma when the number is displayed? If that's the case, I haven't found any other way than to change it to currency format.

 

Best Regards,
Community Support Team _ kalyj

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

mattkocak
Kudo Kingpin
Kudo Kingpin

The easiest way would probably be to click on the column that you are trying to update from the side menu, go to the Column tools tab in the top ribbon, and change the Data type to decimal there.

mattkocak_0-1660122576740.png

 

Additionally, you could update the data type in the Power Query Editor (Transform data selection), as oulined in the following blog post:

https://sqlskull.com/2022/01/05/change-column-data-type-in-power-bi/

 

If I answered your question, it would be helpful to me to mark my post as a solution and give me a thumbs up.

 

Best,

Matt

Anonymous
Not applicable

Dear Matt,

 

Thank you so much for your quick response!

 

Apologies if my initial message was confusing, but as I mentioned, I did already change the type to a decimal number.

 

However, the number itself still remains a full number following this, and I would like to convert it so unit price says 20,99 instead of 2099 (as an example).

 

Is this possible? Is there some way of separating numbers so the first two numbers are whole numbers, and the remaining will be added after a comma? So if total product cost has a value of 18663, it becomes 18,663 instead.

 

Thank you!

Ahh I see. Sorry about the confusion.

 

I'm not sure if this is the best way of doing it, but you could create a custom column for each of the columns that you had mentioned. You can use code similar to that below, which basically creates a string that's formatted the way that you would like.

 

FormattedUnitPrice = CONCATENATE(LEFT('Table'[UnitPrice], 2), CONCATENATE(".", RIGHT('Table'[UnitPrice], LEN('Table'[UnitPrice]) - 2)))

 

mattkocak_0-1660125229588.png

 

These new columns will initally be of type text, but you can update the type to a decimal number and then format the decimal as you see fit. This did the trick for me. Let me know if it works for you!

 

Best,

Matt

Anonymous
Not applicable

Hi again Matt,

 

Thank you so much! That seems like a wonderful solution 🙂

 

I am still extremely new at Power BI, but I made a column and inserted the code, however it does not seem to work and returns an #ERROR. Did I do something wrong?

 

Jyggalag77_0-1660128599083.png

 

 

Kind regards,

Jyggalag

The 'Table'[UnitPrice] pieces of the code, which are underlined in red in your error message, should be updated to reference whatever your table/column you are evaluating.

 

So if your table's name is Sales and you would like to reference the UnitPrice column, then you would use 'Sales'[UnitPrice]. And you will need to do similar replacements with the other columns that you will be creating.

 

I don't know the name of your table, which is why I use a generic 'Table' term in the code I provided. This is something the you will have to update based on the specific table name. Does this clarify the error?

Anonymous
Not applicable

Hi Matt,

 

Thank you so much. This makes much more sense!

 

I do have the embarrassing admission however, that I am not familiar with what a "table" is? I am aware that my column's name is UnitPrice, but how do I find the table name, if I may ask?

 

Would be greatly appreciated - thank you! 🙂

A table is the structure that contains all of your columns. You can find it in the far right menu in Power BI Desktop. In the screenshot below, it's Product Details.

 

mattkocak_0-1660137350745.png

 

There are a lot of other places where you can find it as well. In your error message screenshot above, it would be the heading in the far left menu I believe, although it's cut out of the screenshot.

 

Would you mind upvoting the posts that are helpful and accepting a post as a solution if it solves what you are trying to accomplish?

@Anonymous, did this end up fulfilling your request?

Anonymous
Not applicable

Dear Matt,

 

Happy to upvote, especially given all the effort you are putting into helping me, really appreciate it! Also apologies for the very late response.

 

I would like to achieve this result:

 

Jyggalag77_0-1660302505297.png

 

This graph has Profit on the X-axis, Sales Type on Y-axis and Sales Type as Legend

 

I have used your formula now (also thanks for explaining to me what a table is!) and am getting the following:

 

Jyggalag77_1-1660302586434.png

 

However, I would also like to convert the values from "Text" to "Decimal Number" so I may get the full number, while also converting the number values to USD currency. However, when I convert it to decimal numbers I get this error:

 

Jyggalag77_2-1660302640187.png

 

Another issue is that when I insert it into my graph for Sales Type, the "FormattedProfit" is shown as a "Count":

 

Jyggalag77_3-1660302697341.png

 

 

 

I am very new to this, but I assume that rather than provide me with the overall profits in total, or an average of the profits (the two things I am interesting in), it counts the number of values in the column for FormattedProfit and tells me there are 13,44K values for Internet Sales and 4,47K for Reseller Sales, if I am correct?

 

Is there any solution to this? Sorry for digging so deep into the hole... I would truly appreciate your help if possible however 🙂

 

Kind regards,

Jyggalag

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.