Date Manipulation

[Anonymous]

I'm trying to create a query that references a previous query but removes the last 3 days worth of contents.

Although I could just run another SQL query to do this, I thought it may be more efficient to use truncate the already obtained data.

However, it's not been as simple as it seems.

There is no relative terminology that I could find in M Query, so I've gone down the lines of adding a conditional column.

[Date] = Date.AddDays(Date.From(DateTime.LocalNow), -3)

I seem to struggle with date manipulation in Power BI - there are so many built-in functions, but not all of them play well together.

I'd appreciate any pointers or help you can give.

Thanks,

Chris

[mahoneypat]

Here is one easy way to do this

1. Right click on your first query and choose Reference (so that you only load data once on refresh)

2. Click on the pull down on your Date column and choose Date Filters / In the Previous and choose 3 Days

You will get a step added that looks like this

=Table.SelectRows(#"Changed Type", each Date.IsInPreviousNDays([date], 3))

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

Thanks, @mahoneypat ,

That sounds like it's just going to give me the last 3 days, where I want everything but what happened in the last 3 days.

However, I think I've found an answer based on information I found here: - Convert DateTime into Date in Query Editor using M function 

#"Added Conditional Column" = Table.AddColumn(Source, "Custom", each if [Date] < DateTime.Date(Date.AddDays(Date.StartOfDay(DateTime.LocalNow()),-3)) then "Keep" else "Remove"),

This seems to give me what I want in a column, and then I can add a step to remove any row with "Remove" in that column.  Happy to look at alternatives though if you think your option can also work?

[mahoneypat · Accepted solution]

That would work too, or you could just add "not" to what I sent before

=Table.SelectRows(#"Changed Type", each not Date.IsInPreviousNDays([date], 3))

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

Thanks @mahoneypat 🙂