Forum Discussion
To Keep Duplicate Row but Delete Value from One
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.
Hi biengineer ,
if you use " try [Merged]{0} otherwise null " it should work.try this function
= Table.FuzzyNestedJoin(Table1,{Col1 , Col2, Col3},Table2,{Col1 , Col2, Col3},"n",JoinKind.LeftOuter,[Threshold=1,NumberOfMatches=1])
7 Replies
try this function
= Table.FuzzyNestedJoin(Table1,{Col1 , Col2, Col3},Table2,{Col1 , Col2, Col3},"n",JoinKind.LeftOuter,[Threshold=1,NumberOfMatches=1])
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))
Hi biengineer ,
if you use " try [Merged]{0} otherwise null " it should work.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.
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.
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.Very closed it worked. how to handle the error where there is no match. Can i replace with zero during expansion.
Best regards
