Issues with conversion from text to decimal

[fedosklyar]

Hello to everyone.
I faced with issue of interpreting of column type by PowerBI.

Loading data from data warehouse, the type of one of columns from fact table was determined as text, despite, inside DBMS(postresql, in my situation) it is defined as numeric (4,2)



Also, I decided to check the data using python, and, after loading dataframe and checking info about it, I was informed that the type is correct, and has float64 type, as I expected and how need be

But, within the PowerBI, that field interpreted as Text and after trying different methods of conversion to decimal (using powerBI tools and editing the query), I got the error messages

Probably, anybody have previously faced with such issue, or, at least have an idea how it can be fixed?

Also, a sample of data from fact table

1 3418 2 16 0.01 55 "tbd" 11007
2 1408 1 7 0.34 79 "8.6" 12888
3 2108 2 11 0.00 53 "6.6" 8719
4 3406 2 11 0.28 80 "8.7" 8928
5 506 1 4 0.15 50 "5.0" 9943
6 1908 2 17 0.01 77 "7.7" 12384
7 4259 2 6 0.08 77 "7.8" 8914
8 1337 1 16 0.10 69 "6.8" 9326
9 533 1 6 0.04 45 "4.6" 11899
10 4527 1 11 0.05 52 "7.2" 11891

[PhilipTreacy]

Hi @fedosklyar 

When you click on Keep Errors the table that is displayed is just rows with errors - click beside an error and towards the bottom of the screen you'll see the error message describing why that has has occurred.  You can then take action to fix the errors.

Phil

[fedosklyar]

Hi, Phil

Thanks for that tip. After fullfilling actions, mentioned by you, I have seen the error message, and the appearence of that is next

The ways to solve that problem is still ambiguous for me... Probably, anybody have an idea, what is the possible methods to fix that issue?

P.S. I tried different ways to manually specify the type, both type number and Decimal.type, and always the same problem occurs

[PhilipTreacy]

Hi @fedosklyar 

To manually specify a decimal data type just use type number 

If it still gives you errors there must be something in the column that Power Query considers as text.  Check the column for such values.

When you attempt the conversion from text to decimal, and you get am error, the celsl that cause the error wil contain the word Error 

You can click on the top left corner of the table

and from the menu choose Keep Errors

Which will then show you the rows causing the problems.  Check what the values in those rows are.

Regards

Phil

[fedosklyar]

Hi, Phil

Really grateful for your answer.

I inspected my column as you recommended, and, to my surprise, it displays all cells as invalid

And I have no idea, what caused such behavior and what the are the ways fo solve that problems

Sincerely,
Fedir

[Anonymous]

Hi @fedosklyar 

Perhaps you have missed @PhilipTreacy 's first sentence "To manually specify a decimal data type just use type number ". See below image, either "type number" or Decimal.Type should work. "type Decimal.Type" is incorrect. 

Best Regards,
Jing