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]

You can include all columns that you want to retain as a list in the second argument of the Table.Group function, like this: 

{"NVE-Nr.", "Next Column", "Next Column"} 

What you are doing there is creating a list of the column names to return, where each column name is inside double-quotes, the columns are separated by a comma, and wrapped in curly braces.

Alternatively, if you want to keep all columns, you could use this as the 2nd argument. Table.ColumnNames returns a list of columns for a table, and Lost.RemoveItems does just that, removes the column names from the list, so you can add a new column for that value.

List.RemoveItems(Table.ColumnNames(PriorStep), {"LHM-Nr."})

[Syndicate_Admin]

Hi,

Your first solution produced exactly the same table as before grouping with all the duplicates I wanted to get rid of, which I would like to avoid. Second one led to error where it says that Date column can't be converted into function.

Do you have any hint why it happened? I feel like more lost now. 

Here is a screenshot.

Regards

[Syndicate_Admin · Accepted solution]

Okay, please try this.  I have included comments that explain each step.

BEFORE: 

The goal is to remove rows 1, 3, 6, 8 and to have all columns present in the result.

RESULT:

SCRIPT:

There are two different options in the grouped step along with scenarios for when one would apply as opposed to the other, based on the specifics of the source data.  Currently, option 2 is in use.  To switch to option 1, add two forward slashes in front of Table.LastN and remove the two slashes at the beginning of let varTable and Table.FirstN.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jcy5DcAgEETRXjZGaHbBVwi+irDovw0vJiFAmOQjxGOeh0IIZIgB6BHiroW3YCsQ1kt+pGT+ISNXKiu9UTcEGaL1n40x5n/F7sfZGJ0q6HtwHoIMp10qOxV7XndjdB2CDK/dKKUX", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Latest = _t, #"NVE-Nr." = _t, Item = _t, Date = _t, #"LHM-Nr." = _t, Index = _t]),
    grouped = Table.Group(
        Source, 
        // Column(s) containing the values from which you'd like duplicates removed.  
        {"NVE-Nr."},
        {
            { "Table", // Name of the new column.
                each 
                //---------------------------------------------------------
                // Option 1: Sort descending, then select the first result.
                //---------------------------------------------------------
                // This will work if there is a maximum of only two rows per NVE-Nr.
                //let varTable = Table.Sort ( _, {{"LHM-Nr.", Order.Descending}}) in
                //Table.FirstN ( varTable, 1 ),
                //---------------------------------------------------------
                // Option 2: Select the last LHM-Hr for each NVE-Nr.
                //---------------------------------------------------------
                // This will work if the row to keep always appears last in the group.
                Table.LastN ( _, 1 ),                
                type table
            }
        }
    ),
    expand = Table.ExpandTableColumn ( 
        grouped, 
        "Table",                                  // Expand the tables in this column
        List.Difference (                         // New column names
            Table.ColumnNames (                   // are the column names 
                Table.Combine ( grouped[Table] )  // in the nested tables 
            ), 
            Table.ColumnNames ( grouped )         // that do not appear in the grouped table.
        ) 
    )

in
    expand

[Syndicate_Admin]

@SergeyShelest 

jennratten's solution is working in my test, if it doesn't return the correct output, please share a short sample pbix with expected output. So we can provide the exact solution.

Paul Zheng _ Community Support Team

[Syndicate_Admin]

Hi, @V-pazhen-msft 

Here is a sample data file. I did the grouping exactly as per jennratten's solution. If you expand the table you will get exactly the same table as before the grouping.

The file can be found here. I created a .pbix file but it would produce an error when opened on another pc since the source files (pdfs) are on my pc. The resulting table I put into the same folder:

https://docs.google.com/spreadsheets/d/1DxeOckz_5eTte3wUdyDUcmXC5zCGXIbW/edit?usp=sharing&ouid=10760...  

https://drive.google.com/file/d/1kLj8vwUHDV14X2S273cIGtkbVLVLMXpc/view?usp=sharing 

Please let me know if you need anything else.

Regards

[Syndicate_Admin]

Hello again - here is the same query as the last response with your source data plugged in.  Note - I am not able to download your files (blocker on my end), so I converted the image of your original table to an actual table.  

The only difference between the last query and this one is the value of the Source step.  Everything else is the same.  In case you are not already doing so, I recommend you copy this script, paste it into a blank query and then view the output.

BEFORE:

AFTER:

