Forum Discussion
power query formula needed
am trying to find matches on multiple rows and then concatonate to only see one record returned when all criteria is the same using 1 table.
1st I have 3 columns, the 1st column is a name, the 2nd and 3d columns are what I need to see if they match on several rows.
2nd. I then need to see how to compare the results are on all locations.
3rd. If the name and 2nd and 3rd columns match and are on all locations, I only want 1 record returned.
Or
If I have to do in 2 queries
1. what is best way to find maches for Name, Data 1 and Data 2 columns on multiple rows
2. How to then complare 1st results to Location
Example:
Column 1 = Location
Column 2 - name
Column 3 & Collumn 4 data is either 0 or 1
In my example for Name 3 I only want to see 1 record since everything matches
| Location | Name | Data 1 | Data 2 |
| Loc 1 | Name 1 | 1 | 0 |
| Loc 2 | Name 1 | 1 | 0 |
| Loc 3 | Name 1 | 0 | 0 |
| Loc 4 | Name 1 | 0 | 0 |
| Loc 5 | Name 1 | 1 | 0 |
| Loc 6 | Name 1 | 1 | 1 |
| Loc 1 | Name 2 | 1 | 0 |
| Loc 2 | Name 2 | 1 | 0 |
| Loc 3 | Name 2 | 0 | 0 |
| Loc 4 | Name 2 | 0 | 0 |
| Loc 5 | Name 2 | 1 | 0 |
| Loc 6 | Name 2 | 1 | 1 |
| Loc 1 | Name 3 | 1 | 1 |
| Loc 2 | Name 3 | 1 | 1 |
| Loc 3 | Name 3 | 1 | 1 |
| Loc 4 | Name 3 | 1 | 1 |
| Loc 5 | Name 3 | 1 | 1 |
| Loc 6 | Name 3 | 1 | 1 |
3 Replies
- lbendlin
Super User
Your criteria for Data1 and Data2 are unclear. Please explain the logic again.
Data 1 and Data 2 columns are results for Name column. Data 1 and Data 2 columns will either have 1 or 0.
There may be up to 15 rows for the same data in the Name column. The Data 1 and Data 2 columns for the 15 rows may be the same or have different values. If Data 1 and Data 2 are the same for a number of rows I want to know if they match for the value in the Name column.
Example:
Name Data 1 Data 2 Name 1 1 0 Name 1 1 0 Name 1 0 0 Name 1 0 0 Name 1 1 0 Name 1 1 1 Name 1 1 1 Name 1 1 1 Name 1 1 1 Name 1 0 1 Name 2 1 0 Name 2 1 0 Name 2 0 0 Name 2 0 0 Name 2 1 0 Name 2 1 1 Name 3 1 1 Name 3 1 1 Name 3 1 1 Name 3 1 1 Name 3 1 1 Name 3 1 1 Name 3 1 1 Name 3 1 1 Name 3 1 1 Name 3 1 1 
v-zhangtiCommunity Support
Hi, lsustaita
You can try the following methods.
Judgment = IF([Data 1]=[Data 2],1,0)Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
