Un-merge a table to create unique values from two columns

Question

I have a table that has 2 columns:

'Project ID' and 'Tool size".

A Project can have more than one tool size. Currently in Excel every tool is entered in a new row with a duplicate project number.

I want to avoid Project Number duplicates and instead combine all tool sizes for that Project into one cell with a comma.

If a project contains 1 tool, leave as is.

If a project contains 2 tools, join the two tools in one cell for that project number.

If a project contains 3 tools, join the three tools in one cell for that project number and so on.

I tried =IF($B2=B3,CONCATENATE(C2,",",C3),C2)

But the formula won't work if there are more than two tools for a project

see [TEXTJOIN for xl2010/xl2013 with criteria](https://stackoverflow.com/questions/50716550/textjoin-for-xl2010-xl2013-with-criteria/50719050?s=1|83.2743#50719050) — , Jul 25 '18 at 22:42
@Jeeped textjoin won't take care of the situation when there are more than two tools (B6=B7=B8). Concatenate should be enough, I just need to "Count the number of duplicate project numbers, and enter their respective tool size values in column D — Victor --------, Jul 25 '18 at 22:47
See [this](https://i.stack.imgur.com/jGTFM.png) before you start discounting responses out of hand. — , Jul 25 '18 at 22:53
@Jeeped, thank you sir. You are right, but I was also missing the COUNTIF part, which I was more concerned about without realizing that concatenate won't work. — Victor --------, Jul 25 '18 at 22:59
If you are OK with just one row per project in the desired result, you can use `Get&Transform` — Ron Rosenfeld, Jul 26 '18 at 01:38
@RonRosenfeld That's what I was thinkng with my question on Excel Version (as PQ is baked in to 2016, meaning you don't have to beg IT to install it). But I can't seem to nail the PQ query to do this. Would be keen to see someone use this approach.. — jeffreyweir, Jul 26 '18 at 01:56
@ Ron Rosenfeld. I do have PQ installed. I also had no idea how to use PQ for this task, I mostly use it for VLOOKUPS when I need to merge stuff — Victor --------, Jul 27 '18 at 04:40

Ron Rosenfeld · Accepted Answer · 2018-07-26T02:12:11.943

If you are OK with one line per Project Number in your results, you can use Get&Transform for this. Most steps can be done using the GUI.

Group Rows by Project Number with an operation of "All Rows"

Add Custom Column with a formula:

Table.ToList(
    Table.Transpose(
      Table.FromList(
        Table.Column([Tools],"Tool Size")
)
)
)

In the above formula Tools is what I Named the new column when I grouped the rows and Tool Size is the name of the column in the original data

Then you just Expand the new Tools column with a comma delimiter, and delete the table column.

The entire M code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table9"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project Number", Int64.Type}, {"Tool Size", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Project Number"}, {{"Tools", each _, type table}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.ToList(
    Table.Transpose(
      Table.FromList(
        Table.Column([Tools],"Tool Size")
)
)
)),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom1", "Custom"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Tools"})
in
    #"Removed Columns"

The results:

@jeffreyweir thank you! I do have PQ. I just didn't know PQ is capable of this, learn something every day — Victor --------, Jul 27 '18 at 04:41
@Victor-------- It's the formula in the custom column that required research for me who has minimal familiarity with M code. — Ron Rosenfeld, Jul 28 '18 at 19:51

Un-merge a table to create unique values from two columns

1 Answers1