Forum Discussion
Merging two columns by criteria
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 |
5 Replies
Like this?
Column3 = IF(LEN([Column1])=6,[Column1],[Column2])
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?
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
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?
