Combine common values in Power Query

[Iamnvt]

hi,

I have a Column:

A, B

B, C

A, C

D, E, F

D, E, G

how can I combine the 2 rows into one row in Power Query, if they have any value common: 

Expected result: 

A, B	A, B, C
B, C	A, B, C
A, C	A, B, C
D, E, F	D, E, F, G
D, E, G	D, E, F, G

Thanks

[v-juanli-msft]

Hi @Iamnvt

A bit more steps needed to implement, please see my pbix to catch the idea.

If you have any problem, please feel free to ask me.

 

Best Regards

Maggie

[Iamnvt]

hi @v-juanli-msft,

thank you for your solution. I checked it, and have few comments:

1. If I change the sequence of the values in column1, this is not working anymore. Eg:

A, B	A, B, C
D, E, F	D, E, F, G
A, C	A, B, C
B, C	A, B, C
D, E, G	D, E, F, G

2. This solution is little bit brute force. My problem is not limited in 3 values in one column,  but could extend to many values.

Is there any generalized approach to address this problem?