Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors