How to Count rows that contain certain text string and current cell value

[Bertenvanloover]

Hi, I could use some help finding a way to do the manipulation below in Power Query:
I want to create a custom column "Times_Edited" that counts the total times a Filename got "Change Editing Required" or "Editing Required".

I tried this one, but I presume that my [Filename]=[Filename] is the culprit.
= Table.AddColumn(#"Replaced Value1", "Times_Edited", each Table.RowCount(Table.FromRecords({[TransitionName] = "Change Editing Required" || [TransitionName] = "Editing Required" ,[Filename]=[Filename]})))

Purpose: this table shows a history log of changes to the status of Documents. This column would be used as a metric on how often a file is send back for editing. (potentially a DAX procedure is a better choice in comparison to a separate column?)

Thanks!!

Related to:
https://community.powerbi.com/t5/Power-Query/How-to-find-text-from-list-in-cell-and-return-that-text...

https://community.powerbi.com/t5/Power-Query/Find-Text-from-List-in-Rows-with-same-ID-as-current-row... 

Filename	TransitionName	SubmittedDate	Times_Edited
1	Send for Approval	22/02/2023 11:00	1
1	Change Editing Required	22/02/2023 11:01	1
1	Approved	22/02/2023 11:02	1
2	Send for Approval	22/02/2023 11:05	0
2	Approved	22/02/2023 11:06	0
3	Send for Approval	22/02/2023 11:00	3
3	Editing Required	22/02/2023 11:02	3
3	Send for Approval	22/02/2023 11:03	3
3	Change Editing Required	22/02/2023 11:05	3
3	Approved	22/02/2023 11:10	3
3	Change Editing Required	11/09/2023 09:11	3

[6677028 · Accepted solution]

Send You.

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Removed Columns" = Table.RemoveColumns(Source,{"Times_Edited"}),
#"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each if[TransitionName] ="Change Editing Required" or [TransitionName]= "Editing Required" then 1 else 0),
#"Grouped Rows" = Table.Group(#"Added Custom", {"Filename"}, {{"Count", each List.Sum([Custom]), type number}}),
#"Merged Queries" = Table.NestedJoin(#"Removed Columns", {"Filename"}, #"Grouped Rows", {"Filename"}, "Removed Columns", JoinKind.FullOuter),
#"Expanded Removed Columns" = Table.ExpandTableColumn(#"Merged Queries", "Removed Columns", {"Filename", "Count"}, {"Filename.1", "Count"}),
#"Removed Columns1" = Table.RemoveColumns(#"Expanded Removed Columns",{"Filename.1"})
in
#"Removed Columns1"