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

types powerbi decimal powerquery complex-data-types
Denis Leu picture Denis Leu · Dec 10, 2019 · Viewed 7.8k times · Source

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

Dreekun picture Dreekun · Dec 10, 2019

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.

Related questions

How to determine a Python variable's type?

How do I see the type of a variable whether it is unsigned 32 bit, signed 16 bit, etc.? How do I view it?

Read More
Change column type in pandas

I want to convert a table, represented as a list of lists, into a Pandas DataFrame. As an extremely simplified example: a = [['a', '1.2', '4.2'], ['b', '70', '0.03'], ['x', '5', '0']] df = pd.DataFrame(…

Read More
Determine the type of an object?

Is there a simple way to determine if a variable is a list, dictionary, or something else? I am getting an object back that may be either type and I need to be able to tell the difference.

Read More