Repeating Record Values

[bmarcowka]

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? 

[BA_Pete]

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

[Anonymous]

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"})