Dynamically delete duplicate rows - leave no gaps

[dslhs]

Hi,

I have a table (A2 = Names, B2 = Courses). There are multiple duplicate rows - where the information in both the A column and B column are exactly the same. I would like to re-create the table, but without any duplicate rows. There will be duplicates in the A column, and in the B column, but I don't want there to be A&B. I'd also like the new linked table to have no row gaps.

I know I can Go to the Data tab > Data Tools group, and click the Remove Duplicates, but I'd like this to be dynamic as the table is linked to external data.

I don't have the 'Unique' function, as I'm in 2019.

Many thanks,

 

[alansidman]

you can do this in power query which is part of your version and called Get and Transform Data.

Keep or remove duplicate rows (Power Query)

Removes all rows from a Power Query table, in the Query Editor, where the values in the selected columns duplicate earlier values.

support.microsoft.com

 

[dslhs]

Not sure how? Would this be dynamic as the data changes frequently? Thanks

 

[alansidman]

the simple answer is yes. However, if you would like to see this in a demo, suggest you upload some sample data and a mocked up solution. Use XL2BB to do this. Do not load a picture as we can not manipulate data in a picture.

 

[dslhs]

Thanks. Below is a mini-sheet with anonymised details. Essentially, I want to dynamically recreate the table (A2:B31) in columns D and E, but without duplicate rows. So for example, row 7,. 11, and 12 wouldn't be copied over as they are a duplicate row with row 5 (both student and course). Thanks

Book1
	A	B	C	D	E
2	Name	Course/Class Name		Name	Course/Class Name
3	Student A	45C/Life Skills
4	Student A	Mathematics
5	Student A	KS45
6	Student A	Physical Education
7	Student A	KS45
8	Student A	45C/PE
9	Student A	Life Skills
10	Student A	45C/English
11	Student A	KS45
12	Student A	KS45
13	Student A	45C/Life Skills
14	Student B	Physical Education
15	Student B	45C/Wellbeing
16	Student B	IT + Computing
17	Student B	45C/Maths
18	Student B	IT + Computing
19	Student B	KS45
20	Student B	Mathematics
21	Student B	Independent Living Skills
22	Student B	Options 1/ILS
23	Student B	45C/Health Education
24	Student B	Options 2/IT
25	Student C	KS45
26	Student C	Mathematics
27	Student C	Health Education
28	Student C	Mathematics
29	Student C	English
30	Student C	IT + Computing
31	Student C	Computing
32
Class List

 

[alansidman]

Here is the Mcode. I imported your file to Power Query. Highlighted the two columns and clicked on Remove Rows-->Remove Duplicates.

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

 

[dslhs]

Brilliant. Got that to work.

While I have you... how would I do the same but only remove duplicates values from a column in power query. Specifically the second column titled 'Course/Class Name' for a Table called 'Courses'.

Many thanks,

 

[alansidman]

I'm not understanding your needs. Suggest you upload a sample and a mocked up solution. so that I have a better understanding.

 

[dslhs]

I'd like it so all the duplicates (e.g. KS3) in column F are removed - but dynamically as the source is external data that updates regularly. Where before I was looking to remove duplicate rows, I'm now looking to remove duplicates within a column please.

Thanks!

Book2
	E	F
2	Parent Course	Course/Class Name
3		Mathematics
4		Computing
5		Music
6		EHCP Targets
7		Engineering
8		English
9		Physical Education
10		Registration
11		Relationship + Sex Education
12		SaLT and Interpersonal Skills
13		Science
14		Life Skills
15		Drama
16		Creative Media
17		Food
18		Community Skills
19		Health Education
20		Humanities
21		Work/College Experience
22		Citizenship
23		Alternative Provision
24		Horticulture
25		Wellbeing
26		Future Skills
27		Art
28	Art	Options 2/Art
29	Art	KS3
30	Art	Options 1/Art
31	Art: KS3	3C/Art
32	Art: KS3	3D/Art
33	Art: KS3	3A/Art
34	Art: KS3	3B/Art
35	Citizenship	Options 1/Citizenship
36	Community Skills	KS4
37	Community Skills	KS5
38	Community Skills	KS45
39	Community Skills	KS3
40	Community Skills: KS3	3CD/Community Skills
41	Community Skills: KS3	3AB/Community Skills
42	Community Skills: KS4	4A/Community Skills
43	Community Skills: KS4	4AB/Community Skills
44	Community Skills: KS5	5AB/Community Skills
45	Community Skills: KS45	45CD/Community Skills
46	Computing	KS3
47	Computing	Options 1/Computing
48	Computing	IT + Computing
49	Computing: IT + Computing	Options 2/IT
50	Computing: KS3	3C/Computing
51	Computing: KS3	3D/Computing
52	Computing: KS3	3A/Computing
Class List

 

[alansidman]

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