Power Query - Converting whole number to text in a CUSTOM COLUMN

Question

I need to convert 3 whole number columns to text in a formula when adding a new column inside power query. I know how to do this in dax using FORMAT function but I can't make it work inside power query.

3 columns are - click to veiw

Then below is my CUSTOM COLUMN:

    = Table.AddColumn(RefNo.3, "Refernce Number", each 
if Text.Length([RefNo.3]) > 1 and Text.Length([RefNo.3]) < 11  then [RefNo.3]
else if Text.Length([RefNo.2]) > 1 and Text.Length([RefNo.2]) < 11 then [RefNo.2]
else if Text.Length([RefNo.1]) > 1 and Text.Length([RefNo.1]) < 11 then [RefNo.1]
else null)

However, at the moment I'm getting this error: Expression.Error: We cannot convert a value of type Table to type Number. Details: Value=[Table] Type=[Type]

So I know I need to convert the whole number columns to text first inside the formula. Also, I had to intentionally convert those 3 columns from text to whole number previously to get rid of redundant values (so that's not an option for me to revert that). thanks in advance guys.

Ron Rosenfeld Ron Rosenfeld · Accepted Answer · 2021-05-31T01:26:05

There are any number of ways to solve this, depending on your real data.

Just set the columns to Type.Text before executing your AddColumn function.
- If you do this, you would also have to check for null as they will cause the script, as you've written it, to fail
- Or you could precede your testing with another line to replace the nulls with an empty string (""): Table.ReplaceValue(table_name,null,"",Replacer.ReplaceValue,{"RefNo", "RefNo2", "RefNo3"}),
If they are all positive integers, compare the values rather than the string lengths: eg >=0 and <10000000000
Construct a numeric array, and return the last value that passes the filter

 = Table.AddColumn(your_table_name, "Reference Number", 
      each List.Accumulate(List.Reverse(List.RemoveNulls({[RefNo],[RefNo2],[RefNo3]})),
        null,(state,current)=> if state = null then 
            let 
              x =  Text.Length(Text.From(current))
            in 
              if x > 1 and x < 11 then current else state 
        else state))

Power Query - Converting whole number to text in a CUSTOM COLUMN

1 Answers