To Keep Duplicate Row but Delete Value from One

[biengineer]

Dear All,

I have used merge Leftouter base one Col1 , Col2, Col3, I have duplicates in  this concatenation Col1-Col2-Col3, I want to keep the duplicate row but value in only one row resulted from Merge.

Best Regards. 

[ImkeF · Accepted solution]

Hi @biengineer ,
if you use " try [Merged]{0} otherwise null " it should work.

[wdx223_Daniel · Accepted solution]

try this function

= Table.FuzzyNestedJoin(Table1,{Col1 , Col2, Col3},Table2,{Col1 , Col2, Col3},"n",JoinKind.LeftOuter,[Threshold=1,NumberOfMatches=1])

[biengineer]

Sir, I got error with data that it supports only text column, I converted date to text in both tables. And It has worked great with leftouter. 

Thanks for that.

I want to do it with fullouter, and import all the rows from the Table 2 while keeping all rows of Table 1. After this step wish to apply this step

=Table.FromRecords(List.TransformMany(Table.ToRecords(#"Merged Packing"),each Table.ToRecords([Packing]),(x,y)=>Record.RemoveFields(x,{"Packing"})&y))

[ImkeF · Accepted solution]

Hi @biengineer ,
if you use " try [Merged]{0} otherwise null " it should work.

[biengineer]

Sir, I thought it worked, but still shows the duplicate value. 

= Table.AddColumn(#"Merged Packing", "Custom", each try [Packing]{0} otherwise null)

= Table.ExpandRecordColumn(#"Added Custom7", "Custom", {"PackingQty"}, {"PackingQty"})

Should have shown only 1229.

Best Regards.

[ImkeF]

Hi @biengineer ,

then my understanding of your requirement was wrong.
It might be that you need a right outer join instead, but still not sure about your exact requirements.
Also check out @wdx223_Daniel s solution. It does the job with just one join command.

[biengineer]

Very closed it worked. how to handle the error where there is no match. Can i replace with zero during expansion.

Best regards 

[ImkeF]

Hi @biengineer ,
don't expand the column from the merge (imaging it is called "Merged"). Instead add a column that references that column and picks the first row from it: [Merged]{0}. Now expand this new column.
That will make sure that only the first row of the found matches will be returned and you will keep your original number of rows.