list of dates in a field, how to filter the list to exclude dates before date in another field

[MountainDweller]

I have a list of dates stored in a field and I want to add a new column that contains a subset of the list filtered to exclude dates before the date storred in a third column.

The following formula produces an error:

= Table.AddColumn(#"Expanded CR (Step1)", "FutureReferralDates", each List.Select([ReferralDateList], each _ > [Referral Date]))

It works if I replace [Referral Date] with a date literal. However, editor offers [Referral Date] as an available field to reference. 

What's wrong with the formula? Any suggestions for the best method to achieve this?

Result is error instead of the list {2023-07-04}The source column contains {2020-11-04, 2023-07-04} and the criteria column contains the date 2023-07-04

[Rickmaurinus · Accepted solution]

Hey Mountaindweller,

The code is a little hard to test without source dataset. But I think your issue lies in this step: 

= Table.AddColumn(#"Expanded CR (Step1)", "FutureReferralDates", each  List.Select( [ReferralDateList],each _ > [Referral Date] ) )

The each statement of List.Select calls a new context, so the scope of [Referral Date] (that lies outside of this context) can't be reached. What you can do is make a variable as follows: 

= Table.AddColumn(#"Expanded CR (Step1)", "FutureReferralDates", each let RefDate = [Referral Date] in List.Select( [ReferralDateList],each _ > RefDate ) )

Then this particular step will give you a result. I can't test the query, but please do let me know if this solves your issue. 🙏

--------------------------------------------------

@ me in replies or I'll lose your thread

Master Power Query M? -> https://powerquery.how

Read in-depth articles? -> BI Gorilla

Youtube Channel: BI Gorilla

If this post helps, then please consider accepting it as the solution to help other members find it more quickly.

[MountainDweller]

Thank you Rick! That works perfectly.

[MountainDweller]

Here is the full code.

let
Source = #"CR (Step1)",
#"Removed Other Columns" = Table.SelectColumns(Source,{"Patient #", "Referral Date"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"Referral Date", "OtherReferralDate"}}),
TypeChange = Table.TransformColumnTypes(#"Renamed Columns",{{"OtherReferralDate", type date}}),
Group = Table.Group(TypeChange, {"Patient #"}, {{"ReferralDatesTable", each _, type table [#"Patient #"=nullable number, OtherReferralDate=nullable date]}}),
#"Added ReferralDateList" = Table.AddColumn(Group, "ReferralDateList", each Table.Column([ReferralDatesTable],"OtherReferralDate")),
#"Removed Other Columns1" = Table.SelectColumns(#"Added ReferralDateList",{"Patient #", "ReferralDateList"}),
#"Merged Queries" = Table.NestedJoin(#"Removed Other Columns1", {"Patient #"}, #"CR (Step1)", {"Patient #"}, "CR (Step1)", JoinKind.RightOuter),
#"Expanded CR (Step1)" = Table.ExpandTableColumn(#"Merged Queries", "CR (Step1)", {"First Name", "Surname", "ReferringDoctor", "Referral Date", "Referral Urgency", "Referral Reason", "Referral Details", "Pat Md", "Status", "StatusComments", "Next Appt Date", "Next Appt Prvdr", "Extract Date", "Health Number", "CR Occurrence Date", "(on form) Decision", "RequestedMD", "RqstdConsultType", "RqstdConsult", "(on form) Track", "FormType", "Patient Name", "DateEDC", "BookingInstruction", "BookingPlan", "CRKey", "Decision", "Track"}, {"First Name", "Surname", "ReferringDoctor", "Referral Date", "Referral Urgency", "Referral Reason", "Referral Details", "Pat Md", "Status", "StatusComments", "Next Appt Date", "Next Appt Prvdr", "Extract Date", "Health Number", "CR Occurrence Date", "(on form) Decision", "RequestedMD", "RqstdConsultType", "RqstdConsult", "(on form) Track", "FormType", "Patient Name", "DateEDC", "BookingInstruction", "BookingPlan", "CRKey", "Decision", "Track"}),
#"Added FutureReferralDates" = Table.AddColumn(#"Expanded CR (Step1)", "FutureReferralDates", each List.Select([ReferralDateList],each _ > [Referral Date])),
#"Reordered Columns" = Table.ReorderColumns(#"Added FutureReferralDates",{"Patient #", "First Name", "Surname", "ReferringDoctor", "Referral Urgency", "Referral Reason", "Referral Details", "Pat Md", "Status", "StatusComments", "Next Appt Date", "Next Appt Prvdr", "Extract Date", "Health Number", "CR Occurrence Date", "(on form) Decision", "RequestedMD", "RqstdConsultType", "RqstdConsult", "(on form) Track", "FormType", "Patient Name", "DateEDC", "BookingInstruction", "BookingPlan", "CRKey", "Decision", "Track", "ReferralDateList", "Referral Date", "FutureReferralDates"})
in
#"Reordered Columns"