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

Question

I have Excel data which looks something like 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 am trying to use Power Query to condense it to this:

Sources   Targets   Routes

Lemons    Chair     A,D,F
Oranges   Chair     B,C,F,G
Oranges   Door      A,B,C,G

That is, for each Source/Target pair, I need to

Split apart the comma-delimited Routes,
Eliminate the duplicate routes
Combine the routes back into a comma-delimited list
Display it in a single record for the Source/Target pair.

There is a max of 3 routes in Routes source data. I'm pretty sure I need to split the Routes column into 3 columns, then use Group. But there I get stuck.

Suggestions?

MarcelBeug MarcelBeug · Accepted Answer · 2017-04-15T01:36:02

The code below implements the steps you outlined plus a sort on Routes. It doesn't use split columns, but Text.Split.

The SplittedRoutes step was created using some text transform function on the Transform tab, then adjusted to use Text.Split.

Likewise, the GroupedRows step was created with Group By on the Transform tab, using operation All Rows, then adjusted to the 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

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

1 Answers