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.

Answer 1

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))