Import files filtered by preferences

Hi alejandroezp ,

You can address this in Power Query by ensuring that, for duplicate Registrations, the record with Status = "service" is retained and the one with "garage" is removed.

Option 1 – Using Power Query UI Steps

1. Load your table into Power Query.
2. Select the Registration column.
3. (Optional) Use Home - Keep Duplicates to identify duplicate entries.
4. Add a Conditional Column: if [Status] = "service" then 1 else 2.
5. Sort the table by Registration (ascending) and then by the new column (ascending).
6. Go to Home - Remove Duplicates on the Registration column.
7. Power Query will retain the first instance, prioritizing the "service" row if present.

Result: Each Registration will have only one record, always keeping "service" when available.

Option 2 – Using Automated M Code

If you prefer to use code, you can apply the following M script:

let
   Source = YourTableName, // Replace with your table name
   AddPriority = Table.AddColumn(Source, "Priority", each if [Status] = "service" then 1 else 2, Int64.Type),
   Sorted = Table.Sort(AddPriority, {{"Registration", Order.Ascending}, {"Priority", Order.Ascending}}),
   Result = Table.Distinct(Sorted, {"Registration"})
in
   Result

This approach ensures your data is organized as follows:

Cars without duplicates remain unchanged.

For cars with duplicates, only the "service" record is kept.