Forum Discussion

Helper I

2 years ago

Solved

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...

Need Help

AlienSx

2 years ago

Hello, fox_NL

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

Anonymous

2 years ago

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

Helper I

2 years ago

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

2 years ago

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

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

fox_NL
Helper I
2 years ago
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!

Forum Discussion

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

Related content

split column by delimiter in dax

DAX formula help splitting string using delimiters

Escape row delimiter within quotes

decompress, skip lines, delimit

Splitting multiple columns using delimiter