topic Re: Filter table based on date in Power Query

Filter table based on date

Anonymous — Thu, 07 Jul 2022 21:26:35 GMT

Hello,

I have the following table loaded in Power BI (Power Query). The table is called the Reference table.

Student Name	To submit as from	Submission optional (Yes/No)
Lizui	02 July 2022	No
Laufenburg		Yes
Tegalpapak	29 August 2022	No
Ar Rabiyah
Bellegarde	02 July 2022
Gangarampur		No
Luntas	05 May 2022	Yes
Frei Paulo	10 August 2022	Yes
Seedorf	03 July 2022	No
Cosamaloapan de Carpio	10 September 2022	No

The goal is to generate a new table called Results with a single column showing the list of Student names based on the following condition:

1. If the "To submit as from" date is before the current week (based on the ISO week date calendar, so, the week always starts on Monday and ends on Sunday). The current week started on Monday, 4 July 2022.

2. If the "To submit as from" date is blank/null, then the date should be assumed to be before the current week.

The Results table should be as below:

Student Name

Lizui

Laufenburg

Ar Rabiyah

Bellegarde

Gangarampur

Luntas

Seedorf

Is it possible to do this entirely in Power Query? Any help is much appreciated!

Re: Filter table based on date

otravers — Thu, 07 Jul 2022 21:39:10 GMT

Yes, it's possible with PQ, there are a lot of date-related functions. Your high- level logic would be to 1) start with the #2 nulls, then 2) deal with #1 using a conditional column. Filter on the outcome of that conditional column then drop all columns but Student Name.

Re: Filter table based on date

Anonymous — Thu, 07 Jul 2022 22:42:09 GMT

Hi otravers Thanks for your reply! But i am a beginner at Power Query. I am not sure about the code. I found this example and it is quite confusing! https://community.powerbi.com/t5/Desktop/Power-Query-choose-a-step-to-create-based-on-a-condition/m-p/385799

Re: Filter table based on date

otravers — Thu, 07 Jul 2022 23:59:55 GMT

You can do a lot with Power Query just with the UI without writing your own M code. Start with this:

https://docs.microsoft.com/en-us/power-query/add-conditional-column

Re: Filter table based on date

Vijay_A_Verma — Fri, 08 Jul 2022 05:19:20 GMT

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately. If you have columns other than these, then delete Changed type step and do a Changed type for complete table from UI again)

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZY9BC4JAEIX/iuzZhTI6dDShICoiu4R4GHG0pXV3WZuD/fomTUSCOey8N9/smywTR/UmJUIRyQNpGUX8PFuRh+wAVWgK8jVrXHdse/2GNWgHDp5fbCNjqmdc7IMrFKqDx8D14ha1Zs6XOP+rN/dg2ILGkR+QMQGZF7Tcr+UJugEYU+w8quACpC2Ly8WUYhxIEUvrK1ZWf6cltoUGtOUjTFBikIB36rcoRTfN5h8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Student Name" = _t, #"To submit as from" = _t, #"Submission optional (Yes/No)" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Student Name", type text}, {"To submit as from", type date}, {"Submission optional (Yes/No)", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [To submit as from]<=Date.StartOfWeek(Date.From(DateTime.FixedLocalNow()),1) or [To submit as from]=null), #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = true)), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"To submit as from", "Submission optional (Yes/No)", "Custom"}) in #"Removed Columns"

Re: Filter table based on date

AlexisOlson — Sun, 10 Jul 2022 22:43:39 GMT

Anonymous Adding the custom column is a great way to visualize which rows get filtered but it's also possible to do the filtering in one step with exactly the same logic rather than the three steps of adding a column, filtering that column, and then deleting that column.

The /*[abbreviated]*/ query would look like this instead:

let Source = Table.FromRows(/*[...]*/), #"Changed Type" = Table.TransformColumnTypes(Source, {/*[...]*/}), #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([To submit as from] <= Date.StartOfWeek(Date.From(DateTime.FixedLocalNow()),1) or [To submit as from] = null)) in #"Filtered Rows"