Search for specific text and return one result

Hi WYSE595 ,

Not sure I fully understand your requirements here, but here's a few options for what I think you might want. These are all based on an original query called 'procMeeting' as follows:

// Call this procMeeting
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKT04tLlYwVNJRMjLSNzDUNzIwMgJyPAyVYnVQ5Q2N9Q2M8clb6htYwuWNMOSNDPQNDXHIg4QMLXDbD5Y3w22+MVQel/tA8kYWBPQD3WeEQ94EKAR0HKr+WAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Process Title" = _t, #"Meeting Date" = _t, HalfYear = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"Process Title", type text}, {"Meeting Date", type date}, {"HalfYear", type text}})
in
    chgTypes

1) Retain a single row per Process, per Half Year - will not show missed meetings

Multi-select (Ctrl + click) both [Process Title] and [Half year]

Go to the Home tab > Remove Rows (dropdown) > Remove Duplicates

This gives the following output:

2) Group and count meetings per Process, per Half Year - will not show missed meetings

Multi-select (Ctrl + click) both [Process Title] and [Half year]

Go to the Home tab > Group By, and add a count column as your aggregation:

This gives the following output:

3) Create base "expected meetings" table and merge actuals - WILL show missed meetings:

Create a new query with the following code, assuming that the original query is called 'procMeeting' as provided above:

let
    Source = Table.Distinct(Table.SelectColumns(procMeeting, "Process Title")),
    addHalfYearList = Table.AddColumn(Source, "Half Year", each {"H1", "H2"}),
    expandHalfYearList = Table.ExpandListColumn(addHalfYearList, "Half Year"),
    mergeOriginalQuery = Table.NestedJoin(expandHalfYearList, {"Process Title", "Half Year"}, procMeeting, {"Process Title", "HalfYear"}, "procMeeting", JoinKind.LeftOuter),
    expandDateCountNoNulls = Table.AggregateTableColumn(mergeOriginalQuery, "procMeeting", {{"Meeting Date", List.NonNullCount, "CountOfMeetings"}})
in
    expandDateCountNoNulls

The trick here is to merge on both [Process Title] and [Half Year], and expand the merge as a Count aggregation of the procMeeting[Meeting Date] column, then slightly adjust the generated code to count only non-null values, rather than a pure List.Count which s the default:

This gives the following output:

Pete