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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
fox_NL
Helper I
Helper I

Split into new rows using delimiter, but last value also has delimiter

Hello all,

Some data I need to use comes from a MS Forms, which is downloaded into a Excel.

It is used to registrate usage of production moulds on the shop floor. The Forms user can choose multiple parts in the same category if these have no multiple moulds. 

And if the same part has multiple moulds he can choose multiple moulds.

 

This is example of the data that comes from MS Forms:

NoDateCategoryPartsMoulds
112-7-23 10:07:54TruckRoof trim;Wheel arch front;Wheel arch rear; 
212-7-23 7:57:53CoverFan plateMal 7;Mal 9;Mal 4;Mal 2;
312-7-23 7:57:38CoverDoor setMal 7;
412-7-23 7:23:12CoverDoor setMal 2;Mal 7;Mal 9;
512-6-23 14:56:24CoverMain coverMal 3;Mal 4;Mal 5;Mal 6;
612-6-23 11:01:48TruckUpper main body dashboard; 
712-6-23 11:01:34CaravanRoofpart 41 
812-6-23 9:25:57CoverFan plateMal 1;Mal 2;Mal 7;Mal 8;Mal 6;

 

I need to split this information. So each value in column Parts has it's own row. And each value in column Moulds has it's own row.

Unfortunately Forms always shows a ; even if only 1 option has been choosen in the form.

So this creates a problem: if I use split columns using the delimiter ; it will always split 1 row extra, because there is a ; on the end. 

This extra row contains false information for the data so I don't need it.

 

For the column Moulds I used this query:

Text.Start([Malnummer],Text.Length([Malnummer])-1)

Empty cells will result in a ERROR, but that's fixable.

I can't use this solution for colum Parts.

 

Any suggestions for how to get the right data?

2 ACCEPTED SOLUTIONS
AlienSx
Super User
Super User

Hello, @fox_NL 

    trim_end = Table.TransformColumns(
        your_table, 
        {{"Parts", each Text.TrimEnd(_, ";")},
        {"Moulds", each Text.TrimEnd(_, ";")}}
    )

View solution in original post

Anonymous
Not applicable

Hi @fox_NL ,

You need to run the code given by AlienSx before the split step.
Typically, we refer to the name of the previous step as the your_table parameter.

vcgaomsft_2-1702957966264.pngOutput:

vcgaomsft_1-1702957809155.png

