Filter import date to latest date on that date

[Mahmed1]

Hi

i have a dataset where i have multiple import dates on a given date

what i need to do is look at the date and then go back to previous week of import date and then get the latest import date

for eg

say i have a dataset like this

Date........Code.....Hours.....Import Date

26/07.....Test1......20.............03/07

26/07.....Test1......20.............04/07

26/07.....Test1......20.............14/07

26/07.....Test1......20.............19/07

26/07.....Test1......45.............21/07

in the example above...the week before the 26/07 is 17/07 to 23/07

there are 2 import dates that week 19/07 and 21/07

So the whole data should only be filtered for the 21/07 import date for that week

im basically just trying get the latest imported date going back only to previous week

i really hope im making sense 

i cant remove duplicates as both the hours and import dates change and doesnt give me the results i need

[Mahmed1 · Accepted solution]

I think this could work

Find max import date

then check to see if that max date = import date

of yes then filter that

i know what i want ti achieve but cant put it in a power query formula

[Mahmed1]

Thank you

[Mahmed1]

Hi 

what is the formula to get max date time

so my column has date time

21/07/2023 15:17:26

21/07/2023 17:17:25

What m code formula gives me the max date time from the column?

[Vijay_A_Verma]

Two ways

Table.Max(Previous Step Name, "Column Name")[Column Name]

List.Max(Previous Step Name[Column Name])

[Vijay_A_Verma]

Insert this step where you would need to replace Source with your previous step name

= Table.SelectRows(Source, each List.Contains([a=Date.AddDays(Date.StartOfWeek(Date.AddDays(Table.FirstValue(Source),-8)),1), b=List.Dates(a,7,#duration(1,0,0,0))][b], [Import Date]))

[Mahmed1]

Thank you

i will give this a try

is there any chance you can explain how the code works?

[Vijay_A_Verma · Accepted solution]

It picks up the first value of table through Table.FirstValue which is the date in your case.

Corresponding to this date, it finds the last Sunday which is 23-Jul for 26-Jul date. Then it will generate Monday (17-Jul) to Sunday (23-Jul) list. 

Filter will select only those rows where Import date lies in this list of dates.

[Mahmed1 · Accepted solution]

I think this could work

Find max import date

then check to see if that max date = import date

of yes then filter that

i know what i want ti achieve but cant put it in a power query formula