Split column by delimiter at multiple positions

[MelonCocktail]

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 need to split the column is ";".

Ref	Original
1	Blanche des Honelles;6.0% - Brasserie de l'Abbaye des Rocs, Belgium;witbierWhite;€3.80
2	Abbaye des Rocs Brune;9.0% - Brasserie de l'Abbaye des Rocs, Belgium;Belge Forte FonceeBrown;€4.00;Abbaye des Rocs Blonde;6.5% - Brasserie de l'Abbaye des Rocs, Belgium;blonde belgeblond;4
3	Moinette Blonde;8.5% - Brasserie Dupont, Belgium;Belgian Strong Goldblond;€4.00;Moinette Brune;8.5% - Brasserie Dupont, Belgium;Belge Forte FonceeBrown;€4.00;Monk's Stout;5.2% - Brasserie Dupont, Belgium;stoutBlack;€4.00

For people of culture you may have recognized that these are Belgian beers! The problem is that the beers are grouped for their price and I would like to get a separate row for each beer. A new beer starts after the price is mentioned. If we take the second row for example, "Abbaye des Rocs Brune" is the first and "Abbaye des Rocs Blonde" is the second.

First row does not need any splitting because there are only 3 ";".

Second row needs to be split at the 4th ";" because there are 7 total. 

...

Ref	What I need
1	Blanche des Honelles;6.0% - Brasserie de l'Abbaye des Rocs, Belgium;witbierWhite;€3.80
2.a	Abbaye des Rocs Brune;9.0% - Brasserie de l'Abbaye des Rocs, Belgium;Belge Forte FonceeBrown;€4.00
2.b	Abbaye des Rocs Blonde;6.5% - Brasserie de l'Abbaye des Rocs, Belgium;blonde belgeblond;4
3.a	Moinette Blonde;8.5% - Brasserie Dupont, Belgium;Belgian Strong Goldblond;€4.00
3.b	Moinette Brune;8.5% - Brasserie Dupont, Belgium;Belge Forte FonceeBrown;€4.00
3.c	Monk's Stout;5.2% - Brasserie Dupont, Belgium;stoutBlack;€4.00

What I have tried to do until now is to replace the delimiters with a new unique delimiter "*" and then split into rows for this new delimiter. However, I have only managed to change the first instance of my multiple. Here is the M code that I am using, but I am not able to make it change for multiples of 4, if there are any (or more specifically multiples of 4 minus 1 due to the base 0). 

= Table.AddColumn(#"Add Text", "Custom", each Text.ReplaceRange([Text],Text.PositionOf([Text],";",Occurrence.All ){3},1,"*"))

Meaning, if there are 19 ";" in total the 4th, 8th, 12th and 16th ";" should be changed to a "*" as well. 

I don't know if this is the best approach but it is the best I could think of. Any help will be appreciated!

[AlexisOlson · Accepted solution]

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 · Accepted solution]

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

[MelonCocktail]

Thank you as well, it works!

[AlexisOlson · Accepted solution]

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"

[MelonCocktail]

Thank you for the thorough explanation, works perfectly!