Sort concatenated look up values

[Bren_L1]

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, 

[Anonymous · Accepted solution]

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

[Bren_L1]

Thanks this worked as required, really appreciate the assistance 🙂

[Greg_Deckler]

@Bren_L1 Try:

Column = 
  VAR __Values = { [Column1], [Column2] }
  VAR __Result = CONCANTENATEX( __Values, [Value], ",", [Value])
RETURN
  __Result