Forum Discussion
Filtering a column in Power Query (M language)
I'm following this Curbal tutorial, but i need it to have an extra filtering step.
https://www.youtube.com/watch?v=QaodJFeX49k&t=1s
We have a table of dates denoting when a property was last inspected. Most of them were inspected by internal inspectors, but some of them were inspected by external companies. We want to filter for the LATEST date of inspection that was inspected INTERNALLY.
At 0:54, she starts grouping by the ID and then expanding the table until it just includes the MAX date. Her formula is for her example, so I'll write what I'm using:
= Table.Group(dbo_PROPINSP, {"PROP_CODE"}, {{"Count", each Table.Max(_, "ASOF_DTE")}})
How do I now say that I just want the latest INTERNAL inspection?
P.S. This is my grouped table:
Thank-you in advance.
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.
4 Replies
Hi, if you want only inspection type = INTERNAL, you can simply apply filter on that column to keep INTERNAL values and then do group by.
Yes, filter for internal first, then find the MAX. Thanks so much.
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.
