Remove all rows that don't meet certain criteria

[Dani001]

Hello,

I have a table that I would like to remove some rows that don't meet a criteria.

I have two columns, Candidate ID and Application Status: 

I want to keep all "Candidate IDs" that are identical AND also in the status "Hire, Default AND Interview 1", but I do not want any identical "Candidate ID" that are not in all those status, or only in a few of them, so if they are only duplicate in Hire and Default, but not in Interview 1, I wouldn't want to keep them.

How could I solve this?

Thank you very much 🙂

[v-heq-msft · Accepted solution]

Hi @Dani001 ,

Here some steps that I want to share, you can check them if they suitable for your requirement.

Here is my test data:

Please put all the M code into Advanced Editor

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfLILEpVitWBcFxS0xJLc0rgfM+8ktSisszUcgVDsJgRsgYjLAqM0QwxxqEGIWYEFjNBNtgEzRATLIaYwjXEAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Candidate ID" = _t, #"Application Status" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Candidate ID", Int64.Type}, {"Application Status", type text}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Custom", each let a=Text.Combine(Table.SelectRows(#"Changed Type",(x)=>x[Candidate ID]=[Candidate ID])[Application Status]," ")
in if Text.Contains(a,"Hire") and Text.Contains(a,"Default") and Text.Contains(a,"Interview 1") then 1 else 0),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Custom] = 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom"})
in
    #"Removed Columns"

Final output

Best regards

Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Dani001]

Hello,

It sounds like this should do the trick, but my Data comes from and Excel Workbook, and I have been trying to modify the formula you gave me, but it either gives me "eof token expected" or "identifier token expected" all the time.

What do you think could be the problem? 

Thank you very much!

[v-heq-msft]

Hi @Dani001 ,
According to your error message, I checked the documentation about the problem, the reason for this error may be because power query is case sensitive, you need to check if there is any case mismatch. If this is not the reason, please provide the full code in advaced editor or share your pbix file. Please hide the sensitive information in advance.

Best regards

Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Dani001]

Hello, 

I will share some code that appears in "Advanced Editor", which should be correct (different from yours):

let Origen = Excel.Workbook(File.Contents("C:\Users\fileorigin"), null, true), #"report_REC_KPI s - Month_Sheet" = Origen{[Item="report_REC_KPI s - Month",Kind="Sheet"]}[Data], #"Encabezados promovidos" = Table.PromoteHeaders(#"report_REC_KPI s - Month_Sheet", [PromoteAllScalars=true]), #"Tipo cambiado1" = Table.TransformColumnTypes(#"Columnas quitadas1",{{"Created Date_1", type date}}), #"Columna condicional agregada" = Table.AddColumn(#"Columnas quitadas2", "Source Real", each if Text.Contains([Source Details], "Corporate") then "LinkedIn Research" else if Text.Contains([Source Details], "Employee Referral") then "Referral" else "Job Posts"), #"Errores quitados" = Table.RemoveRowsWithErrors(#"Columnas reordenadas"),

I hope this helps.

Thank you very much!

[v-heq-msft]

Hi @Dani001 ,
Thank you for your patience, according to the code you provided, we unable to determine where the error occurs, please provide a screenshot of the error report including the error message and code or pbix file so that we can better help you.

Best regards,

Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[AndrewPF]

You could try grouping by Candidate ID, using Text.Combine function to combine the application statuses. 

You will end up with data similar to: 

CID       Status

100       Hire; Default; Interview 1

101       Hire; Default

102       Default; Interview 1

Then filter the data to keep only Status = "Hire; Default; Interview 1"