Split delimited text across columns, matching values to column

[gCordran]

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.

 

[horseyride]

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

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"

 

[horseyride]

Code

<deleted>

 

[gCordran]

BRILLIANT - worked an absolute treat.
I have been able to adapt your code across my whole data set.

Thank you!