Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

table replacer & Change type Error (Chicken & Egg?)

i am copying a column and then replacing values to numbers (e.g.  s -> 4, M -> 3, L -> 2, etc etc). Relevant code is:

 

 

#"Dup Risk Impact" = Table.DuplicateColumn(#"Dup Implementation Impact","Risk Impact", "Risk Impact Value"),
#"Dup Size" = Table.DuplicateColumn(#"Dup Risk Impact","Size", "Size Value"),
...
#"RI_Null" = Table.ReplaceValue(#"II_High",null,".01",Replacer.ReplaceValue,{"Risk Impact Value"}),
#"RI_None" = Table.ReplaceValue(#"RI_Null","None",".01",Replacer.ReplaceText,{"Risk Impact Value"}),
#"RI_Low" = Table.ReplaceValue(#"RI_None","Low","1",Replacer.ReplaceText,{"Risk Impact Value"}),
#"RI_Med" = Table.ReplaceValue(#"RI_Low","Med","2",Replacer.ReplaceText,{"Risk Impact Value"}),
#"RI_High" = Table.ReplaceValue(#"RI_Med","High","3",Replacer.ReplaceText,{"Risk Impact Value"}),
#"SIZE_Null" = Table.ReplaceValue(#"RI_High",null,".01",Replacer.ReplaceValue,{"Size Value"}),
#"SIZE_XS" = Table.ReplaceValue(#"SIZE_Null","XS","5",Replacer.ReplaceText,{"Size Value"}),
#"SIZE_S" = Table.ReplaceValue(#"SIZE_XS","S","4",Replacer.ReplaceText,{"Size Value"}),
#"SIZE_M" = Table.ReplaceValue(#"SIZE_S","M","3",Replacer.ReplaceText,{"Size Value"}),
#"SIZE_L" = Table.ReplaceValue(#"SIZE_M","L","2",Replacer.ReplaceText,{"Size Value"}),
#"SIZE_XL" = Table.ReplaceValue(#"SIZE_L","XL","1",Replacer.ReplaceText,{"Size Value"}),
#"Changed Type" = Table.TransformColumnTypes(SIZE_XL,{{"Client Impact Value", type number},  {"Risk Impact Value", type number}, {"Size Value", type number}})

 

 

The thing is, the changed type at the bottom seems to work fine for all values EXCEPT the 'SIZE_XL" line specifically to the XL -> 1 value . Specific error is:

 

DataFormat.Error: We couldn't convert to Number.
Details:
X2

 

The thing is, i don't have any values like 'X2' and when i go back to source, the offending value is XL which is what replace to 1

 

 

Any ideas? I'm really stuck

 

Need Help

3 Replies

  • VasTg's avatar
    VasTg
    Icon for Memorable Member rankMemorable Member
    6 years ago

    Anonymous 

     

    Its hard to see it in the code.

     

    Could you check on "Column Profile" and "Column quality" under view menu in query editor for that particular column? 

     

    Maybe you need to refresh the table to sync with database?

     

    If this helps mark it as a solution.

    Kudos are nice too

  • dax's avatar
    dax
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous , 

    I think you need to delete the "change type"  and change M code to show output to see whether there is any other result in column except number. If it has character not number in it, I think , you can't change the type to number

    in
#"SIZE_XL"

     

    Best Regards,
    Zoe Zhi

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • artemus's avatar
    artemus
    Icon for Microsoft Employee rankMicrosoft Employee
    6 years ago

    You need to replace XL before L. What is happening is that you are replacing L with 2, thus:

    L -> 2

    XL -> X2