Filtering column based on a date (or another columns date)

[Laura1996]

I have a table that is linked to a measure (for that reason I can't use normal filters), so I need a Power Query solution.

The table has a column called "Material" - which is filled with Materials looking like this: 9-*****, 4-*****, Y-*****, X-***** etc.

To calculate the inventory until the 1st of September (01.09) each year just Materials that start with 3-*****, 4-*****, Y-*****, X-***** should be considered. after the 1st of September all the rows should be considered (= no filter).

I tried including if/else into a select rows however it doesn't work, because Power Query can't use </> with the type "date"

Here my try:

= Table.SelectRows(#"changed type", each if ([todays date])<01.09.2022 then Text.StartsWith([Material], "3") or Text.StartsWith([Material], "4") or Text.StartsWith([Material], "X") or Text.StartsWith([Material], "Y") else true)

I don't know how to solve this - as I encounter another problem with this formula: THE YEAR. Because if next year "todays date" = 05.05.2023 this would be ">" 01.09.2022. However this should not happen. The formula should always just consider the month - in that case: is todays day before or after the 01.09? if after apply no filter - if before apply filter for Material that starts with 3,4,X,Y.

Hopefully someone can help me, as I'm completely new to this stuff 🙂

Kind regards 🙂

[Laura1996 · Accepted solution]

Posting the solution myself for educational purposes:

Step 1:

First of all one needs a column for the current date:

= Table.AddColumn(#"Literal eingefügt1", "todays date", each if[Material]=null then "" else DateTime.LocalNow())

It is crucial to check if the material has no value. Otherwhise the calculation later on will proceed.

Step 2:

Change to date type + add another column:

= Table.AddColumn(#"Geänderter Typ2", "Month", each Date.Month([todays date]))

This column should take out just the month of your date - so from: 22.09.2022 The column "Month" should display just the number 9.

Step 3:

= Table.AddColumn(#"Hinzugefügte benutzerdefinierte Spalte1", "True/False", each if [Monat] >= 10 then true else if [Monat] <= 9 then false else null)

This one says that all the numbers (months in this case) greater or equal to 10 (October) are true and all the numbers (months) that are smaller or equal to 9 (September) are false.

I choose true for "consider all the materials from october"

and false for "consider not all the materials until September" (in the next step will be declared which those are)

Step 4:

= Table.SelectRows(#"Hinzugefügte bedingte Spalte", each if [#"True/False"] = false then Text.StartsWith([Material], "3") or Text.StartsWith([Material], "4") or Text.StartsWith([Material], "X") or Text.StartsWith([Material], "Y") else true)

So if the column "True/False" is false then choose just the Materials that start with 3,4,X or Y else TRUE.

You can see that the filter works as soon as you change the date or the numbers in step 3.

[Laura1996]

Material	Charge	Status	Description	Place	Row	Kategory	State	todays date
A-***	01	A	...					14.09.2022
B-***	02	A	...					14.09.2022
1-***	03	B	...					14.09.2022
Y-***	04	C	...					14.09.2022
3-***	05	D	...					14.09.2022
4-***	06	D	...					14.09.2022
X-***	07	R	...					14.09.2022

I left out the filling of the other data because its actually irrelevant. The column "Date" was not there before - I added it: = Table.AddColumn(#"addedliteral", "todays date", each if[Material]=null then "" else DateTime.LocalNow())

the date is only added when the Material is not null, for the reason that the table could be also empty (out of SAP).

However because there is no date provided, and the person that checks the report wants to have always the current date I added this column. One of the problems is how to write the "SelectRows" statement to choose as follows:

Considering just the month - when the year reaches the 1st of september (01.09) - all the table should be displayed. However before the 1st of September of EVERY YEAR (not just the current 2022) just the Materials with the starting number 3-***, 4-***, X-***, Y-*** should be displayed. This is why I can't write a formula saying that todays date < "01.09.2022" as 2022 has to be "updatable".

Hope this helps to understand the issue  🙂

[Vijay_A_Verma]

Please post your table with dummy data.

How to provide sample data