Search for specific text and return one result

[WYSE595]

Hi, I am a relative newbie and am trying to do something that I can't find the answer to anywhere and am hoping you can help.

I have a table which contains a list of different Process Areas which contains all the meetings they have scheduled.  The requirement is to ensure they have at least one meeting scheduled in each half of the year.  My problem is, some areas will have more than one meeting scheduled.    

What I would like is a measure or a column that reports on a search of the list and reports on only one of each for each process area.   Below is a representation of the sort of thing I have.  I would like to find a way of just reporting on one H1 and one H2 from each process area.

Process Title	Meeting Date	HalfYear
Process 1	22/01/2022	H1
Process 1	13/03/2022	H1
Process 1	19/09/2022	H2
Process 1	20/11/2022	H2
Process 2	18/01/2022	H1
Process 2	16/09/2022	H2
Process 3	16/03/2022	H1
Process 3	28/09/2022	H2
Process 3	10/12/2022	H2

I hope I am making sense

[v-yalanwu-msft]

Hi, @WYSE595 ;

You could create a custom column in power query .

= Table.AddColumn(#"Removed Columns", "Custom", each if Date.Month([Meeting Date] ) <=6 then "H1" else "H2")

 Or you could create a measure in desktop.

= Table.AddColumn(#"Removed Columns", "Custom", each if Date.Month([Meeting Date] ) <=6 then "H1" else "H2")

The final show:

If i understand error, can you share more logic and the result you want to ouput?

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[BA_Pete]

@v-jingzhang 

I have removed the solution recently applied to this post. This clearly does not solve the OP's (albeit vague) question.

OP already has the half-year column, they appear to require that the table is adjusted to show where either at least one meeting has taken place during the half-year, or none.

Pete

[BA_Pete]

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