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

Match a pair of values from one column based on multiple other columns

I have the following structure:

DateObject IDType
1/1/2019100023A
1/1/2019100025A
1/1/2019100023B
1/2/2019100065A
1/2/2019100098B
1/2/2019100098A
1/3/2019100052A
1/3/2019100052A
1/3/2019100058A

My goal is to have Power Query flag a Match whenever both type A and B share the same Object ID and Date.

 

I am interested to know if the A/B match happens multiple times for the same Date/Object ID combo. I do not need to know if only A or only B mathc multiple Date/Object ID combos. I've tried creating many variations of GroupBy tables and Calculated Columns but have not found a solution. Can someone please help with this? Thank you!

1 ACCEPTED SOLUTION
Nathaniel_C
Super User
Super User

Hi @OrthoData ,

Created a copy of the table, filtered 1 for A and 1 for B, Merged them based on the first two columns and then expanded for the last column. From there you can throw a flag on the condition. Or you can filter out null, and end up with the values that match.

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel
match.PNG

 





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

Proud to be a Super User!




View solution in original post

3 REPLIES 3
Nathaniel_C
Super User
Super User

Hi @OrthoData ,

Created a copy of the table, filtered 1 for A and 1 for B, Merged them based on the first two columns and then expanded for the last column. From there you can throw a flag on the condition. Or you can filter out null, and end up with the values that match.

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel
match.PNG

 





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

Proud to be a Super User!




View solution in original post

@Nathaniel_C Thank you. What a great, simple solution! 

Hi @OrthoData ,
You are welcome! A lot of my knowledge in Power Query comes from reading M is for (Data) Monkey by @KenPuls .
Nathaniel





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

Proud to be a Super User!




Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Top Kudoed Authors