Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
v-yuta-msft
Community Support
Community Support

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

 

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

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 

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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
Community Support
Community Support

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.