Forum Discussion
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
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
4 Replies
v-yuta-msftCommunity 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.
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
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 [email protected] 7891708176 Full Match Bell BT37 0FR [email protected] 7891708176 Full Match Bell EH4 7RW [email protected] 7802348775 Strong Bell EH4 7RW [email protected] 7803355587 Strong Bell UH4 7RW [email protected] 123132131 Weak Bell PH4 7RW [email protected] 936999999 Weak
- Greg_Deckler
Community Champion
Maybe, but I do not understand how in your example below that
"Bell" = "BT37 0FR" = "[email protected]" = 7891708176
Because the normal rules of "equals" would preclude that conclusion.
