Forum Discussion

gazzo1967's avatar
gazzo1967
Helper III
3 years ago
Solved

Copy text from Delimiter Text field to specific Columns

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


Need Help
  • HotChilli's avatar
    HotChilli
    3 years ago

    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

3 Replies

  • HotChilli's avatar
    HotChilli
    Community Champion
    3 years ago

    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

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    3 years ago

    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"