cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RizzLearn Frequent Visitor
Frequent Visitor

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

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

 

                                                           

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

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

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. 

3.PNG4.PNG11.PNG22.PNGCapture.PNG 

 

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"

55.PNG 

 

The final result is like below:

66.PNG 

 

Regards,

Jimmy Tao

 

4 REPLIES 4
Community Support Team
Community Support Team

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

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. 

3.PNG4.PNG11.PNG22.PNGCapture.PNG 

 

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"

55.PNG 

 

The final result is like below:

66.PNG 

 

Regards,

Jimmy Tao

 

Highlighted
RizzLearn Frequent Visitor
Frequent Visitor

Comparing text columns in Row level

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 

SurNamePostCodeEmailContactNoReport
BellBT37 0FRaaron.bell@agnews.co.uk7891708176Full Match
BellBT37 0FRaaron.bell@agnews.co.uk7891708176Full Match
BellEH4 7RWaaron.bell@arnoldclark.com7802348775Strong 
BellEH4 7RWaaron.bell@arnoldclark.com7803355587Strong 
BellUH4 7RWaaron.bell@arnoldclark.com123132131Weak 
BellPH4 7RWaaron.bell@arnoldclark.com936999999

Weak 

Super User
Super User

Re: Comparing text columns in Row level

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.


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

Proud to be a Datanaut!


RizzLearn Frequent Visitor
Frequent Visitor

Thanks, Greg. I was away. What I meant was if the rows ar...

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.