TextJoin Matrix

Question

Hello stackoverflow community! This is my first post on here so be gentle!

I have been working on trying to automate the naming system for the creation of new products at the company I work for. We come out with new SKUs pretty often so I want to make it as easy as possible for new items to work with our current systems.

Here is what I would like to accomplish: There are different product types, sizes, flavors, and ratios that we have. I want to be able to generate a list of all of the different products we make based off of information entered into a table. For example, if we want to add a bath bath bomb we might have a small one and a large one, lets say a 50g and a 150g. We might also have a couple different smells, such as one with lavender and one with myrrh. So based off of information entered like this:

Name	Scent 1	Scent 2	Size 1	Size 2
Bath Bomb	lavender	Myrrh	50g	150g

I would like to generate a list like this:

Bath Bomb, Lavender, 50g

Bath Bomb, Lavender, 150g

Bath Bomb, Myrrh, 50g

Bath Bomb, Myrrh, 150g

(It doesn't have to be in the same order shown above)

The problem I have is as follows: Some of our products might have 2 sizes where others might have more, same with flavors/ratios. Due to this, the table I have has many blank spots (since Size 2 might not be filled on items with only one size) I am currently experimenting with creating a matrix of every possible combination based on a row but I am not sure how to do this without spending hours setting up TextJoin functions using ISBlank to get rid of any rows that are trying to reference empty cells.

Any help would be greatly appreciated!

Update: To better show what I am trying to do, here is an image that shows an example of a table with a list I manually created below it:

Example Table and List

Answer 1

If I have understood the objective well, you are trying to do a nested unpivot (normalize or melt). You are nesting two attributes: Scents and Sizes, but it sounded like there could be more - if so, this method can be generalized in the following logic:

You are unpivoting Sizes by Scents and then unpivoting that result by Name with an exception that Scents can be blank. This will be a little more clear as we go.

If this is the input data:

Then this would be the expected output result where the blank sizes are removed:

Note that Rust Remover has no scents/flavors, but this is included, whereas the blank size rows are removed as per the rules above.

In this case, I see two ways to do this (excluding VBA):

• PowerQuery - requires Excel for Windows 2010+
• Dynamic Array Formula - requires Excel 2019 or 365 for either Windows or Mac

As the PowerQuery is very straight forward, I will propose that first. If it will not work for you, then the Dynamic Array method could be interesting, if you have the right Excel version, but it takes more time to build and test (on my side).

Power Query Method

This may look intimidating, but it takes very little time, just lots of clicking. 5 mins - tops

First, convert your table above into an Excel Table - you can click anywhere inside of it and press CTRL t. It will ask you if your table has headers - yes, it does.

Now go to the Table Design tab and on the left you can rename your table. I will name it prodTable.

Now go to the Data tab and click From Range/Table.

This will open the Power Query editor which should look like this:

I was just going to paste the PowerQuery M language script here and let you put it in your machine, but it would fail if your table does not match my mock-up, so we need to show the steps instead.

1) Unpivot Scents

Hold CTRL and select the scent columns and then choose Unpivot Columns in the Transform tab.

Double click the Value header on that column and rename it to "Scents". Right Click on the Attributes column and remove it.

2) Unpivot Sizes

Now repeat those steps for Sizes. Hold CTRL and select the size columns and then choose Unpivot Columns in the Transform tab.

Double click the Value header on that column and rename it to "Sizes". Right Click on the Attributes column and remove it.

At this point, you have what you described,

but you said that you wanted to have a text joined name, so let's add that here:

3) Create a Merged Name

Hold CTRL and select the three columns in the order that you want them to appear in the text join. Then select Merge Columns in the Add Column tab.

It will ask you what you want for a separator - I just used the pipe character. It will also ask you what you want to name this column - I chose SKU Name.

4) Load it into Excel

Now, you just need to move this back into Excel. Go to the Home tab and choose Close and Load to.

Then tell it that you want to load it into a new worksheet - that's easiest.

Now you will have a new worksheet called prodTable and it will have your transformed table of products. This is all a one-time set up. From now on, as users create new combinations, you would only need to refresh the table by right-clicking it and Refresh - PowerQuery will execute the M script that you just made and will generate all of the SKU's based on the inputs from the users.

If you have Excel for Mac or you want it to be based on a formula, then shout out in the comments. The only way that I know of to do this with a Dynamic Array required Excel 2019 because it will rely heavily on SEQUENCE and LET. I've already done unpivots/melts, but nested melts would take some thinking.

One More Approach

Here is an M script that you can load into the PQ Advanced Editor that will give you basically what you want where there are no scents/flavors, but there are sizes. It does not handle all of the nested attributes that you want and, unfortunately, it would have to be rewritten each time you add an attribute, but it give you an idea of the method:

let
   Source = Excel.CurrentWorkbook(){[Name="prodTable"]}[Content],
   #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Scent 1", type text}, {"Scent 2", type text}, {"Size 1", type text}, {"Size 2", type text}}),
   #"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,"WASNULL",Replacer.ReplaceValue,{"Scent 1"}),
   #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Replaced Value", {"Name", "Size 1", "Size 2"}, "Attribute", "Value"),
   #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Value", "Scents"}}),
   #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Attribute"}),
   #"Unpivoted Columns1" = Table.UnpivotOtherColumns(#"Removed Columns", {"Name", "Scents"}, "Attribute", "Value"),
   #"Renamed Columns1" = Table.RenameColumns(#"Unpivoted Columns1",{{"Value", "Sizes"}}),
   #"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns1",{"Attribute"}),
   #"Replaced Value1" = Table.ReplaceValue(#"Removed Columns1","WASNULL",null,Replacer.ReplaceValue,{"Scents"}),
   #"Inserted Merged Column" = Table.AddColumn(#"Replaced Value1", "SKU Name", each Text.Combine({[Name], [Scents], [Sizes]}, "|"), type text)
in
    #"Inserted Merged Column"

I tried to make the attributes dynamic, but this becomes hard without doing a lot of ETL. Somehow your objective seems within reach. Your idea of generating all possible combinations could work, but it will take a lot of set up in order to allow for the rules that you want in place.