Forum Discussion
turning single column into multiple rows based on text values for filtering based on BOM combination
Hi everyone,
I’m in the process of trying to design a Power BI dashboard, where I want to be able to see which unique combinations a given BOM component has been used in.
The case is a pastry manufacturer where, for example, I am interested in being able to select a given dough recipe and then show all the different topping types and filling types that has been recorded in the BOM list with the given dough recipe and vice versa. A simplified illustration of this is given in the figure below.
I did make a prototype of the dashboard in Excel, where I had a large single sheet with all the information in, which allowed me to easily filter all the unique BOM combinations (or rows) based on the finished goods product number. (see figure below) However, I am having tremendous difficulties in making this work in Power BI with live data from our SQL server. I have tried multiple different approaches yet have not succeeded in making it work yet.
With the live data from the SQL server, I have the following tables in my model (although very simplified in number of columns and rows):
So, in a nutshell, what I want to achieve is to take the “Item No” column in the “Item” table and divide it into individual columns based on the item type (dough – “SD”, filling – “SF”, topping – “ST”) combinations listed in the “Production BOM Line” table. I have tried to illustrate it in the figure below.
As mentioned above, I have tried so many different options that I cannot remember all of them anymore, and it frustrates me that I think it would take me a few minutes to achieve this in Excel, but my knowledge of Power BI and SQL is relatively limited so it has taken me several days of trying and getting nowhere.
I hope the explanation of my problem makes sense, otherwise please let me know!
Thanks,
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
5 Replies
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
