Forum Discussion

MelonCocktail's avatar
MelonCocktail
Frequent Visitor
4 years ago
Solved

Split column by delimiter at multiple positions

Hi everyone, I would want to split a column into rows for every delimiter that is a multiple of 4 of said delimiter. Here is an example of the data I am working with:  The delimiter for which I nee...
  • AlexisOlson's avatar
    4 years ago

    Try splitting on ";" into rows rather than columns.

    Now add a couple of index columns starting from zero then mod one column by 4 and integer divide the other column by 4 to get this:

     

    Now you can pivot on the [i] column using [Original] as the values column. The result should look like this:

    From here, you can rename, remove, or combine columns as desired.

     

    Full sample query you can paste into the Advanced Editor of a new blank query:

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZKxTsMwEIZf5WSJLUQmbaHIUyMELF1gYAgZ4uTUWnXvkO2oYuV5eCqeBDuhQrRSBSy2JZ+/7/6Tq0pciEyUtqF2jdChh3smtBa9uszlGZxD6Rrv0Zl0C/a5l7K4WmjdvI7lD9z6DEq0K9Nv1c4EbdA9rU1A9fH2PsnnUtRZJYpoOXgVyT2huv6HJu0It+xCWqlFLB3vKBmnuZTqyGSZOoyJZn9W6eEp6GQczmo6BJrEQEs2hCH28MWfH/Jv+hem8LNv0xA8Bse0gju23cjcN/5NHGbzK+CpQSyZNmNAH6XcBzXLi9NIn8rih2g3e4qo608=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Ref = _t, Original = _t]),
        #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Original", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Original"),
        #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Ref", Int64.Type}, {"Original", type text}}),
        #"Added Index" = Table.AddIndexColumn(#"Changed Type", "i", 0, 1, Int64.Type),
        #"Added Index1" = Table.AddIndexColumn(#"Added Index", "j", 0, 1, Int64.Type),
        #"Calculated Modulo" = Table.TransformColumns(#"Added Index1", {{"i", each Number.Mod(_, 4), type number}}),
        #"Integer-Divided Column" = Table.TransformColumns(#"Calculated Modulo", {{"j", each Number.IntegerDivide(_, 4), Int64.Type}}),
        #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Integer-Divided Column", {{"i", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Integer-Divided Column", {{"i", type text}}, "en-US")[i]), "i", "Original")
    in
        #"Pivoted Column"

     

  • wdx223_Daniel's avatar
    4 years ago

    NewStep=#table(Table.ColumnNames(PreviousStepName),List.TransformMany(Table.ToRows(PreviousStepName),each List.Split(Text.Split(_{1},";"),4),(x,y)=>{x{0},Text.Combine(y,";")}))