How to count the occurence of a value from a column in a coma separated field in another column?

[Betty888]

Dear all, 

I'm trying to count the number of occurence of a value from column "Date" in a list of dates with a semicolon separator in another column "Column B" as below : 

Date	Column B
date1	date1; date2;date3;date4;date5;date6; ,,,,,
date2	date1; date2;date3;date4;date5;date6; ,,,,,
date3	date1; date2;date3;date4;date5;date6; ,,,,,
date4	date1; date2;date3;date4;date5;date6; ,,,,,

Can you help me with any ideas, please 🙏

Thank you in advanace for your help ! 

Regards, 

[Moetazzahran · Accepted solution]

Hello @Betty888, 

I created this calculated column which uses two main variables
The first variable 

OccurrenceLength is the difference between the length of the text of [ColumnB] and the length of the same text after it replaces the variable in [Date] with a blank().

This difference in length will then be divided by the length of the variable we replaced within the string to get the number of occurance of the variable you are searching for within the ColumnB 

Please let me know if this works, and accept it as a solution if it does. You are kudo is also much appreciated.

[Anonymous · Accepted solution]

Hi, @Betty888 

You can create a custom column and use the following M expression in PowerQuery. This formula splits the text in "Column B" by semicolon and counts the number of elements after splitting. It then adds one to the count result. If there is no data after the semicolon, we need to subtract one from it.

= List.Count(Text.Split([Column B], ";"))-1

Here is my preview:

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Betty888]

Thanks a lot ! 

In fact, your suggested solution counts the number of items, but what I need is to count the number of occurence of value from column "Date" in "Column B".

Regards,

[Moetazzahran · Accepted solution]

Hello @Betty888, 

I created this calculated column which uses two main variables
The first variable 

OccurrenceLength is the difference between the length of the text of [ColumnB] and the length of the same text after it replaces the variable in [Date] with a blank().

This difference in length will then be divided by the length of the variable we replaced within the string to get the number of occurance of the variable you are searching for within the ColumnB 

Please let me know if this works, and accept it as a solution if it does. You are kudo is also much appreciated.

[Betty888]

Many thanks ! 
it works for my problem, many many thanks !