Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a dataset of approximately 2.9m records that I am trying to find the records that have matching field values over the course of the preceding X (dynamic) number of days/weeks. What would be the best way to just filter these specific records only?
Hi @bmarcowka ,
My recommendation would be to select the field you want to check for duplicates, then run the GUI 'Keep Duplicates' function (Home tab > Keep Rows dropdown > Keep Duplicates).
Once PQ has generated the function for you, edit the table name within the Table.Group function wit a Table.SelectRows function to select your dates.
So you would go from seeing something like this in the formula bar:
To something like this:
In terms of your 'dynamic' requirement, PQ isn't really 'dynamic' in this way. I think the closest you would get to dynamic in PQ would be to have a separate query which only contains a date which can be referenced within a >, <, >=, <=, calculation within the Table.SelectRows function you added above.
Hope this makes sense.
Pete
Proud to be a Datanaut!
This feels too easy, but wouldn't the remove dulplicates option under the Remove Rows drop down work? Or insert the below code in the advanced editor. The below example is for when you want to remove rows when values in columns 1 and 2 are duplicated.
= Table.Distinct(#"Previous Step", {"Column1", "Column2"})
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.