Copy text from Delimiter Text field to specific Columns

[gazzo1967]

HI All
I have a field that has multiple text strings in it which is delimiter with a semi colon.
What i would like to do is copy each specfic text string to its own column.
I have a single cell with eg
cell 1 = FF1;FF2;FF3;FF4

but there could be a cell1 = FF2;FF4
I would like to write each to its own column.
 My columns would be:
FF1a, FF2a etc

My logic is IF cell1 contains "FF1" copy FF1 to column FF1a
the same would happen for each delimited text.

I know i can split using the delimter but i want each entry to go to it respective column.
Regards Gary

[Ashish_Mathur]

Hi,

This M code will get you from Image1 to Image2

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnMztHZzMwJiYyA2UYrVAYkZQdixAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Text = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Added Index", {{"Text", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Text"),
    #"Duplicated Column" = Table.DuplicateColumn(#"Split Column by Delimiter", "Text", "Text - Copy"),
    #"Pivoted Column" = Table.Pivot(#"Duplicated Column", List.Distinct(#"Duplicated Column"[Text]), "Text", "Text - Copy")
in
    #"Pivoted Column"

 

[HotChilli · Accepted solution]

If there are only to be 4 possible values (and therefore 4 columns), you could add 4 custom columns , each one using a slightly different version of : if Text.Contains([the1stColumn], "FF1") then "FF1" else null

[gazzo1967]

Do i use an EXTRACT for this