Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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

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 IDSubjectActivity Logged By
1LeadJohn Sed
1PresentationJames Bate
2AssessmentLiz Benton
2DemoAlex Green
3LeadGill Bay
4SupportFay Smith

 

End Result after solution in Power Query:

 

Unique IDSubjectActivity Logged By
1LeadJohn Sed
1PresentationJames Bate
3LeadGill 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. 

Need Help
  • mahoneypat's avatar
    mahoneypat
    4 years ago

    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

     

3 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    4 years ago

    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

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    = 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

     

  • v-yingjl's avatar
    v-yingjl
    Community Support
    4 years ago

    Hi Anonymous ,

    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.