CODEWITHSUNDEEP
excel-formulapowerquery
Goffer
Goffer

Reputation: 400

Delete values that are duplicate and move to another table

I would like to create a table based on a visible one, in which there will be no duplicate values in column A and no duplicate values in the content of column B. What functions should pay attention to? I am asking for advice, I haven't found a function to remove duplicates + move to another table. The number of rows varies depending on the data source.

enter image description here

Upvotes: 0

Views: 266

Answers (2)

Rosetta
Rosetta

Reputation: 2725

I assume your table is named Table1, you can do it this way:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Table.Distinct = Table.Distinct(Source),
    Text.Split     = Table.TransformColumns(Table.Distinct, {"B", (x)=>Text.Split(x, ", ")}),
    List.Distinct  = Table.TransformColumns(Text.Split, {"B", List.Distinct}),
    Text.Combine   = Table.TransformColumns(List.Distinct, {"B", (x)=>Text.Combine(x,",")})
in
    Text.Combine

enter image description here

If you like shorter version, then it can be as follow

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Table.Distinct = Table.Distinct(Source),
    Text.Distinct  = Table.TransformColumns(Table.Distinct, {"B", (x)=>Text.Combine(List.Distinct(Text.Split(x, ", ")),", ")})
in
    Text.Distinct

Upvotes: 2

Aleksei Zhigulin
Aleksei Zhigulin

Reputation: 1634

Specifically for you case you my use this code:

= Table.Group(YourTable, "A", {"B", each Text.Combine(List.Sort(List.Distinct(Text.Split([B]{0}, ", "))), ", ")})

But following code is more robust (since, there may be different values in each row of Column B):

= Table.Group(YourTable, "A", {"B", each Text.Combine(List.Sort(List.Union(List.Transform([B], each List.Distinct(Text.Split(_, ", "))))), ", ")})

Upvotes: 2

Related Questions