Delete duplicates per key in a column

[Dag2]

Hey everyone,
I posted this earlier as follow-up in a different thread, but I thought it would be wiser to open a new one since its a different issue than the original one.

I want to delete duplicates per key in this table:

Keywords_Search_Klemens.xlsm
	A	B
1	Key	ActualCategories
2	1	Informationssicherheit
3	2	Informationssicherheit
4	3	Informationssicherheit
5	4	Vorgaben&Maßnahmen
6	5	Informationssicherheit
7	5	Daten&Informationen
8	5	Informationssicherheit
9	6	Informationssicherheit
10	6	Daten&Informationen
11	6	Informationssicherheit
12	6	Informationssicherheit
13	6	Vorgaben&Maßnahmen
14	6	Vorgaben&Maßnahmen
MappedSource

As you can see the Category "Informationssicherheit" (as well as "Vorgaben&Maßnahmen") appears multiple times at key 6. I want to delete duplicates, so I only have unique Categories per Key.
So it looks like this:

Keywords_Search_Klemens.xlsm
	F	G
1	Key	ActualCategories
2	1	Informationssicherheit
3	2	Informationssicherheit
4	3	Informationssicherheit
5	4	Vorgaben&Maßnahmen
6	5	Informationssicherheit
7	5	Daten&Informationen
8	6	Daten&Informationen
9	6	Informationssicherheit
10	6	Vorgaben&Maßnahmen
MappedSource

I wanted to work this out in PowerQuery but couldn`t figure out how to do it, so I am preferably looking for a solution there. Any other solution will be appreciated though!

Thanks in advance for your help!
Best regards,
Klemens

 

[Akuini]

I wanted to work this out in PowerQuery but couldn`t figure out how to do it,

Probably @alansidman could help.

I can offer a solution with VBA:

Sub Dag2()
Dim i As Long, j As Long, k As Long, n As Long
Dim va, vb
Dim d As Object

Set d = CreateObject("scripting.dictionary"): d.CompareMode = vbTextCompare
va = Range("A1", Cells(Rows.Count, "A").End(xlUp)).Resize(, 2)
ReDim vb(1 To UBound(va, 1), 1 To 2)

For i = 2 To UBound(va, 1)
 j = i
    Do
        i = i + 1
        If i > UBound(va, 1) Then Exit Do
    Loop While va(i, 1) = va(i - 1, 1)
    i = i - 1
    d.RemoveAll
   
    For k = j To i
        If Not d.Exists(va(k, 2)) Then
            d(va(k, 2)) = Empty
            n = n + 1
            vb(n, 1) = va(k, 1)
            vb(n, 2) = va(k, 2)
        End If
    Next
   
Next

Range("F2").Resize(UBound(vb, 1), 2) = vb

End Sub

 

[alansidman]

Mcode in Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Removed Duplicates" = Table.Distinct(Source)
in
    #"Removed Duplicates"