Merging two columns by criteria

[Anonymous]

 Hi everyBI'ers

Q: How do I merge two columns by the criteria that the values from column1 and colum2 is evaluated by it's number of digits?

Example: I would like to merge the following two columns to a column3 so only values of 6-digit number from column1 and column2 goes into column3? 

Column1	Column2	Column3
5910	841437
5910	798953
5910	841759
5910	840494
7400	840216
798953	8400
798799	8999
841759	744
840768	112
798718	1888
796920	1880
841498	5910
841632	5910
841188	7400

[MalS]

In the Query Editor you could click Add Column > Custom Column and enter this formula:

if Text.Length([Column1]) = 6 then [Column1] else if Text.Length([Column2]) = 6 then [Column2] else null

[Anonymous]

@MalS Thanks for your answer, but then columns has to be converted to text, isn't it possible to do the same operation with number-columns?

[v-shex-msft · Accepted solution]

HI @Anonymous,

You can try to use below formula:

if [Column1] >= 100000 then [Column1] else if [Column2] >= 100000  then [Column2] else null

In addition, you can also use Number.From function to convert result from mals's formula.

Regards,

Xiaoxin Sheng

[Greg_Deckler]

Like this?

Column3 = IF(LEN([Column1])=6,[Column1],[Column2])

[Anonymous]

@Greg_Deckler Thanks for your answer. Would you do this as a new add'ed column in the query editor? I get the error that "IF"-function is not known here?