Forum Discussion
Remove duplicates by prioritizing rows based on another column
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
Hi, Anonymous
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://drive.google.com/file/d/1kLj8vwUHDV14X2S273cIGtkbVLVLMXpc/view?usp=sharing
Please let me know if you need anything else.
Regards
AdministratorHello 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