You can also create a new blank query to copy the code below into the advanced editor to see all the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZHNasMwEIRfZfE5EGsl/0Q6hZTeDKW09GBy2MQKDnUss3EDffsqcnDttATESFo0H9rZsoxEtIgELrMlxihBxDrOdKJgXfj6G3/tP/3+6twBej6ezEdtbQPE+xoO7Np+WmBLbPxriLaLMsIZ1zP9kgN24y6W/f5MLXQN9dafC2ogM1ddBVVB0QSW/MuS+Zz15BzD2fYjKhjVnRGlFvjQiGb6kwBJBkg6QFAnqUYFL1NIQccW9uOlATlpIgmaDrB0BhNCx0KrfB73e9dZhtOVuXPVN1R0rneOuPpNN/sHI29T2xDThdrb3DriHpQYnfnMudKY+Dgfz0WY+2TysaXtDw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [No = _t, Date = _t, Category = _t, Parts = _t, Moulds = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"No", Int64.Type}, {"Date", type datetime}, {"Category", type text}, {"Parts", type text}, {"Moulds", type text}}),
    trim_end = Table.TransformColumns(#"Changed Type", {{"Parts", each Text.TrimEnd(_, ";")},{"Moulds", each Text.TrimEnd(_, ";")}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(trim_end, {{"Parts", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Parts"),
    #"Split Column by Delimiter1" = Table.ExpandListColumn(Table.TransformColumns(#"Split Column by Delimiter", {{"Moulds", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Moulds")
in
    #"Split Column by Delimiter1"

More details, please refer to:
Power BI Forum Help: How to integrate M-code into ... - Microsoft Fabric Community

Best Regards,
Gao

Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

View solution in original post

5 REPLIES 5
fox_NL
Helper I
Helper I

Thank you @AlienSx and @spinfuzer for your reply.

This is my uncufficient knowledge of the advanced editor but I'm not getting this to work.

I already had to apply a lot of steps in the query settings, so I need to add one of your solutions into these steps. 

I can't get eitherto work.

 

Below is my text from the advanced editor. There are names in the query that I can't show unfortunately, I signed a NDA. So I've deleted some and replaced it with xx. 

 

Could you tell me how to implement your solution?

 

let
Source = Excel.Workbook(Web.Contents("https://xx.sharepoint.com/sites/xx/polyester.xlsx"), null, true),
Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
#"Sorted Rows" = Table.Sort(Table1_Table,{{"ID", Order.Descending}}),
#"Changed Type" = Table.TransformColumnTypes(#"Sorted Rows",{{"ID", Int64.Type}, {"Start time", type datetime}, etc etc}),
#"Removed Blank Rows" = Table.SelectRows(#"Changed Type", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
#"Renamed Columns" = Table.RenameColumns(#"Removed Blank Rows",{{"Klant", "Klant1"}}),
#"Inserted Merged Column" = Table.AddColumn(#"Renamed Columns", "Klant", each Text.Combine({[Klant1], Text.From([Klanten overig], "nl-NL")}, ""), type text),
#"Inserted Merged Column1" = Table.AddColumn(#"Inserted Merged Column", "Parts", each Text.Combine({xxx}, ""), type text),
#"Inserted Merged Column2" = Table.AddColumn(#"Inserted Merged Column1", "Moulds", each Text.Combine({[#"Mallen "], [#"L+R"], [Ventilatorplaat], [Hoofdkap]}, ""), type text),
#"Removed Other Columns" = Table.SelectColumns(#"Inserted Merged Column2",{"ID", "Start time", "Completion time", "Email", "Name", "Jaar-week", "Gelost door", "Klant", "Product", "Moulds"}),
#"Added Custom1" = Table.AddColumn(#"Removed Other Columns", "Year-invoer", each Date.Year([Start time])),
#"Added Custom" = Table.AddColumn(#"Added Custom1", "Week-invoer", each Date.WeekOfYear([Start time])),
#"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom", "Jaar-week (alleen invullen als je later invult dan huidige week)", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Jaar-form", "Week-form"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Jaar-form", type text}, {"Week-form", Int64.Type}}),
#"Added Custom2" = Table.AddColumn(#"Changed Type1", "Jaar", each if [#"Jaar-form"] = "" then [#"Year-invoer"] else [#"Jaar-form"]),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "Week", each if [#"Week-form"] = "" then [#"Week-invoer"] else [#"Week-form"]),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom3",{{"Jaar", Int64.Type}, {"Week", Int64.Type}}),
#"Removed Other Columns1" = Table.SelectColumns(#"Changed Type2",{"ID", "Start time", "Name", "Gelost door", "Klant", "Parts", "Maoulds", "Jaar", "Week"})
in
#"Removed Other Columns1"

 

Anonymous
Not applicable

Hi @fox_NL ,

You need to run the code given by AlienSx before the split step.
Typically, we refer to the name of the previous step as the your_table parameter.

vcgaomsft_2-1702957966264.pngOutput:

vcgaomsft_1-1702957809155.png

You can also create a new blank query to copy the code below into the advanced editor to see all the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZHNasMwEIRfZfE5EGsl/0Q6hZTeDKW09GBy2MQKDnUss3EDffsqcnDttATESFo0H9rZsoxEtIgELrMlxihBxDrOdKJgXfj6G3/tP/3+6twBej6ezEdtbQPE+xoO7Np+WmBLbPxriLaLMsIZ1zP9kgN24y6W/f5MLXQN9dafC2ogM1ddBVVB0QSW/MuS+Zz15BzD2fYjKhjVnRGlFvjQiGb6kwBJBkg6QFAnqUYFL1NIQccW9uOlATlpIgmaDrB0BhNCx0KrfB73e9dZhtOVuXPVN1R0rneOuPpNN/sHI29T2xDThdrb3DriHpQYnfnMudKY+Dgfz0WY+2TysaXtDw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [No = _t, Date = _t, Category = _t, Parts = _t, Moulds = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"No", Int64.Type}, {"Date", type datetime}, {"Category", type text}, {"Parts", type text}, {"Moulds", type text}}),
    trim_end = Table.TransformColumns(#"Changed Type", {{"Parts", each Text.TrimEnd(_, ";")},{"Moulds", each Text.TrimEnd(_, ";")}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(trim_end, {{"Parts", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Parts"),
    #"Split Column by Delimiter1" = Table.ExpandListColumn(Table.TransformColumns(#"Split Column by Delimiter", {{"Moulds", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Moulds")
in
    #"Split Column by Delimiter1"

More details, please refer to:
Power BI Forum Help: How to integrate M-code into ... - Microsoft Fabric Community

Best Regards,
Gao

Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

Thank you very much! I need get used to the naming of the steps in the next. But your images helped me a lot. Perfect!

AlienSx
Super User
Super User

Hello, @fox_NL 

    trim_end = Table.TransformColumns(
        your_table, 
        {{"Parts", each Text.TrimEnd(_, ";")},
        {"Moulds", each Text.TrimEnd(_, ";")}}
    )
spinfuzer
Solution Sage
Solution Sage

write an if statement with Text.EndsWith([column],";") then   remove the last text value

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.