Distinct Tables

[Anonymous]

Hi all,

I have been struggling to figure out how to get this to work in PowerQuery [If possible] for the past couple days.
We are trying to create a report based off three columns. (Emplyee Name, Program Name, and Status). An example Excel file screenshot is provided below.

Excel.
What we are trying to do is dedupe the table AND check the status of the report. 
Example (I deduped the table based on Employee Name and Program Name): 


Bob still has two entries. The difference is the Status column. If a user has two different rows with Status of 'Incomplete' and 'Complete', we want to remove the 'Incomplete' row. 
Some users won't have multiple rows. So they may just have the status marked as 'Incomplete', which is fine and we want to keep those. 

I've googled for a couple days and read quite a few articles, wasn't able to figure it out in PowerQuery.
There may be an easier way to this, still learning PowerBi!

Any help would be greatly appreciated!

[v-piga-msft · Accepted solution]

Hi @Anonymous ,

By my tests, you could create the group index firstly and create a conditional column, then filter the null value.

Here is the M query you could refer to.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcspPUtJRcq5MSi0C0fm5BTmpJalAZnBqcmlRZkmlUqxOtJJzRlFmMRnqPPOScahEtRe3usjUxAKgmF9qSXl+UTYeq4nziGNiXiJhe2MB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Emp = _t, Pro = _t, Status = _t, #"Pro Alt Name" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Emp", type text}, {"Pro", type text}, {"Status", type text}, {"Pro Alt Name", type text}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Emp", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Emp"}, {{"All Rows", each Table.AddIndexColumn(_,"Index",1,1), type table}}),
    #"Expanded All Rows" = Table.ExpandTableColumn(#"Grouped Rows", "All Rows", {"Pro", "Status", "Pro Alt Name", "Index"}, {"All Rows.Pro", "All Rows.Status", "All Rows.Pro Alt Name", "All Rows.Index"}),
    #"Added Conditional Column" = Table.AddColumn(#"Expanded All Rows", "Custom", each if [All Rows.Index] = 1 then 1 else null),
    #"Filtered Rows" = Table.SelectRows(#"Added Conditional Column", each ([Custom] = 1))
in
    #"Filtered Rows"

This is the output.

Best Regards,

Cherry