how to do the opposite of merge or combine?

[jbaich]

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

 

[MarcelBeug]

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.

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

 

[jbaich]

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

 

[cnestg8r]

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.

 

[MarcelBeug]

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.