Mergae two queries upon non duplicates of a certain column

[HusseinKhalil]

Greetings,

i have two queries in Bi desktop, with a column shared between both, this column has the PO no. for a selling products, i want to append Query A with Query B, but first i need to check if there is any PO no. in Query B that already exist in Query A, it should be removed first.

[jstorm · Accepted solution]

To remove duplicates in an organized fashion, you need to rebuffer the table to change the way the records are sorted in the database.

Add a column 'Table' to each table with every row set to 'A' or 'B' respectively.

Append the queries.

Use M Query Table.Buffer() to rebuffer the table sorted by 'Table'

Remove duplicates based on column 'Table'

This process will make Power BI remove duplicates in the sorted order of 'Table'.  It should only removes duplicates with Table = 'B'.

Please refer to this article ->

https://exceleratorbi.com.au/remove-duplicates-keep-last-record-power-query/

[dax]

Hi  @HusseinKhalil , 

You could try to use merge (LeftAnti) then append. Try below M code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUoEYkNTpVidaCUjIDMJxDUAc42BzGQgNgJyYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [No = _t, name = _t, amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"No", Int64.Type}, {"name", type text}, {"amount", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"No"}, B, {"No"}, "B", JoinKind.LeftAnti),
    #"Removed Columns" = Table.RemoveColumns(#"Merged Queries",{"B"}),
    #"Appended Query" = Table.Combine({#"Removed Columns", B})
in
    #"Appended Query"

This is Table B

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlLSUUoBYkMDpVidaCVjIDMNiI0gXFMgMx2IjYHcWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [No = _t, name = _t, amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"No", Int64.Type}, {"name", type text}, {"amount", Int64.Type}})
in
    #"Changed Type"

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[HusseinKhalil]

is there anyway around to simply add a new flag column in the second query to check if the PO number exists in first query or not, so that i can filter out the results of matching cases, then i can make append the two queries. 

[zaza]

In the second query, merge the query with the first one based on the PO no., (Left Join). Then filter out the values that have successfuly returned rows.

Now you can append them without worrying about duplicates.