Forum Discussion
IF then else function
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.
5 Replies
Please make sure that Data Type of the column is Text
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])
