Change the data type of a column that contains text and number in Power BI Query

Question

I am struggling with a simple data type change. I have a column that contains both text (string) and numbers. This column is computed from a simple formula. Both the text and the numbers are important, so I need to keep them both in the same column.

What I would like to do is just to reduce the number of decimals of the numbers. Unfortunately, I cannot do that, as the data type is "Any". If I change the data type to "Decimal number", the text values show an error.

Is there a way to somehow change the data type only of the number values and to not affect the text? Or just to reduce the number of decimals of the numbers?

Attached is a picture with the current situation: the number displayed is just the result of a subtraction.

Thank you for the help!

https://ibb.co/C7yFH5F

Answer 1

Okay. I managed in the end to do it. I just made other columns with the result as a text and then created another one using LEFT(Text;"X"). It displays the number in reasonable way and it is also correct. Many thanks for the help! It certainly pushed me towards the right way.

Answer 2

You can use the Extract function(it's located in the UI under Transform>Extract), and provide a range of characters. This will work if none of the text cells have more characters than the range provided. It generates this code:

= Table.TransformColumns(#"Previous Step", {{#"Your Column", each Text.Middle(_, 0, 12), type text}})

In this case I inserted from 0 to 12. It is not elegant but it might work for your specific case.

Answer 3

You need to create a new column with a formula that converts your number. To avoid errors on lines with text, you must use the error tracking function.

try otherwise

After the word "try" the function of converting the number from the column. After "otherwise" the column without conversion