Results 1 to 4 of 4

Thread: Split delimited text across columns, matching values to column

  1. #1
    Neophyte gCordran's Avatar
    Join Date
    Sep 2018
    Posts
    2
    Articles
    0
    Excel Version
    Microsoft Office 365 ProPlus

    Question Split delimited text across columns, matching values to column



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have a column where each row contains a delimited list of values.
    I want to split the row text into columns, with one value always landing in the same column.
    I've tried using the Power Query > Split Column > By delimiter option, but this simply spreads the split row text across the new columns, first to last. It does not logically allocate as I want.

    In the picture below...
    Column A is the SOURCE data, 6 rows of delimited text. The delimiter is __#__ .
    Columns C to F are the standard result of the Power Query > Split action
    Columns H to M are an example of what I want to achieve in Power Query, with identical values always in the same column.
    Columns Q to T are an alternative example, where TRUE reflects the presence of that column/text value. Of course - there may be other ways of displaying this.

    As there are many columns with (different) delimited lists of values, I am looking for an intelligent solution which doesn't rely on manual definition of column names etc ... in an ideal world!

    Any and all help gratefully received.

    Click image for larger version. 

Name:	Screen Shot 2018-09-12 at 16.31.46.png 
Views:	77 
Size:	57.1 KB 
ID:	8448

  2. #2
    Acolyte horseyride's Avatar
    Join Date
    Nov 2017
    Posts
    27
    Articles
    0
    Excel Version
    Office 365 Pro Plus 1708.8431.

    Code

    Same idea, but here I'm splitting on the | character. Edit as needed. I'm starting with a one column table using row header of "SOURCE"
    1. Add Index
    2. Split on your delimiter/characters
    3. Duplicate that column
    4. Click the duplicate column and use Transform...Pivot.. Values column is the original column; use the advanced option Don't aggregate
    5. Remove index


    Code:
    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
        #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Added Index", {{"SOURCE", Splitter.SplitTextByDelimiter("|", QuoteStyle.None), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "SOURCE"),
        #"Duplicated Column" = Table.DuplicateColumn(#"Split Column by Delimiter", "SOURCE", "SOURCE - Copy"),
        #"Pivoted Column" = Table.Pivot(#"Duplicated Column", List.Distinct(#"Duplicated Column"[SOURCE]), "SOURCE", "SOURCE - Copy"),
        #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
    in
        #"Removed Columns"
    Last edited by horseyride; 2018-09-14 at 02:16 PM.

  3. #3
    Acolyte horseyride's Avatar
    Join Date
    Nov 2017
    Posts
    27
    Articles
    0
    Excel Version
    Office 365 Pro Plus 1708.8431.

    Code

    <deleted>

  4. #4
    Neophyte gCordran's Avatar
    Join Date
    Sep 2018
    Posts
    2
    Articles
    0
    Excel Version
    Microsoft Office 365 ProPlus
    BRILLIANT - worked an absolute treat.
    I have been able to adapt your code across my whole data set.

    Thank you!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •