Use PowerQuery to pull all rows of the same unique ID when a certain value in the rows is met.

[Mehal1996]

Hi there,

Is it possible for PowerQuery to look at all the distinct rows which refer to the same Unique ID and return only the Unique IDs (with all distinct rows included) that have a specific value included. An example is shown below which shows the data as is. In this example, the second table further below shows that I want to return all distinct rows related to a Unique ID where the Subject feature Lead.

Data As Is:

Unique ID	Subject	Activity Logged By
1	Lead	John Sed
1	Presentation	James Bate
2	Assessment	Liz Benton
2	Demo	Alex Green
3	Lead	Gill Bay
4	Support	Fay Smith

End Result after solution in Power Query:

Unique ID	Subject	Activity Logged By
1	Lead	John Sed
1	Presentation	James Bate
3	Lead	Gill Bay

As you can see, Unique ID data for 2 & 4 have been filtered out due to no distinct rows being associated with a Lead. On the other hand, Unique ID's 1 & 3 remain in the final data extract as they both feature distinct rows which have Lead featured at least once.

Additionally, If it is possible, I would also need to do the opposite for another table view, therefore, I would need to return all distinct rows related to a Unique ID that dont have the Subject set as Lead.

Thanks for your support. 

[v-yingjl]

Hi @Mehal1996 ,

mahoneypat is correct, if you also want to get another table which not contains 'Lead', you can use 'Left Anti' to merge the new table and the source table:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("RY1LCsMwDESvIrzOpp8LNJQGShcFL4MXggzE4E+wXGh6+soJpSsNbx6jcTQH05kHeNJzz3Mii8m4bufPAkGqXH1OrecIoZ4rNuOo6CICkahSm/Ef6jWq/OuviLlpAW8aCrA3p//PwYegk+vGzwrsa1lyaXM3XslGX2fj3Bc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Unique ID" = _t, Subject = _t, #"Activity Logged By" = _t]),
    #"Merged Queries" = Table.NestedJoin(Source, {"Unique ID"}, Query1, {"Unique ID"}, "Query1", JoinKind.LeftAnti),
    #"Removed Columns" = Table.RemoveColumns(#"Merged Queries",{"Query1"})
in
    #"Removed Columns"

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mahoneypat · Accepted solution]

Here's one way to do it in the query editor.  To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("RY1LCsMwDESvIrzOpp8LNJQGShcFL4MXggzE4E+wXGh6+soJpSsNbx6jcTQH05kHeNJzz3Mii8m4bufPAkGqXH1OrecIoZ4rNuOo6CICkahSm/Ef6jWq/OuviLlpAW8aCrA3p//PwYegk+vGzwrsa1lyaXM3XslGX2fj3Bc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Unique ID" = _t, Subject = _t, #"Activity Logged By" = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Keep", each let 
thisID = [Unique ID],
LeadRows = Table.SelectRows(Source, each [Unique ID] = thisID and [Subject] = "Lead"),
Result = if Table.RowCount(LeadRows)>0 then "Y" else "N"
in 
Result),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Keep] = "Y")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Keep"})
in
    #"Removed Columns"

Pat

[watkinnc]

= Table.SelectRows(Table, each List.Contains(List.Distinct(Table.SelectRows(Table, each [Subject] = "Lead")[Unique ID], [Unique ID]))

The converse:

Table.SelectRows(Table, each List.Contains(List.Distinct(Table.SelectRows(Table, each [Subject] <> "Lead")[Unique ID], [Unique ID]))

--Nate