Forum Discussion

gazzo1967's avatar
gazzo1967
Icon for Helper III rankHelper 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 wi...
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

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper 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"