Power Query: Cleaning Repeating Delimiters

[lneidorf]

Hi there.

I've been using PowerQuery to collapse a flat file into combined, multi-value cells with delimiters. The inconsistent nature of the raw data results in repeating delimiters / spaces, like so:

; ; Smith, Judy; Carson, Kevin; ; Levy, Kevin; Bloom, Judy; ; ; ; Nicholas; ; ;

I'd like to add some code to strip out the excess delimiters (and any leading delimiters) so that the result is:

Smith, Judy; Carson, Kevin; Levy, Kevin; Bloom, Judy; Nicholas

A bonus would be if I could sort the entries between delimiters, like so:

Bloom, Judy; Carson, Kevin; Levy, Kevin; Nicholas; Smith, Judy

Many thanks.

 

[smozgur]

Something like this:

let
    Source = "; ; Smith, Judy; Carson, Kevin; ; Levy, Kevin; Bloom, Judy; ; ; ; Nicholas; ; ;",
    SplitText = Text.Split(Source, ";"),
    RemoveEmpty = List.RemoveItems(SplitText, {" ", ""}),
    SortList = List.Sort(RemoveEmpty,Order.Ascending),
    JoinList = Text.Combine(SortList, ";")
in
    JoinList

Result:

You will probably need to do this in a table, so here is the function version that you can add a column by using it. I am just assuming since I don't know the exact data and current query structure. Let's say you have the table as Source instead of a simple text value, and Column1 is the column name that contains many of these values, then you can use the following to add a new column for each row in your table.

let
    fnCleanText = (txt as text) => 
        let
            SplitText = Text.Split(txt, ";"),
            RemoveEmpty = List.RemoveItems(SplitText, {" ", ""}),
            SortList = List.Sort(RemoveEmpty,Order.Ascending),
            JoinList = Text.Combine(SortList, ";")
        in
            JoinList,

    Source = #table(1, {{"; ; Smith, Judy; Carson, Kevin; ; Levy, Kevin; Bloom, Judy; ; ; ; Nicholas; ; ;"}}),
    Result = Table.AddColumn(Source, "Cleaned", each fnCleanText(_[Column1]) )
in
    Result