excel - MS Power Query - eliminating duplicates in a set of delimited strings -

i have excel data looks this:

sources   targets   routes  lemons    chair     a,d lemons    chair     d,f oranges   chair     b,f,g oranges   chair     b,c oranges   door      a,g oranges   door      b,c 

i trying use power query condense this:

sources   targets   routes  lemons    chair     a,d,f oranges   chair     b,c,f,g oranges   door      a,b,c,g 

that is, each source/target pair, need

• split apart comma-delimited routes,
• eliminate duplicate routes
• combine routes comma-delimited list
• display in single record source/target pair.

there max of 3 routes in routes source data. i'm pretty sure need split routes column 3 columns, use group. there stuck.

suggestions?

the code below implements steps outlined plus sort on routes. doesn't use split columns, text.split.

the splittedroutes step created using text transform function on transform tab, adjusted use text.split.

likewise, groupedrows step created group on transform tab, using operation rows, adjusted code below.

let     source = exceldata,     splittedroutes = table.transformcolumns(source,{{"routes", each text.split(_,",")}}),     expandedroutes = table.expandlistcolumn(splittedroutes, "routes"),     removedduplicates = table.distinct(expandedroutes, {"sources", "targets", "routes"}),     groupedrows = table.group(removedduplicates, {"sources", "targets"}, {{"routes", each text.combine(list.sort(_[routes]),","), type text}}) in     groupedrows