Creating multiple columns,based on count of Each item Occurrence in each Column.

[Anonymous]

Hi All,

I am just a beginner to Power Bi. I am need of help. And any idea would be appreciated.

1. I, need to create multiple columns based on the count of occurrences of each type in the columns.    

2. Then multiple conditional statements. As an example :

 Columns :

Email                      Mobile                PostCode         Surname

abc@                      12345                 e13                    SSS

bcc@                      12345                 r13                     DDD

ccd@                       2345                  e13                    EEE

abc@                      234                     e13                    FFF 

ccd@                      2345                   e13                    SSS

New Output Coumns:

Email   Count           Mobile Count    PostCode Count   Surname Count

abc@       2               12345     3          e13              4      SSS         2

bcc@       1               2345       2          r13               1      DDD       1

ccd@       2               234         1                                      EEE         1

                                                                                        FFF         1

And then : 

IF EmailCount = MobileCount and  EmailCount = PostCodeCount and EmailCount = SurnameCount Then Full Match

If EmailCount = MobileCount  AND EmailCount = PostCodeCount  AND EmailCount <> SurnameCount Then Strong Match

else WeakMatch.

Obviously, this needs to be coded correctly.

Any suggestion or idea will be greatly appreciated.

Thanks

[Anonymous]

Hi All,

I am in an urgent need of creating a report based on these criteria :

So, If SurName = PostCode = Post Email = ContactNo  Then Full Match

     If SurName = PostCode  and (SurName = ContactNo  or SurName = Email) Then Strong

     If SurName = PostCode  or  or SurName = ContactNo  or SurName = Email Then Weak

Is it possible at all?  Any help will be highly appreciated. Thanks in Advance 

SurName	PostCode	Email	ContactNo	Report
Bell	BT37 0FR	aaron.bell@agnews.co.uk	7891708176	Full Match
Bell	BT37 0FR	aaron.bell@agnews.co.uk	7891708176	Full Match
Bell	EH4 7RW	aaron.bell@arnoldclark.com	7802348775	Strong
Bell	EH4 7RW	aaron.bell@arnoldclark.com	7803355587	Strong
Bell	UH4 7RW	aaron.bell@arnoldclark.com	123132131	Weak
Bell	PH4 7RW	aaron.bell@arnoldclark.com	936999999	Weak

[Greg_Deckler]

Maybe, but I do not understand how in your example below that

"Bell" = "BT37 0FR" = "aaron.bell@agnews.co.uk" = 7891708176

Because the normal rules of "equals" would preclude that conclusion.

[Anonymous]

Thanks, Greg. I was away. What I meant was if the rows are matched for all the columns then 'Full Match', if rows are matched in 3 columns among 4 columns then Strong.  And if rows are matched in 2 columns among 4 columns then Weak. ETC.

[v-yuta-msft · Accepted solution]

Hi RizzLearn,

Though you can use select columns from tableA into tableB in sql, you can also achieve your requirement in powerquery but a little complex.

Duplicate the table 4 times, in each duplicated table, click query editor-> Group by email, mobile, postcode and surname and add index column for each-> merge the four tables based on index column. 

 

Then click custom column input code like below:

 = if ([Count_Of_Email] = [Count_Of_Mobile]) and ([Count_Of_Email] = [Count_Of_PostCode]) and ([Count_Of_Email] = [Count_Of_Surname]) then "Full Match" else if ([Count_Of_Email] = [Count_Of_Mobile]) and ([Count_Of_Email] = [Count_Of_PostCode]) and ([Count_Of_Email] <> [Count_Of_Surname]) then "Strong Match" else "WeakMatch"

 

The final result is like below:

 

Regards,

Jimmy Tao