Forum Discussion
Remove duplicates by prioritizing rows based on another column
- 5 years ago
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
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
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
- jennratten5 years agoSuper User
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_Admin5 years agoAdministrator
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.
- Anonymous5 years agoNot applicable
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,
- jennratten5 years agoSuper User
I apologize if my last response was confusing. I will separate it clearly.
Your question:
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?
The first part of the response (answering your question).
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.
The second part of the response (explains how to add this to your existing query).
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.