cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MrSh3ld0 New Member
New Member

Distinct Tables

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.

DedupePowerBI.pngExcel.
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): 
PowerBIDeDupe.png

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Distinct Tables

Hi @MrSh3ld0 ,

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.

Capture.PNG

Best Regards,

Cherry

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
Community Support Team
Community Support Team

Re: Distinct Tables

Hi @MrSh3ld0 ,

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.

Capture.PNG

Best Regards,

Cherry

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Top Kudoed Authors
Users Online
Currently online: 132 members 1,666 guests
Please welcome our newest community members: