Change column data type depending the value

[RWRW]

Good Morning,

I have this table. As you can see in Result column sometimes I have text sometimes but also I have numbers, either Whole Numbers or Decimal Numbers. I'm wondering How can I change the data type of the column depending the value.

In fact, I'm trying to change in the Main Menu

But I'm getting this 2 messages

 

I tried many different things but it doesn't work.

This Result column I'm using sometimes for trending chart so I need to take the numbers as Data type Number or Decimal.

I hope someone can help me.

Thanks a lot in advance

[RWRW]

Sorry this screenshot

[ryan_mayu]

@RWRW 

we can only set up one data type for one column. if the column contains both text and number, the data type can only be TEXT.

you can try to create a new column in PQ to retrieve numbers.

=try Number.FromText([Result]) otherwise null

[tharunkumarRTK]

@RWRW 

I would suggest you to split your result column into two different columns based on their type and proceed accordingly. You can split it by following this syntax:

= Table.AddColumn(#"Added Custom", "Number", each if Value.Type([Result]) = Number.Type then [Result] else null, type number)

If the post helps please give a thumbs up

If it solves your issue, please accept it as the solution to help the other members find it more quickly.

Tharun

[RWRW]

Hi,

I tried and all values are null and I don't know if I'm doing something wrong

Thanks

[RWRW]

I got it for Numbers

What about for text?

Thanks a lot

[ryan_mayu]

pls try

=if [Numeirc Result] is null then [result] else null

[tharunkumarRTK]

= Table.AddColumn(#"Changed Type", "Text", each if Value.Type([Result]) = Text.Type then [Result] else null,type text)

If the post helps please give a thumbs up

If it solves your issue, please accept it as the solution to help the other members find it more quickly.

Tharun

[RWRW]

Hi again,

Something is incorrect? Copy and paste...

Thanks

[tharunkumarRTK]

Your column name is 'Result', with  upper case R

[RWRW]

Yes yes I realized, I already changed and still not working

[tharunkumarRTK]

@RWRW 

I understand the problem, your result column data type should be 'Any' for the formula to work, if it is text then you can follow this syntax 

= Table.AddColumn(#"Changed Type1", "Custom", each try Number.From([Result]) otherwise null)

If the post helps please give a thumbs up

If it solves your issue, please accept it as the solution to help the other members find it more quickly.

Tharun

[RWRW]

Hi Again,

For Number is ok

But For text not, It's giving all the values, It should be null for numeric values and it's not.

I mean Text Result COlumn should be

null

null

Approved

Approved

Failed

Thanks a lot again, hope you can help me

[tharunkumarRTK]

@RWRW 

Please share your file, I will add those two columns and can give it to you.

[RWRW]

Hi,

How Can I share the file?

Anyway, if you can tell me the function I will add no worries.

For Numeric Column the formula is already ok. I only need when is text.

Thanks

[tharunkumarRTK]

@RWRW 

Table.AddColumn(#"Changed Type", "Text", each if Value.Type([Result]) <> Number.Type then [Result] else null,type text)

[RWRW]

Hi again,

Already changed and it doesn't work

The first two in Text Result Column should be null and it's not

Thanks

[gmsamborn · Accepted solution]

Hi @RWRW 

I noticed the changes @tharunkumarRTK  and @ryan_mayu  suggested but noticed that for both lines you are using "Changed Type" as the first argument.

The first argument for the second column added should refer to your previous step.  If you refer to an earlier step, the steps in between will be missed.

Change the first argument of your second column (your text column) to refer to the step name for the first column (your numeric column).

In my example when creating the numeric column, my step name ended up as "Custom1" and the code is as follows:

= Table.AddColumn( #"Changed Type", "Numeric Result", each try Number.From([Result]) otherwise null )

For my text column, i used the following:

= Table.AddColumn( Custom1, "Text Result", each if [Numeric Result] = null then [Result] else null )

Notiice the change in the first arguments?

I hope this helps.

[RWRW]

Hi,

You are right, after refering Numeric column it works perfectly.

I would like to send thanks all of you to spend time with my issue.

I'm really appreciated

Thanks again

[tharunkumarRTK]

not sure, why these formulas are not working for you. I am able to run them
please upload it to any storage accounts like google dirve and share the link after granting access.