Forum Discussion

shade206's avatar
shade206
Brass Contributor
Jun 16, 2020

Returning results with a unique condition

Hello all,

 

On a second sheet I'm looking to extract only the rows where a USER ID has ONLY ONE "ANI" status, in this case the only rows that meet that criteria are for "Raj Singh" and "Mr Goerge"

 

USER IDNAMESTATUS
11Jane SmithANI
11John SmithANI
15Raj SinghANI
17Debbie DownerNIN
17Donald DownerANI
21Lee NguyenANI
21Chun Lee NguyenANI
22Fransisco GalvezNIN
22Franchesca GalvezANI
25Mr GeorgeANI

 

I have attached the sample workbook, a little help please?

BI & Data Analysis
excel
Formulas and Functions

9 Replies

  • ElElyon's avatar
    ElElyon
    Copper Contributor
    Jun 16, 2020

    shade206 Your criteria may have been conveyed incorrectly, as in the case below, the rows that meets the criteria.

     

    Criteria 1.

    - the rows where a USER ID has ONLY ONE "ANI" status is as shown below;

    USER ID           NAME           STATUS

    15Raj SinghANI
    17Donald DownerANI
    22Franchesca GalvezANI
    25Mr GeorgeANI

     

    Maybe the criteria you meant is as below

     

    Criteria 2.

    - User ID that appears once AND that has ONLY ONE "ANI" status.

    if this is the criteria then you will only return the rows as shown below

    USER ID     NAME       STATUS

    15Raj SinghANI
    25Mr GeorgeANI

     

    See the attached

     

    I have added 2 new sheet(Criteria 1 and Criteria 2) that gives you both results. I designed it using Power Query advance Group By function, for every change you make to Sheet1 you need to click Refresh to have it reflected to Criteria 1 and Criteria 2 sheets.

     

     

    • shade206's avatar
      shade206
      Brass Contributor
      Jun 16, 2020

      ElElyon while i really appreciate you taking the time to do this, i don't know exactly what you did to achieve this, i just have the results i desired. 

       

      I'd need to know what you did in order to recreate it myself against my actual data. 

      • ElElyon's avatar
        ElElyon
        Copper Contributor
        Jun 16, 2020

        shade206  

         

        I am glad it solved your problem. I will share the code below, if it still does not make sense.

        Let me know, which of the criteria you wanted. And I could look into doing a quick video for you.

         

        for Criteria 1 see the m code below.

        let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"USER ID", Int64.Type}, {"NAME", type text}, {"STATUS", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([STATUS] = "ANI")),
#"Grouped Rows" = Table.Group(#"Filtered Rows", {"USER ID"}, {{"Count", each Table.RowCount(_), type number}, {"New", each _, type table [USER ID=number, NAME=text, STATUS=text]}}),
#"Filtered Rows1" = Table.SelectRows(#"Grouped Rows", each ([Count] = 1)),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows1",{"New"}),
#"Expanded New" = Table.ExpandTableColumn(#"Removed Other Columns", "New", {"USER ID", "NAME", "STATUS"}, {"USER ID", "NAME", "STATUS"})
in
#"Expanded New"

         

        For the Criteria 2 see the m code below.

        let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"USER ID", Int64.Type}, {"NAME", type text}, {"STATUS", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"USER ID"}, {{"Count", each Table.RowCount(_), type number}, {"New", each _, type table [USER ID=number, NAME=text, STATUS=text]}}),
    #"Filtered Rows1" = Table.SelectRows(#"Grouped Rows", each ([Count] = 1)),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows1",{"New"}),
    #"Expanded New" = Table.ExpandTableColumn(#"Removed Other Columns", "New", {"USER ID", "NAME", "STATUS"}, {"USER ID", "NAME", "STATUS"})
in
    #"Expanded New"

         

        Cheers!

Resources