Power query: Remove duplicate record based on criteria

[Wishmaster89]

Hi all

So I have some data in power query where their is a duplicate learner ID but some records also have a restart value of True. Where this is the case i want to keep the record which = True.

Please see sample data below, it is in excel as i don't want to post actual data from power query as its sensitive.

Book1
	A	B
1	Learner ID	Restart
2	1
3	1	TRUE
4	2
5	2	TRUE
6	3
7	3
Sheet1

 

[johnny51981]

You'll probably want to do a Group By in the query editor, with your Learner ID being what to group by, and then setting your Restart column to Max. It will cause it to condense to one line per Learner ID.

 

[Wishmaster89]

That deleted every column except the first one :/

Thanks for the suggestion though.

 

[johnny51981]

Well, not knowing what all you have in your sheet aside from the two columns....you'll need to include the missing columns in the same piece of the group by as your Restart column, using Max for each.

 

[citizenbh]

Filter column B for TRUE

 

[Wishmaster89]

Can’t because theres other records that the true value doesn’t apply to.