merge tabseperated column values at once

[s-in-a-triangle]

Hi all,

My data looks like this:

column	id
|158|	9978
|116|623|	48724
|116|437|852|863|	48344
|116|437|852|	48337
|102|	4744
	4746
|102|	4747
|116|437|852|863|	4755
	4845
|102|	4846
	4856
|102|	4882

I have another table where the column values are mapped to string values:

column_id	value
102	a
116	b
158	c
437	d
623	e
852	f
863	g

What I am looking to achieve is:

column_values	issueid
c	9978
b, e	48724
b, d, f, g	48344
b,d, f	48337
a	4744
	4746
a	4747
b, d, f, g	4755
	4845
a	4846
	4856
a	4882

How can I achieve this as efficient as possible?

I can split column by delimiter and then merge one by one, but this will get out of hand quickly.

  #"Split Column by Delimiter" = Table.SplitColumn(#"previous_step", "column", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"column.1", "column.2", "column.3", "column.4", "column.5", "column.6", "column.7", "column.8"}),

Anybody better ideas?

[Zubair_Muhammad · Accepted solution]

Hi @s-in-a-triangle 

Assuming TableNames are Table1 and Table2, try this custom column

It gives me correct results with sample data
See attached file's Query Editor as well

=let Replacements=Record.FromList(Table2[value],List.Transform(Table2[column_id],each Number.ToText(_)))
in
Text.Combine(List.RemoveItems(List.Transform(Text.Split([column],"|"), each Record.FieldOrDefault(Replacements, _, _)),{null,""}),",")