Powerquery to split delimited columns into unique rows

Question

I have a spreadsheet that looks like this:

I would like it to look like this at the end:

I know that I can use PowerQuery to delimit and expand into rows, but that would only work on the first column. The second time doing that would introduce lots of duplicates.

Any help?

Answer 1

I'd just do them separately and combine

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Removed Columns" = Table.RemoveColumns(Source,{"People"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Emails] <> null)),
SCBD = Table.ExpandListColumn(Table.TransformColumns(#"Filtered Rows", {{"Emails", Splitter.SplitTextByDelimiter(";", QuoteStyle.None)}}), "Emails"),
#"Removed Columns1" = Table.RemoveColumns(Source,{"Emails"}),
#"Filtered Rows1" = Table.SelectRows(#"Removed Columns1", each ([People] <> null)),
SCBD1 = Table.ExpandListColumn(Table.TransformColumns(#"Filtered Rows1", {{"People", Splitter.SplitTextByDelimiter(";", QuoteStyle.None)}}), "People"),
Combined = SCBD  & SCBD1,
#"Sorted Rows" = Table.Sort(Combined,{{"Name", Order.Ascending}, {"Emails", Order.Descending}})
in #"Sorted Rows"

Answer 2

You lose original order with this approach (you could attempt to re-sort at the end if you wanted).

If at some point you get more columns that need to be similarly split, you can just add to the columnsToSplit list.

let
    dataFromSheet = Table.FromColumns({{"Cereal Killers", "Acme Products", "Arkham Asylum"}, {"123 Sugar Way", "345 Whoville Place", "Gotham City"}, {"[email protected]; [email protected]; [email protected]", null, "[email protected]; [email protected]"}, {"Tony Tiger; Toucan Sam; Lucky Leprauchan", "W. Coyote; R. Runner; R. Rabbit", null}}, type table [Name=text, Address=text, Emails=nullable text, People=nullable text]),
    columnsToSplit = {"Emails","People"},
    loopOverColumnsToSplit = List.Accumulate(columnsToSplit, #table({}, {}), (tableState, currentColumn) =>
        let
            reduceColumns = Table.SelectColumns(dataFromSheet, {"Name", "Address"} & {currentColumn}),
            dropNullRows = Table.SelectRows(reduceColumns, each Record.Field(_, currentColumn) <> null),
            splitIntoList = Table.TransformColumns(dropNullRows, {{currentColumn, each Text.Split(_, "; "), type list}}),
            expandList = Table.ExpandListColumn(splitIntoList, currentColumn),
            appendToAccumulatedTable = tableState & expandList
        in appendToAccumulatedTable
    )
in
    loopOverColumnsToSplit

---

If preserving order is important, then maybe try approach below (which might take a bit longer as it has a few extra steps).

let
    dataFromSheet = Table.FromColumns({{"Cereal Killers", "Acme Products", "Arkham Asylum"}, {"123 Sugar Way", "345 Whoville Place", "Gotham City"}, {"[email protected]; [email protected]; [email protected]", null, "[email protected]; [email protected]"}, {"Tony Tiger; Toucan Sam; Lucky Leprauchan", "W. Coyote; R. Runner; R. Rabbit", null}}, type table [Name=text, Address=text, Emails=nullable text, People=nullable text]),
    columnsToSplit = {"Emails","People"},
    numberOfColumnsToSplit = List.Count(columnsToSplit),
    loopOverColumnsToSplit = List.Accumulate(List.Positions(columnsToSplit), #table({}, {}), (tableState, currentIndex) =>
        let
        currentColumn = columnsToSplit{currentIndex},
        reduceColumns = Table.SelectColumns(dataFromSheet, {"Name", "Address"} & {currentColumn}),
        dropNullRows = Table.SelectRows(reduceColumns, each Record.Field(_, currentColumn) <> null),
        addIndex = Table.AddIndexColumn(dropNullRows, "toSortBy", currentIndex, numberOfColumnsToSplit),
        splitIntoList = Table.TransformColumns(addIndex, {{currentColumn, each Text.Split(_, "; "), type list}}),
        expandList = Table.ExpandListColumn(splitIntoList, currentColumn),
        appendToAccumulatedTable = tableState & expandList
        in appendToAccumulatedTable
    ),
    sorted = Table.Sort(loopOverColumnsToSplit, {"toSortBy", Order.Ascending}),
    dropHelperColumn = Table.RemoveColumns(sorted, {"toSortBy"})
in
    dropHelperColumn

---

Just to clarify, if you have a row where the values in Emails and People columns are both null, then that row will not be present in the output table.