Forum Discussion

RWRW's avatar
RWRW
Helper III
2 years ago
Solved

Change column data type depending the value

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 t...
Need Help
  • gmsamborn's avatar
    gmsamborn
    2 years ago

    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.

tharunkumarRTK's avatar
tharunkumarRTK
Super User
2 years ago

RWRW 

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

RWRW's avatar
RWRW
Helper III
2 years ago

Hi again,

 

Already changed and it doesn't work

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

 

Thanks

  • tharunkumarRTK's avatar
    tharunkumarRTK
    Super User
    2 years ago

    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.

  • gmsamborn's avatar
    gmsamborn
    Super User
    2 years ago

    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's avatar
    RWRW
    Helper III
    2 years ago

    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