cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MelonCocktail
Frequent Visitor

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

 

RefOriginal
1Blanche des Honelles;6.0% - Brasserie de l'Abbaye des Rocs, Belgium;witbierWhite;€3.80
2Abbaye 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
3Moinette 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. 

...

 

RefWhat I need
1Blanche des Honelles;6.0% - Brasserie de l'Abbaye des Rocs, Belgium;witbierWhite;€3.80
2.aAbbaye des Rocs Brune;9.0% - Brasserie de l'Abbaye des Rocs, Belgium;Belge Forte FonceeBrown;€4.00
2.bAbbaye des Rocs Blonde;6.5% - Brasserie de l'Abbaye des Rocs, Belgium;blonde belgeblond;4
3.aMoinette Blonde;8.5% - Brasserie Dupont, Belgium;Belgian Strong Goldblond;€4.00
3.bMoinette Brune;8.5% - Brasserie Dupont, Belgium;Belge Forte FonceeBrown;€4.00
3.cMonk'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!

2 ACCEPTED SOLUTIONS
AlexisOlson
Super User
Super User

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

AlexisOlson_0-1654022535555.png

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:

AlexisOlson_1-1654022643285.png

 

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

AlexisOlson_2-1654022717075.png

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"

 

View solution in original post

wdx223_Daniel
Super User
Super User

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

View solution in original post

4 REPLIES 4
wdx223_Daniel
Super User
Super User

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

Thank you as well, it works!

AlexisOlson
Super User
Super User

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

AlexisOlson_0-1654022535555.png

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:

AlexisOlson_1-1654022643285.png

 

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

AlexisOlson_2-1654022717075.png

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"

 

Thank you for the thorough explanation, works perfectly! 

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors