Forum Discussion
turning single column into multiple rows based on text values for filtering based on BOM combination
My reply was too quick I did not understand your problem :smileyvery-happy:
Okay so you have your 3 tables in the query editor.
The one that's interesting to us is Production BOM Line, we won't touch the others. I'll assume that columns type is text, otherwise just change the type to text before applying my changes.
Here is the Query :
let Source = Any, #"Duplicate" = Table.DuplicateColumn(Source, "Item", "ItemType"),
#"Split" = Table.TransformColumns(Duplicate, {{"ItemType", each Text.Start(_, 2), type text}}),
#"Pivot" = Table.Pivot(Split, List.Distinct(Split[ItemType]), "ItemType", "Item"),
#"Rename" = Table.RenameColumns(Pivot,{{"SD", "Dough"}, {"SF", "Filling"}, {"ST", "Topping"}}),
#"AddDough" = Table.NestedJoin(#"Rename", {"Dough"}, Item, {"Item"}, "Item", JoinKind.LeftOuter),
#"DoughResult" = Table.ExpandTableColumn(#"AddDough", "Item", {"Description", "Equipment"}, {"Dough.Description", "Dough.Equipment"}),
#"AddFilling" = Table.NestedJoin(#"DoughResult", {"Filling"}, Item, {"Item"}, "Item", JoinKind.LeftOuter),
#"FillingResult" = Table.ExpandTableColumn(#"AddFilling", "Item", {"Description", "Equipment"}, {"Filling.Description", "Filling.Equipment"}),
#"AddTopping" = Table.NestedJoin(#"FillingResult", {"Topping"}, Item, {"Item"}, "Item", JoinKind.LeftOuter),
#"ToppingResult" = Table.ExpandTableColumn(#"AddTopping", "Item", {"Description", "Equipment"}, {"Topping.Description", "Topping.Equipment"})
in
#"ToppingResult"Here is the result :
Here is a step by step explaination so you can understand what we are doing :
1. Duplicate column Item
2. Exctract the two first characters from the duplicated column
3. Select the duplicated column again and then in the Transform Ribbon choose Pivot column
The following menu will open, in Values Column select Item and in Aggregate Value Function select Do not aggregate (or something similar). Then hit OK
4. Rename the 3 new columns Dough, Filling and Topping.
5. We will now add Description and Equipment for each type : go to Merge Queries
select Dough column from Production BOM Line and Item column from table Item, and let Left Outer as Join kind
6. Develop the new column
7. Repeat operations 5 and 6 for Filling and Topping.
I hope this solution will work for you, tell me if there is any problem
Regards,
Etienne
Hi Rasmus_A ,
Could you try this : In the Query Editor,
Select your 3 columns (Dough, Filling and Topping)
Right click them and select 'Unpivot columns'
Here is the query :
let
Source = any,
Result = Table.Unpivot(Source , {"Dough", "Filling", "Topping"}, "ItemType", "ItemNo")
in
ResultTell me if that's what you expected ;)
Regards,
Etienne
Thanks for the quick reply!
I am having a bit of trouble understanding how I should proceed with your proposed solution as my issue is that I do not have the three columns "dough", "filling", and "topping" at the moment, rather that is my objective to have. I currently only have the single "Item No" column in the "Item" table, which I would like to separate into the aforementioned three individual columns based on the Item No code listed.
Kind regards,
Rasmus
My reply was too quick I did not understand your problem :smileyvery-happy:
Okay so you have your 3 tables in the query editor.
The one that's interesting to us is Production BOM Line, we won't touch the others. I'll assume that columns type is text, otherwise just change the type to text before applying my changes.
Here is the Query :
let Source = Any, #"Duplicate" = Table.DuplicateColumn(Source, "Item", "ItemType"),
#"Split" = Table.TransformColumns(Duplicate, {{"ItemType", each Text.Start(_, 2), type text}}),
#"Pivot" = Table.Pivot(Split, List.Distinct(Split[ItemType]), "ItemType", "Item"),
#"Rename" = Table.RenameColumns(Pivot,{{"SD", "Dough"}, {"SF", "Filling"}, {"ST", "Topping"}}),
#"AddDough" = Table.NestedJoin(#"Rename", {"Dough"}, Item, {"Item"}, "Item", JoinKind.LeftOuter),
#"DoughResult" = Table.ExpandTableColumn(#"AddDough", "Item", {"Description", "Equipment"}, {"Dough.Description", "Dough.Equipment"}),
#"AddFilling" = Table.NestedJoin(#"DoughResult", {"Filling"}, Item, {"Item"}, "Item", JoinKind.LeftOuter),
#"FillingResult" = Table.ExpandTableColumn(#"AddFilling", "Item", {"Description", "Equipment"}, {"Filling.Description", "Filling.Equipment"}),
#"AddTopping" = Table.NestedJoin(#"FillingResult", {"Topping"}, Item, {"Item"}, "Item", JoinKind.LeftOuter),
#"ToppingResult" = Table.ExpandTableColumn(#"AddTopping", "Item", {"Description", "Equipment"}, {"Topping.Description", "Topping.Equipment"})
in
#"ToppingResult"Here is the result :
Here is a step by step explaination so you can understand what we are doing :
1. Duplicate column Item
2. Exctract the two first characters from the duplicated column
3. Select the duplicated column again and then in the Transform Ribbon choose Pivot column
The following menu will open, in Values Column select Item and in Aggregate Value Function select Do not aggregate (or something similar). Then hit OK
4. Rename the 3 new columns Dough, Filling and Topping.
5. We will now add Description and Equipment for each type : go to Merge Queries
select Dough column from Production BOM Line and Item column from table Item, and let Left Outer as Join kind
6. Develop the new column
7. Repeat operations 5 and 6 for Filling and Topping.
I hope this solution will work for you, tell me if there is any problem
Regards,
Etienne
Anonymous , thank you very much for your elaborate description of how to solve the problem - it works perfectly! You have absolutely made my day! :D
Kind regards,
Rasmus
You're welcome, have a nice day ! :D
Regards,
Etienne