how to do the opposite of merge or combine?

jbaich

Board Regular
Joined
Nov 2, 2011
Messages
139
Hi all, I have 3 queries, one returning a large list of "all" records and the other two are different subsets of this data... what I'm trying to do in Power Query is reference the "All Records" query and then remove or filter out any records that are found in the other two tables... resulting in a table containing only records not returned or part of either of other two queries... like remove duplicates but without leaving a record if it was a duplicate...?

There is a unique product code column that I can use to identify records by... in regular excel I probably would have just done a countif and then filter out anything beside "0", but I'm trying to learn how to use power query and at least enough m code to be quasi useful, however I haven't been able to find any info on the web regarding this scenario... probably using the wrong search terms, but I can't think of what else i should try...

Any advice would be very much appreciated.

Thanks,
joe
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
You can merge the queries with join kind left anti. The following code works if both subsets (Subset1 and Subset2) have the same name of the key column ("Code").
Alternatively you can merge with each subset separately.

Code:
let
    Source = Table.NestedJoin(AllRecords,{"Code"},Subset1&Subset2,{"Code"},"Subsets",JoinKind.LeftAnti),
    #"Removed Columns" = Table.RemoveColumns(Source,{"Subsets"})
in
    #"Removed Columns"
 
Upvote 0
Thanks Marcel!!!

Took me a few tries but i got it! One other question maybe? No matter what i do, the formatting I apply to columns in the query editor do not transfer through to the table... doesn't seem to flow from the data model either if i apply formatting there... any ideas?

Thanks,
joe
 
Upvote 0
I have a similar question. I see how to do it in M with a query. How would one do it in DAX, perhaps with GENERATE?, i.e.,Rows from A not in B.
 
Last edited:
Upvote 0
You're welcome.

With regard to the formatting, there may be a few points of attention:
1. During some specific query steps, previously formatting may get lost, so you should make sure that the data is properly typed at the end of each query.
2. If a table column has a specific data type, it may well be that the column contains data of a different type.
E.g. this is perfectly legal in Power Query, but it won't load into the data model: = #table(type table[Number = Int64.Type],List.Zip({{"A".."C"}})).
3. Durations and datetimezone values are not known outside Power Query.
4. Only data types are defined in Power Query; specific formatting is done in Excel / the data model.

Without more detailed information from your side, I can't tell you what's the matter in your specific situation.

@cnest8gr: I'm not too familiair with DAX.
My suggestion would be to raise your own topic: a topic with 0 replies gets more attention from other helpers, than a new question hidden in a topic that already has replies.
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top