Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have a column1 in which i have 15 values, those are employees name. In another column2 I have name of all the employees. I want to get those values those are present in column2 but not in column1.
column1 column2
a p
b q
c c
b
a
so the coutput should be p and q.
basically i want column1 - column2.
Is there any function for that?
Thanks for your time.
Solved! Go to Solution.
i have a different way to solve this question:
1 create a measure
notincolumnb = VAR col2 = VALUES ( Table1[Column2] ) RETURN IF ( HASONEVALUE ( Table1[Column2] ), CALCULATE ( COUNTROWS ( Table1 ), FILTER ( ALL ( Table1 ), Table1[Column1] = col2 ) ) + 0 )
2. Use a table visual with a visual level filter
In the Query Editor you can also use the following code, where Data is your data table
= List.RemoveMatchingItems(Data[column2],Data[column1])
In the query editor you can use Table.Join() and do a right anti-join. Here's my query code where I manually entered your two example columns as the first step:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUSpQitWJVkoCsgrBrGQgKxnMAjKSYIxEpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}), #"Anti" = Table.Join( Table.SelectColumns(#"Changed Type", {"Column1"}), "Column1", Table.SelectColumns(#"Changed Type", "Column2"), "Column2", JoinKind.RightAnti) in #"Anti"
The last step named #"Anti" is the only important part here. The first argument in Table.Join is a table to use as an input. I want all of Column1 from the original table and nothing else, so I used Table.SelectColumns to give me a one-column table. The second argument is the first key for the join, that is the column that I want to compare to my second table. The third argument is a table containing only column2, and fourth argument is the column from that second table that I want to compare to column1 from my first table. Finally I tell it what kind of join to do. A right anti join will give me all of the rows from the second table that are not present in the first.
In DAX...I haven't figured it out yet. It will no doubt involve SUMMARIZE.
Proud to be a Super User!
If you are unfamiliar with the joins - Look here
http://www.excelguru.ca/blog/2015/12/16/merge-tables-using-outer-joins-in-power-query/
and also here
http://www.excelguru.ca/blog/2015/12/23/merge-tables-using-inner-and-anti-joins-in-power-query/
Good Luck!
EDIT: But again here's @MarcelBeug showing off with his 1 line solutions!
I am trying with IN operator like 'Product'[Color] IN { "Red", "Blue", "Black"} but I need NOT IN, can someone help me with this. If there is NOT IN available, I am done with it.
https://msdn.microsoft.com/en-us/library/ee634237.aspx
Thanks a lot.
@AnandRangaI'm confused. In your example you had two columns in a table that you wanted to compare. Now you say you have a manually written list of values? Which is it? Those are two very different scenarios.
Proud to be a Super User!
My expression is:
defaulter = IF(TIMESHEETANALYSISCUBE[WORKER NAME] IN {TIMESHEETANALYSISCUBE[WorkerNameDEF]},TIMESHEETANALYSISCUBE[WORKER NAME])
But I want to use NOT IN.
Thanks for your time.
i have a different way to solve this question:
1 create a measure
notincolumnb = VAR col2 = VALUES ( Table1[Column2] ) RETURN IF ( HASONEVALUE ( Table1[Column2] ), CALCULATE ( COUNTROWS ( Table1 ), FILTER ( ALL ( Table1 ), Table1[Column1] = col2 ) ) + 0 )
2. Use a table visual with a visual level filter
User | Count |
---|---|
128 | |
108 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |