Forum Discussion
How to Count rows that contain certain text string and current cell value
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!!
| 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 |
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"
1 Reply
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"