How to count repetitive values in a cell for a whole table

[Chavas]

Hello I am facing an issue on how to get a measure.

I have a table called tasks.

Each task row has a cell called Details which is fulfilled like this

************************************ 13-Aug-20 1:27 AM @ Person1: ************************************
lorem
************************************ 12-Aug-20 4:44 PM @ Person2 ************************************

ipsum

************************************ 12-Aug-20 6:00 AM @ Person3 ************************************

lorem

************************************ 10-Aug-20 13:27 AM @ Person2 ************************************
ipsum


And I will need to count the times that a Person has written for the last: 7 and 30 days for the whole table.
So I can produce a table like this:
 

	Last 7 days	Last 30 days
Person 1	1	3
Person 2	4	8
Person 3	3	6

Splitting by delimiter is not a way as far as n columns may appear so I have no idea on how to filter.

I am thinking and I have no clue on how to do it. Any advice will be fantastic

Thanks in advance

[Nathaniel_C]

Hi @Chavas ,

Started with pasting your table into a table in Power Query

Then cleaned up the columns to get this:

Is this what you are looking to do? I split columns with delimiters, and replaced values.  This is just one way to do it.

If you wish to see my steps go to Power Query, start a blank query, and paste this into the Advanced Editor.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W0iICKBga6zqWpusaGSgYWhmZKzj6KjgoBKQWFefnGVopEGOCUqxOtFJOflFqLphFnKVGMEtNrExMFAIQlhoRbyeYyCwoLs1FcEm03czKwADZy8Yk2o7wNgm2G8AD3BgtxEnwPNTfsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByDelimiter("@", QuoteStyle.Csv), {"Column1.1", "Column1.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "Column1.1", Splitter.SplitTextByEachDelimiter({"*"}, QuoteStyle.Csv, true), {"Column1.1.1", "Column1.1.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Column1.1.1", type text}, {"Column1.1.2", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type2","*","",Replacer.ReplaceText,{"Column1.2"}),
    #"Filtered Rows" = Table.SelectRows(#"Replaced Value", each ([Column1.2] <> null)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Column1.1.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column1.1.2", "Date"}, {"Column1.2", "Name"}})
in
    #"Renamed Columns"

It will create a table, and you can follow my steps.

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel