Remove duplicates by prioritizing rows based on another column

[Syndicate_Admin]

Hi, everyone.

I am having the following problem and don't seem to find any solution to implement this in Power Query.

I have a dataset of loading lists of orders. It looks like this:

 For the given Loading list (Ladeliste) I want to remove duplicate NVE numbers (shipping unit number) but only those that have empty values in LHM number column (loading unit ID). If a corresponding cell in LHM-Nr. column is not empty I want to keep it. For example, when removing duplicates in rows 2 and 6, I want to remove row 2 since LHM-Nr. column cell is empty. Correspondingly, between rows 3 and 7 I want to remove row 3. 

This is how the resulting table should look:

Removing duplicates in Power Query using Remove rows removes first rows. Sorting by loading list and then deleting rows might work but I am not sure if it will also not be deleting rows that have values in LHM-Nr. column.

Does anyone have any idea how to implement this?

[Syndicate_Admin]

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

[Syndicate_Admin]

Hi, edhans. 

Here is a sample data:

https://docs.google.com/spreadsheets/d/12v9392oeTLGUqKC98SZLt-cZVHAh4dhA/edit?usp=sharing&ouid=10760...

Please let me know if you need any clarifications.

Regards,

[Syndicate_Admin]

I might have a simpler solution. From this data set, I can see that there are only duplicates in NVE when LHM-Nr. is empty. In theory, you could just remove all rows where LHM-Nr. is empty and it would consequently remove duplicates from NVE. For this, you can click on the LHM-Nr. column, and on the top of Power Query click on Remove Rows+, then Remove Blank Rows. This should work.

Otherwise, you could just do what you said and sort by loading list. Since the rows move as a whole, you wouldn't mix any infos and could then remove duplicates, since the duplicate row without empty values would be on top. Hope this helps.

[Syndicate_Admin]

Hi, @JoaoPereira .

Thanks for the tip. Unfortanutely this will lead to data loss since I have data of over 12 000 rows.. Since I imported data from pdf and did the cleaning, I have some loading lists where Power Query didn't catch LHM numbers but only NVE numbers. If I delete all rows with empty LHM numbers it will delete this information as well, which I would like to avoid.

That's why I need a delicate solution so that it wouldn't lead to any information loss.

Regards,

[Syndicate_Admin]

Alright, the solution I came up with is a bit more complicated than it probably needed to be but it's all I got. Here is what I recommend:

Duplicate the LHM column. In the Transform section, divide column by Number of Characters (position 26) and by specific character (.). This will leave you with two columns, one with a 4 digit number (3422 for example) and a second column with the word pdf for the separator. Delete the pdf column. 

Now, duplicate the NVE column. 

Now, join the duplicate LHM column with the 4 digits and the NVE duplicate column. Since every NVE is unique, the only duplicate values in this new column will be when the 4 digits from the LHM are identical AND when the NVE column was blank, therefore not adding anything to the values.

Finally, click on the joined column from both duplicates and erase duplicates. From here, you can delete the joined column since the functions work without it. There you have it, you'll have a data set with duplicates but only when NVE is not empty. 

[Syndicate_Admin]

Hello - here is an option for returning the result.

I have created a sample table with similar specs.  The goal is to keep rows 2, 3, 5, 7, 8.

BEFORE

AFTER

SCRIPT

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJ0cgaSSrE6yFzfxJKMYrCYEZDn5OwCJF3z0nMyizPQRMFcYyDD2cUVRSNCDMw1ATJcXN1gXFOQgW7uyMbGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Student ID" = _t, #"Student Name" = _t, Subject = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Student ID", Int64.Type}, {"Student Name", type text},  {"Subject", type text}}),
    #"Grouped Rows" = Table.Group(
        #"Changed Type", 
        {"Student ID", "Student Name"}, 
        {
            { "Subject", each List.First ( List.Sort ( _[Subject], Order.Descending ) ) },
            { "All", each _, type table [Student ID=nullable number, Student Name=nullable text, Subject=nullable text]}}
    )
in
    #"Grouped Rows"

[Syndicate_Admin]

Hi, @jennratten .

Thank you for your solution.

I am not that advanced user in Power Query, that's I don't really fully understand what the code does. Could you please explain what the code does so that I adjust it to my case? Why do I actually need index column of Student ID and what does it do? Do I need to also create an index column in my case?

Regards  

[Syndicate_Admin]

Hi - no problem at all.  You don't need an index column.  That just happened to be the column in my sample data that I wanted to use as the column from which duplicates should be removed.  See this new script below.  I have added comments and examples of what you need to change to make it work for you.  Please let me know if you have any other questions.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJ0cgaSSrE6yFzfxJKMYrCYEZDn5OwCJF3z0nMyizPQRMFcYyDD2cUVRSNCDMw1ATJcXN1gXFOQgW7uyMbGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Student ID" = _t, #"Student Name" = _t, Subject = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Student ID", Int64.Type}, {"Student Name", type text},  {"Subject", type text}}),
    #"Grouped Rows" = Table.Group(
        #"Changed Type", 
        // Column(s) containing the values from which you'd like duplicates removed.  
        // Replace this with the NVE numbers column. {"NVE-Nr."}
        {"Student ID", "Student Name"}, 
        {
            // Column that contains blank/empty/null values that should be removed if 
            // another row exists that is not blank/empty/null.
            // "Subject" will be the name of the new column.
            // _[Subject] is the column in the current table whose values should be evaluated.
            // Your column name in the current table will need to be referenced a little differently
            // since it contains a hyphen.
            // { "LHM-Nr.", each List.First ( List.Sort ( _[#"LHM-Nr."], Order.Descending ) ) },            
            { "Subject", each List.First ( List.Sort ( _[Subject], Order.Descending ) ) },
            { "All", each _, type table }}
    )
in
    #"Grouped Rows"

[Syndicate_Admin]

Hey,

thanks again for your help.

It has worked but my goal is to get the duplicates removed and also pertain all the other information in the other columns as well. Now I only have two columns of NVE and LHM numbers with grouped tables that have the duplicate values and all the information I need. When I expand the tables I again get all the duplicates -- seems like I have to go over it again.  Is there a way to get the duplicates removed (as you already showed) and pertain all the other columns just next to the sorted data (after removing duplicates) so that I would have clean table with all the information I need?

Regards