Cant load decimal numbers to power BI

[Munch00]

Hello All

Can anyone help with below issue pls?

I loaded data from xlsb file > Power BI auto converts to text > I then converted it to decimal places (3 d.p.) > Power Bi is still showing as whole number.

I checked my locale settings and no issue with it...

Before (text)

After

-tried to convert to 3 d.p. but still showing as whole numbers. 

Raw data

-even though raw data formating has no decimal places, when u click into it, excel will show that all these numbers have decimals but power BI just doesnt show it no matter what i try.  

-How do i rectify it? (EDIT: i can rectify once i change formatting in raw data so it display decimals. But raw data set is large, I would have to manually edit formatting for hundreds of sheets. Is there a way to for PowerBI to capture all the decimal places without having to change the formatting?)

[Anonymous]

Hi @Munch00 ，

How  do you change your type to decimal number? Is there?

you can also try to in "data" view to transform your data:

Best regards,

Community Support Team Selina zhu

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

[Munch00]

@Anonymous 

Sorry for the very late response. I tried that method too and it doesnt help.

So I manually changed the format of my raw data to decimals before loading in powerBI. And i make sure that data type is decimals in the query.

However, when i go to the data table, there is still rounding off issues. It is giving me basically whole numbers- all decimals are "000", which is different from data in the query. Do you have any idea why this is happening?

Thank you. 

[Anonymous]

Hi @Munch00 ,

I do the same test but my result is correct, I highly suspect that there is some data problem that is causing this, perhaps you could conveniently give me sample data where this error exists (don't include private data)?

Best regards,

Community Support Team Selina zhu

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

[Munch00 · Accepted solution]

Hi @Anonymous 

Appreciate your help! I managed to find where the problem lies.😊

So my issue is that data & visuals somehow still doesnt load the decimals even though i already changed it in the Query to decimal format and in "data" view

I realised that under TRANSFORM SAMPLE FILE format it is not in decimal. So i changed the format to decimal, refreshed it and now visuals are fine.  

[Anonymous]

Hi @Munch00 ,

That's great news! I'm glad you solved the problem~😊
Please mark your answer as SOLUTION so that other members with similar problems can get advice~

Best regards,

Community Support Team Selina zhu

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

[davehus]

Hi @Munch00 , As part of the import, is power query automatically applying a changed type step? Hase your data got the decimal places prior to this step. At the promoted headers stage?

[Munch00]

@davehus I notice the step and deleted away, but seems that there is no difference. When the data is imported over, there is no decimals at all.

My workaround is just changing the default formatting in my raw data. Instead of rounding off to whole numbers, make it display more d.p. before importing over...but that just involves more manual work 😞  So trying to see if there is a way to avoid this method

Before-Raw data

After-Raw data