Forum Discussion

imranamikhan's avatar
imranamikhan
Helper V
4 years ago
Solved

Power Query Last 7 Days From Max Date Column

Hi everyone,    I want to use Power Query to add a custom column which identifies whether a date value from a date column is "within" or "outside" the last 7 days from the maximum date in the date ...
Need Help
  • mahoneypat's avatar
    mahoneypat
    4 years ago

    Sorry. Forgot that part. Here you go.

     

    let maxdate = List.Max(#"Previous Step"[Date]) in if [Date] >= Date.AddDays(maxdate, -7) then "Y" else "N"

     

    Pat

imranamikhan's avatar
imranamikhan
Helper V
4 years ago

Thanks Pat. I suspect I have messed up the syntax here? The name of the Date column is "d".

 

 

let

    Source = Sql.Database("xxx", "xxx", [Query="select * from ##dates"]),

    #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"d", type date}}, "en-GB"),

    Custom1 = Table.AddColumn(#"Changed Type with Locale", "IsLast7Days", let maxdate = List.Max(#"Changed Type with Locale"[d]) in if [d] >= Date.AddDays(maxdate, -7)  then "Y" else "N")

in

    Custom1

  

imranamikhan's avatar
imranamikhan
Helper V
4 years ago

I was complicating things - now corrected by adding Pat's formula to a custom column.

 

let maxdate = List.Max(#"Changed Type with Locale"[d]) in if [d] >= Date.AddDays(maxdate, -7) then 1 else 0