Filter Before Date dynamically in Power Query

geofspa

New Member
Joined
Jan 7, 2012
Messages
30
Office Version
  1. 365
Platform
  1. Windows
Hi

I am really new to Power Query - please be gentle :)

I have been set a task to "automate" invoice target monitoring that can be updated, maybe weekly, that shows account controllers how close to achieving their monthly targets they are. The problem I am facing is the financial targets shown should only be for invoices due before the end of "This Month" but the data I am working with includes dates beyond this. Simply put is how can I dynamically filter in power query to only include everything before the beginning of next month.

Currently I have coded a hard filter for the begining of next month

= Table.SelectRows(TargetTotal, each [Due Date] < #date(2021, 3, 1))

Could the date here be dynamic?

I have a seperate query that contains calculation for the begining of next month

let
Source = DateTime.FixedLocalNow(),
#"Extracted Date" = Date.From(Source),
#"Converted to Table" = #table(1, {{#"Extracted Date"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
#"Inserted End of Month" = Table.AddColumn(#"Changed Type", "End of Month", each Date.EndOfMonth([Column1]), type date),
#"Changed Type1" = Table.TransformColumnTypes(#"Inserted End of Month",{{"End of Month", type number}}),
#"Added to Column" = Table.TransformColumns(#"Changed Type1", {{"End of Month", each _ + 1, type number}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Added to Column",{{"End of Month", type date}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type2",{"Column1"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"End of Month", "Beginning Next Month"}})
in
#"Renamed Columns"

Could this be used to dynamicaly filter "Before Date"?

Hope this makes sense

Geof
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
615
Office Version
  1. 365
Platform
  1. Windows
There was another PQ question about taking the date from a named cell in a spreadsheet, which I just answered
Does that help you at all ? Its not exactly the same but may either achieve your purpose or give you an idea how you can use your End of Month query as input to your main query.

Filter BEFORE fixed date (Power Query)
I am fairly new to the forum so I am not sure about the protocol is for answering overlapping questions but below is what I responded on the above link.
--------------------------------------------------------------------------------------------------------------------------------
There are quite a few steps to this but they are pretty clear if you read this (its a link):-
Power Query Parameters – How to use Named Cells as Flexible Inputs (by Access Analytic)
It uses your exact same terminology of CutOffDate.

There is a warning about privacy setting but the article was written in May 2018 so I tried it without changing the settings and it seemed to work fine.
I tucked it away some time ago when I was looking at being able to use a cell to determine the data source for the query which is also covered in that same article.
--------------------------------------------------------------------------------------------------------------------------------
 
Solution

geofspa

New Member
Joined
Jan 7, 2012
Messages
30
Office Version
  1. 365
Platform
  1. Windows
Thank you Alex

That has set a whole train of though going - I think I know where I am going with this now
 

Watch MrExcel Video

Forum statistics

Threads
1,130,174
Messages
5,640,590
Members
417,152
Latest member
DayTimeSeby

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top