Replace duplicates with null by power query

I have two tables and after merging them through power query, some of the data in cells duplicate. I need to replace these duplicates with null so that the final output table is clean. if anyone can suggest steps in power query to get the outcome.

Originally Posted by oeldere

Not in power query.

you can use the formula for

O2 =IF(COUNTIF($J$2:$J2,$J2)>1,"delete","")

thanks for your response, I am looking for solution though power query if possible.

Originally Posted by Haluk

@siddiqsh;

It seems that, you are making a "Left Join" on ID's of the 2 tables .... and you are getting the expected results.

So, what is your actual goal in this process?

Is something like shown in the below image will be OK for you?

thanks for your response. No it will not help. the results which I get by merging tables (attached excel sheet) is the desired one. only problem is repetitive values. I prefer solution through power query.

@siddiqsh;

The image I posted in my message #3 is the result of a "Microsoft Query" and your "PQ table" can also be prepared in the same manner.

But; as the results are not unique, the output is not the way you want.

Originally Posted by Bo_Ry

Please try

let
    Source = Table.NestedJoin(Query1, {"ID"}, Query2, {"ID"}, "Query2", JoinKind.LeftOuter),
    Custom1 = Table.TransformColumns(Source,{"Query2", each Table.AddIndexColumn(_,"i")} ),
    #"Expanded Query2" = Table.ExpandTableColumn(Custom1, "Query2", {"AITS", "Description2", "i"}, {"AITS", "Description2", "i"}),
    AddedID = Table.AddColumn(#"Expanded Query2", "a", each if [i] = 0 or [i] = null then [[ID],[Description1],[Status1]] else null),
    #"Removed Columns" = Table.RemoveColumns(AddedID,{"ID", "Description1", "Status1"}),
    #"Expanded a" = Table.ExpandRecordColumn(#"Removed Columns", "a", {"ID", "Description1", "Status1"}, {"ID", "Description1", "Status1"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded a",{"ID", "Description1", "Status1", "AITS", "Description2"})
in
    #"Removed Other Columns"

IMHO leave null data is really bad idea for database.

this works well. thanks for your solution.