Power Query - Remove row(s) question

[scott_86_]

Hi.

Using Microsoft 365.

Below is a snip example of a much larger dataset I am using.

In the snip below, 5/7 names (green) have 3 training types (Height, Rescue & PFA). 2/7 names (red) have PFA only.

Is there a way in Power Query to remove all rows (names) that have PFA only with no other training types? Thanks.

 

[Kerryx]

my error

 

[Alex Blakenburg]

You can do this in a single query but the below requires minimal M code changes
• Import the initial table
• Use Duplicate to create a second copy of the query
• in the 2nd copy with name seletected - Transform > Group > Count rows
• go back to the first query Home > Merge Queries > Left join on name to the 2nd (grouped query)
• Expand the merged table to show count
• Add a conditional column with Count = 1 then Y
• Go into the M code of this and add the part in blue
if [Count] = 1 and [Training] = "PFA" then "Y" else null
• Filter on custom = null
• Remove the columns Count and Custom
• Close and Load

XL2BB in case anyone else want to give you a more streamlined version:

20240515 PQ Remove rows single value scott_86_.xlsx
	A	B	C	D	E
1	Original			Result
2
3	Name	Training		Name	Training
4	Scott	Height		Scott	Height
5	Scott	Rescue		Scott	Rescue
6	Scott	PFA		Scott	PFA
7	David	PFA		Chris	Height
8	Chris	Height		Chris	Rescue
9	Chris	Rescue		Chris	PFA
10	Chris	PFA		Tim	Height
11	Tim	Height		Tim	Rescue
12	Tim	Rescue		Tim	PFA
13	Tim	PFA		Rick	Height
14	Rick	Height		Rick	Rescue
15	Rick	Rescue		Rick	PFA
16	Rick	PFA		Frank	Height
17	Jim	PFA		Frank	Rescue
18	Frank	Height		Frank	PFA
19	Frank	Rescue		Mary	Height
20	Frank	PFA
21	Mary	Height
22
Sheet1

 

[JEC]

Here the short version

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    grp = Table.Group(Source, {"Name"}, {{"Group", each if Table.RowCount(_)=1 and [Training]{0} = "PFA" then Table.RemoveRows(_,0,1) else _  } }),
    result = Table.Combine(grp[Group])
in
    result

 

[scott_86_]

Here the short version

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    grp = Table.Group(Source, {"Name"}, {{"Group", each if Table.RowCount(_)=1 and [Training]{0} = "PFA" then Table.RemoveRows(_,0,1) else _  } }),
    result = Table.Combine(grp[Group])
in
    result

Thanks, this works when I use the basic table from my original post.

I applied it to the large dataset I have; however, it returned an error. I believe the error is because my larger dataset has 10 columns instead of 2 in my example.

Would it be possible to amend the code based off the following column headers I have in my larger dataset?

• User - User full name (This is where the user's name is eg. Scott)
• User - Position
• Certifications - Certification title (This is where the training items are, eg. Provide First Aid - which was PFA in my example)
• Initial Completion Date
• Certification Expiry Date
• User - Location
• User - Manager
• User - Business Unit
• Certifications - Certification user status
• Certifications - Certification period is current