Does not change column type to decimals

[Syndicate_Admin]

I have a data source that is a CSV that looks like this:

When I enter it in power BI the WorkHours column looks like this:

The column has its type as text, as soon as I change the type to decimals it is observed as follows:

It had a data of 8.5 and when making the change of type it transforms it into 85. I checked the formats of windows, excel and power bi and the period is as a decimal symbol. I tried through a function and it still doesn't show up as a decimal.

Some idea of how I can solve this problem, editing the data source is not an option.

[Syndicate_Admin]

Hello.

I went through the process as directed, and it worked. The strange thing is that I substitute "." for the ",", and then when I change the type it places the decimal with the period. Crazy.

Thank you.

[CalebR]

Yes its very weird, I dont like that we have to do it this way. I guess because of the language setting, when its a text field as "8.5"  and you try to switch to decimal, it doesnt even recognize the "." as a valid decimal place due to whatever language you have set. Thats why you have to replace it with a "," first, now it knows that is where a decimal should go when converting. 

I will say that with my settings I do not have this issue at all, I would recommend looking into language settings in powerbi and see if you can switch yours to english. That would allow you to change it to a decimal in the future without having to jump through this extra hoop.

If you found my answer satisfactory please mark it as the solution, have a great day!😁

[Syndicate_Admin]

@Syndicate_Admin Access provided

[CalebR]

Alright I believe I figured it out. I think this has something to do with your language settings. Some languages use "," instead of "." for the decimal place. Theres probably another way to do this but heres what I did:

1. Create a new column using the Substitute function like so:

NewWorkHours = SUBSTITUTE('Horas Reportadas'[WorkHours], ".", ",")
2. Now your value will be "8,5". 
3. Then you can format them as a decimal like normal in the Column Tools tab in the top left and it preforms as expected. 

Theres probably another way of doing this through power query editor but I couldnt do it there since I dont have all the data connections set up like you. I believe if you just replace all the  "."s with ","s then change it to a decimal data type it would work for you in there.

Hope this helped!

[Syndicate_Admin]

@Syndicate_Admin I made the change in the datasheet and it continues to transform it in its entirety. I attach the link to the pbix.

https://drive.google.com/file/d/1mf1LUbzoIRhVcaldBQqPpNPo5wpifxdK/view?usp=share_link

[CalebR]

I was denied access on that link, sent a request in for access. 

[CalebR]

Have you tried formatting it to decimal not in the power query editor screen? Instead try it in the column tools in the data view. If that doesnt work you should upload your pbix file in google drive and share the link. 

[Syndicate_Admin]

@Syndicate_Admin Nice day. I generated the change from type to number and it continues to transform the decimals to integers.

[amitchandak]

@Syndicate_Admin , Try this, for the first change type, overwrite the data type from text to number, and try