Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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?
Solved! Go to Solution.
Hello, @fox_NL
trim_end = Table.TransformColumns(
your_table,
{{"Parts", each Text.TrimEnd(_, ";")},
{"Moulds", each Text.TrimEnd(_, ";")}}
)
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
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"
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
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!
Hello, @fox_NL
trim_end = Table.TransformColumns(
your_table,
{{"Parts", each Text.TrimEnd(_, ";")},
{"Moulds", each Text.TrimEnd(_, ";")}}
)
write an if statement with Text.EndsWith([column],";") then remove the last text value
Check out the July 2025 Power BI update to learn about new features.