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

[fox_NL]

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:

No	Date	Category	Parts	Moulds
1	12-7-23 10:07:54	Truck	Roof trim;Wheel arch front;Wheel arch rear;
2	12-7-23 7:57:53	Cover	Fan plate	Mal 7;Mal 9;Mal 4;Mal 2;
3	12-7-23 7:57:38	Cover	Door set	Mal 7;
4	12-7-23 7:23:12	Cover	Door set	Mal 2;Mal 7;Mal 9;
5	12-6-23 14:56:24	Cover	Main cover	Mal 3;Mal 4;Mal 5;Mal 6;
6	12-6-23 11:01:48	Truck	Upper main body dashboard;
7	12-6-23 11:01:34	Caravan	Roofpart 41
8	12-6-23 9:25:57	Cover	Fan plate	Mal 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?

[AlienSx · Accepted solution]

Hello, @fox_NL 

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

[fox_NL]

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

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.

Output:

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

[fox_NL]

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

Hello, @fox_NL 

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

[spinfuzer]

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