CODEWITHSUNDEEP
excel
Victor --------
Victor --------

Reputation: 512

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

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.

enter image description here

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

Upvotes: 0

Views: 36

Answers (1)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60334

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:

enter image description here

Upvotes: 2

Related Questions