Forum Discussion
Sort concatenated look up values
Hi,
I have concatenated two lookup fields from different tables into a master table, but am getting different combinations of the same values. I cant merge or append these tables unfortunately so trying to resolve outside of query editor.
I have been trying to sort them to resolve this issue but cant get the result i need unfortunately, row 1 & 2 specifcally need to be the same per the required result field below.
Example of data
| Column1 | Column2 | Concatenated field | Required result |
| AAA | BBB | AAA,BBB | AAA,BBB |
| BBB | AAA | BBB,AAA | AAA,BBB |
| CCC | CCC | CCC,CCC | CCC,CCC |
| DDD | DDD | DDD,DDD | DDD,DDD |
| EEE | DDD | EEE,DDD | EEE,DDD |
Any assistance would be greatly appreciated.
Thanks,
Hi Bren_L1 ,
Here are the steps you can follow：
1. Create calculated table.
Table 2 = var _column1= SELECTCOLUMNS('Table',"Column1",[Column1]) var _column2= SELECTCOLUMNS('Table',"Column2",[Column2]) var _table= DISTINCT( UNION( _column1,_column2)) return ADDCOLUMNS( _table,"rank",RANKX(_table,[Column1],,ASC))2. Create calculated column.
Rank_index = var _rank1= MAXX( FILTER( 'Table 2','Table 2'[Column1]=EARLIER('Table'[Column1])),[rank]) var _rank2= MAXX( FILTER( 'Table 2','Table 2'[Column1]=EARLIER('Table'[Column2])),[rank]) return IF( _rank1<=_rank2, [Column1]&","&[Column2], [Column2]&","&[Column1])3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
3 Replies
Bren_L1 Try:
Column = VAR __Values = { [Column1], [Column2] } VAR __Result = CONCANTENATEX( __Values, [Value], ",", [Value]) RETURN __ResultHi Bren_L1 ,
Here are the steps you can follow：
1. Create calculated table.
Table 2 = var _column1= SELECTCOLUMNS('Table',"Column1",[Column1]) var _column2= SELECTCOLUMNS('Table',"Column2",[Column2]) var _table= DISTINCT( UNION( _column1,_column2)) return ADDCOLUMNS( _table,"rank",RANKX(_table,[Column1],,ASC))2. Create calculated column.
Rank_index = var _rank1= MAXX( FILTER( 'Table 2','Table 2'[Column1]=EARLIER('Table'[Column1])),[rank]) var _rank2= MAXX( FILTER( 'Table 2','Table 2'[Column1]=EARLIER('Table'[Column2])),[rank]) return IF( _rank1<=_rank2, [Column1]&","&[Column2], [Column2]&","&[Column1])3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Thanks this worked as required, really appreciate the assistance 🙂
