Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
the data is similar to the example below
Field1 Field2
A 123456
A 125467
B 123456
B 3214
the value in Field2 must only occur once in the table, and the record with Field1 = A must be kept. Must end up with:
Field1 Field2
A 123456
A 125467
B 3214
Solved! Go to Solution.
First step I'd do would be to make a column that highlights whether your Field2 is duplicated, this explains it better than I can:
https://www.excelguru.ca/blog/2015/12/09/identify-duplicates-using-power-query/
Once you've got that, you can concatenate it with Field1, and filter out anything that has a value B1, B2 etc
Found a solution:
Create table1 with Field2 values where Field1 = A
Create table2 with Field1 and Field2 values where Field1 = B
Create new table3 using merge and do right antijoin of table1 and table2, resulting in table3 having only table2 records where Field2 does not exist in table1
remove extra columns from table3
append table3 to table1
this seems to work, but it would be good to know if there is a more elegant method for more complex datasets
First step I'd do would be to make a column that highlights whether your Field2 is duplicated, this explains it better than I can:
https://www.excelguru.ca/blog/2015/12/09/identify-duplicates-using-power-query/
Once you've got that, you can concatenate it with Field1, and filter out anything that has a value B1, B2 etc
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |