Forum Discussion
Filtering a column in Power Query (M language)
Hi, bonjourposte
Before you group dates and find the longest date, make sure that the table contains only the rows that correspond to the internal checks. This may require the addition of a step to filter the table based on the check type column that differentiates between internal and external inspections. For example, if such a column exists and is named , the filtering step would look like this:
FilteredInternal = Table.SelectRows(dbo_PROPINSP, each [InspectionType] = "Internal")
Group and find maximum dates: After filtering, apply grouping and maximum date extraction on the table:
= Table.Group(FilteredInternal, {"PROP_CODE"}, {{"LatestInternalInspection", each Table.Max(_, "ASOF_DTE"), type table}})
This formula assumes that the column is the one that contains the check date and is your property identifier.How to Get Your Question Answered Quickly
Best Regards
Yongkang Hua
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, bonjourposte
Before you group dates and find the longest date, make sure that the table contains only the rows that correspond to the internal checks. This may require the addition of a step to filter the table based on the check type column that differentiates between internal and external inspections. For example, if such a column exists and is named , the filtering step would look like this:
FilteredInternal = Table.SelectRows(dbo_PROPINSP, each [InspectionType] = "Internal")
Group and find maximum dates: After filtering, apply grouping and maximum date extraction on the table:
= Table.Group(FilteredInternal, {"PROP_CODE"}, {{"LatestInternalInspection", each Table.Max(_, "ASOF_DTE"), type table}})
This formula assumes that the column is the one that contains the check date and is your property identifier.
How to Get Your Question Answered Quickly
Best Regards
Yongkang Hua
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks, I didn't see how that could work because I didn't want to accidentally delete an inspection if the latest inspection was external, but the key is to filter the list BEFORE you find the MAX date. It worked, thanks.