Return All Records with certain status in another column

Question

Hi guys,&nbsp;I have some data and it came from a manual entry CRM so it has alot of human errors. (which you will see in the Customer Stage, the status will be marked as XXX Error)&nbsp;The first step that I have to do is to filter out the Customer profile which is at the latest stage.&nbsp;&nbsp;Customer Stage ranking: Lead OR Lead Error -&gt; Prospect OR Prospect Error-&gt; Profile OR Profile Error (final stage)&nbsp;Below is a piece of sample data I have:&nbsp;Customer&nbsp;Profile StatusAssetALead&nbsp;10AProspect20BLead&nbsp;30BProspect30BProfile50CLead&nbsp;40CProfile60CProfile60DLead&nbsp;15DProspect20DProspect&nbsp;Error20EProfile&nbsp;5EProfile&nbsp;Error5&nbsp;The result I am looking for as per below:&nbsp;Customer&nbsp;Profile StatusAssetAProspect20BProfile50CProfile60CProfile60DProspect20DProspect&nbsp;Error20EProfile5EProfile&nbsp;Error5&nbsp;You will probably realize that there are still duplicates after the exercise, however, my goal here is not trying to de-dup but picking up the profile at their latest stages.&nbsp;Lastly, if this can be done in power editor that would be great as I will need to join this table with other tables afterwards. However, Measure is good too if there is no possible way in the editor.&nbsp;I tried approached this by grouping the customer 1st , creating orders for the status and&nbsp;then select the Max order, but this approach only return ONE record (which I willl need duplicate if it is in the same stage).&nbsp;May I know if someone can provide a way of doing this?&nbsp;Any tips are apprecaited.&nbsp;Thank you

danextian · Accepted Answer

I believe, you've already posted this but updated the data.&nbsp; The code I gave you wouldn't remove any duplicates but as mentioned, you need to update the condition for Profile Status to include those with error.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUfJJTUxRANKGBkqxOhChgKL84oLU5BIg0wgi6oSk0BghhKQQVTQtMycVyDKFCDoj6TZBCCHUmeERdEF2oylcCNONyKIKrkVF+UUIOVeEySBzTDEEYeqBMrEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, #"Profile Status" = _t, Asset = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer", type text}, {"Profile Status", type text}, {"Asset", Int64.Type}}),
    #"Trimmed Text" = Table.TransformColumns(#"Changed Type",{{"Profile Status", Text.Trim, type text}}),
    #"Added Custom" = Table.AddColumn(#"Trimmed Text", "Profile Status Order", each if [Profile Status] = "Lead" then 1 else if [Profile Status] = "Prospect" then 2 else if [Profile Status] = "Prospect Error" then 2 else if [Profile Status] = "Profile Error" then 3 else if [Profile Status] = "Profile" then 3 else 3),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Max", each let customer = [Customer]
in
List.Max(
Table.SelectRows(#"Added Custom", each [Customer ] = customer)[Profile Status Order])),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Filter", each [Profile Status Order]=[Max]),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom2", each [Filter] = true),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Customer", "Profile Status", "Asset"}),
    #"Sorted Rows1" = Table.Sort(#"Removed Other Columns",{{"Customer", Order.Ascending}})
in
    #"Sorted Rows1"
&nbsp;
&nbsp;

Forum Discussion

Return All Records with certain status in another column

1 Reply

Related content

Sorting Records

Filtering Record History by Period

Eliminate duplicate records while keeping the most recent record.

Counting Non-Blank Records

filtering out records that don't exist in another

Customer	Profile Status	Asset
A	Lead	10
A	Prospect	20
B	Lead	30
B	Prospect	30
B	Profile	50
C	Lead	40
C	Profile	60
C	Profile	60
D	Lead	15
D	Prospect	20
D	Prospect Error	20
E	Profile	5
E	Profile Error	5