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?
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
Helpful resources
Power BI Dataviz World Championships
The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now!
Power BI Monthly Update - December 2025
Check out the December 2025 Power BI Holiday Recap!
FabCon Atlanta 2026
Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
