Forum Discussion
Helper IVFilter import date to latest date on that date
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
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
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.
7 Replies
Mahmed1I 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
- Vijay_A_Verma
Most Valuable Professional
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
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
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])
- Mahmed1
Thank you