SCRIPT:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("vZVPa4MwGIe/i+ci778k5ihsDEYZwjZ2KEUctWPQw2D7/uxN5oyhxZMaQjAa8jzmF+PhUBAQAoK04MWRb/fdqb98fv/0LQtR+XU6F7sCpAQsw1DtvNUobAS8XnuPxGIgFATnOdzU2tR7bcPopvvo9VnsHXdL84imPLcgz4C5wYNqyqtW56GZ8vzq6xnz0wm1OIfWpijD2r5075cBDmuFOcB1pd0IJ14SPpNsevMJXNaHx5gTvEpwMwtHRgsuh5tb8Pr1+QGB2WHoPT7dGUBk0UmAtIrlv31239TaMs9ttOWglEFlGyhmULMNFDKo3QbK46YiQrSJP/8tL5vvuKtjvoNAtZEAjgJ6ssWsBwG/kQAkAWsoCQjMCNgg4BEzAbH/AvZaYDi+rgWcHQWAIVIHgfzfrPzjLw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Ladeliste = _t, Datum = _t, #"Auftragsnr." = _t, #"NVE-Nr." = _t, #"LNM-Nr." = _t, #"LHM-Typ" = _t, Index = _t, Page001 = _t, DeleteRowIndex = _t]),
    grouped = Table.Group(
        Source, 
        // Column(s) containing the values from which you'd like duplicates removed.  
        {"NVE-Nr."},
        {
            { "Table", // Name of the new column.
                each 
                //---------------------------------------------------------
                // Option 1: Sort descending, then select the first result.
                //---------------------------------------------------------
                // This will work if there is a maximum of only two rows per NVE-Nr.
                //let varTable = Table.Sort ( _, {{"LHM-Nr.", Order.Descending}}) in
                //Table.FirstN ( varTable, 1 ),
                //---------------------------------------------------------
                // Option 2: Select the last LHM-Hr for each NVE-Nr.
                //---------------------------------------------------------
                // This will work if the row to keep always appears last in the group.
                Table.LastN ( _, 1 ),                
                type table
            }
        }
    ),
    expand = Table.ExpandTableColumn ( 
        grouped, 
        "Table",                                  // Expand the tables in this column
        List.Difference (                         // New column names
            Table.ColumnNames (                   // are the column names 
                Table.Combine ( grouped[Table] )  // in the nested tables 
            ), 
            Table.ColumnNames ( grouped )         // that do not appear in the grouped table.
        ) 
    )

in
    expand

[Syndicate_Admin]

Hi, @jennratten .

Thank you very much for your help. I don't seem to understand the source step. So far I always left it since I though it was irrelevant for my case. Could you please explain what it does and what this random characters do (do I also need to adjust it if I am applying to a table with a bigger size and if yes how do I do it?)

Regards

[Syndicate_Admin]

Sure thing - this value is generated by copying data and pasting it into Excel.  For example, I converted your image into an Excel table.  Then I copied the Excel data and in Power Query, Home> Enter Data > paste > OK (the options will be slightly different if you are using Excel).  After doing so, a new query will be created and the only step that will be in the query is Source, with a similar script.  See the image below.  I  To integrate the sample script I provided with your query, copy the grouped and expand steps from my script, open the Advanced Editor for your query, paste them after the step which generated the table in your first post (likely the last step in your query), then replace "Source" in the grouped step with the name of your last step.

[Syndicate_Admin]

Sure thing - this value is generated by copying data and pasting it into Excel.  For example, I converted your image into an Excel table.  Then I copied the Excel data and in Power Query, Home> Enter Data > paste > OK (the options will be slightly different if you are using Excel).  After doing so, a new query will be created and the only step that will be in the query is Source, with a similar script.  See the image below.  I  To integrate the sample script I provided with your query, copy the grouped and expand steps from my script, open the Advanced Editor for your query, paste them after the step which generated the table in your first post (likely the last step in your query), then replace "Source" in the grouped step with the name of your last step.

[Syndicate_Admin]

Hi,

I am now a bit confused since I import lots of pds into Power Query first and clean them first. The images/samples I provided are the ones after the cleanup, meaning I have multiple previous steps that are in Power Query. I could copy and paste an Excel table into the Power Query but the thing is, I will be expanding my table when I get new pdfs. 

Is there any automatic way to remove duplicates without having to copy cleaned table in Excel and open it again in a new query but rather continue where I just left off processing my data?

 

Thanks and regards,