Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Rasmus_A
Frequent Visitor

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.

 

filter tables.png

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.

 

result.png

 

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):

 

tables.png

 

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.

desired outcome.png

 

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 🙂

1 ACCEPTED SOLUTION
Anonymous
Not applicable

My reply was too quick I did not understand your problem Smiley Very 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 : 

image.png

 

Here is a step by step explaination so you can understand what we are doing :

1. Duplicate column Item

image.png

2. Exctract the two first characters from the duplicated column

image.png

3. Select the duplicated column again and then in the Transform Ribbon choose Pivot column

image.png

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

image.png

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

image.png

select Dough column from Production BOM Line and Item column from table Item, and let Left Outer as Join kind

image.png

6. Develop the new column

 image.png

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

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

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
    Result

Tell 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

Anonymous
Not applicable

My reply was too quick I did not understand your problem Smiley Very 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 : 

image.png

 

Here is a step by step explaination so you can understand what we are doing :

1. Duplicate column Item

image.png

2. Exctract the two first characters from the duplicated column

image.png

3. Select the duplicated column again and then in the Transform Ribbon choose Pivot column

image.png

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

image.png

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

image.png

select Dough column from Production BOM Line and Item column from table Item, and let Left Outer as Join kind

image.png

6. Develop the new column

 image.png

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! 😄

 

Kind regards, 

Rasmus

Anonymous
Not applicable

You're welcome, have a nice day ! 😄 

 

Regards, 

Etienne

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.