Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
97 | |
69 | |
46 | |
39 | |
33 |
User | Count |
---|---|
158 | |
102 | |
60 | |
43 | |
40 |