Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
AnandRanga
Helper III
Helper III

is it possible to subtract two text value set in power bi ?

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.

1 ACCEPTED SOLUTION

@AnandRanga

 

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

 

notincolumnb.png




Lima - Peru

View solution in original post

7 REPLIES 7
MarcelBeug
Community Champion
Community Champion

In the Query Editor you can also use the following code, where Data is your data table

= List.RemoveMatchingItems(Data[column2],Data[column1])
Specializing in Power Query Formula Language (M)
KHorseman
Community Champion
Community Champion

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.

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Sean
Community Champion
Community Champion

@AnandRanga

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! Smiley Happy

 

EDIT: But again here's @MarcelBeug showing off with his 1 line solutions! Smiley Very Happy

@Sean

@MarcelBeug

@KHorseman

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@KHorseman

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.

@AnandRanga

 

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

 

notincolumnb.png




Lima - Peru

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.