IF then else function

[Svante109]

My problem is - I've got two columns of serialnumbers. Most of them have 5 digits, but I have serialnumbers with 2,3, and 4 digits. In one column the serialnumber has leading zeros, while the other doesn't. This mess up my data when merging the columns to extract data from one query to another, because query doesn't link 00630 with 630 e.g.

I have tried adding the zeros to the non-zero-column, with this formula

= Table.AddColumn(#"Replaced Value3", "unit serial no", each if [unit seriel no_]=3 then "00"[unit seriel no_] else if [unit seriel no_]=4 then "0"[unit seriel no_] else if [unit seriel no_]=2 then "000" else [unit seriel no_])

It just returns the serialnumber it self. I want it to take 630 and return 00630.

[RolandsP]

Please make sure that Data Type of the column is Text

[parry2k]

@Svante109 seems like type of your uniserialno_ column is number, you need to check the length of the value in this column and then concatenate with number of zeros to make it proper length. 

In following example, replace [Key] column with your column number and add more if condition to check length 

(if Text.Length(Number.ToText([Key])) = 1 then "000" else
if Text.Length(Number.ToText([Key])) = 2 then "00" else
if Text.Length(Number.ToText([Key])) = 3 then "0" else "")
& Number.ToText([Key])

[Svante109]

Hi @parry2k 

I will try these tomorrow, thank you. I'm pretty sure that both columns are text though.

[Svante109]

Hi @parry2k 

When using the formula, it returns a column with just errors, unfortunately.

[parry2k]

@Svante109 it worked for me, share what error you are getting and waht formula you exactly used.

Also share what is the data type of your columns