cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Chavas
Helper II
Helper II

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

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 daysLast 30 days
Person 1     13
Person 2    48
Person 3   36



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

1 REPLY 1
Nathaniel_C
Super User
Super User

Hi @Chavas ,

Started with pasting your table into a table in Power Query

Clean.PNG

 

Then cleaned up the columns to get this:
Clean1.PNG

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it Out!

Join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!