HOW?Calculated Column: if "this column" matches "this column" then display "this column" else "None"

[heathernicole]

I am trying to create a calculated column that looks at a couple of different columns - if they match, then display the number, if there's no match - display "No PO".

Here's an example: (there are actually thousands and thousands of rows for these two columns)

Type                          Number

Purchase Order          12345

Sales Order                 34567

Sales Order                12345

Sales Order                45769

Purchase Order          34567

Purchase Order          12567

What I need to get:

Type               Sales Order #     POs

Sales Order     34567                34567

Sales Order     12345                12345
Sales Order     45769                 "NO PO"

ANY assitance on this will be GREATLY appreciated!! 

The script I've created gets this error: DAX Comparison operations do not support comparing values of type Text.

Not very savvy with DAX yet...

Thanks in advance!!! 

[Sean · Accepted solution]

@ImkeF Thanks! This works great!

One thing I'll say if anyone is trying this don't rename a step "Filter Table" with a space

I don't know why it doesn't work with a space - use "FilteredTable" no space

Otherwise works great! Thanks again!

[Sean · Accepted solution]

You have to create a Relationship to the new Lookup Table you've just created...

[Sean]

POs = IF ( [Type] = "Purchase Order", [Number], "NO PO")

[heathernicole]

That didn't quite work... 😞 

If sales order number and the purchase order number MATCH - the display the number - if there's not a match the display NO PO

That's kind of the algorithm / code I'm shooting for...  this might be a better visual:

Sales Order    Matching PO

12345               12345

34567                NO PO

I'm trying to explain it as best as I can. So sorry for the confusion!

[Sean]

What are the columns in your data?

[heathernicole]

What do you mean exactly? 

[Sean]

I got it now. Actually the best way to do this is with a new Query which you write in M not DAX

I would tell you you're best bet is ImkeF she is the M expert

Because you are actually comparing rows

If I get another idea I'll post

[kcantor]

You should merge the tables within the query editor. Then pull from them as a single fact table. What it seems you are trying to do is a version of the excel vlookup. I have run into this in the past and used the merge function to overcome it. You base the merge on the column you are trying to match. If  you don't need all of the data, limit what you pull in.

[heathernicole]

I have tried merging them, that seemed to be the easiest way to go about it - but it didn't work the way I thought it would. I think part of what messes it up is the fact that the sales orders and purchase orders are in the same column. 

[Sean]

Hi @ImkeF we hope you can help us with this problem

We have 2 Columns Type and Number and want to create a 3rd Column called Pos

Any suggestions would be greatly appreciated!

@heathernicole feel free correct or add to my explanation

[heathernicole]

@Sean I was just getting ready to do the same! 😄 Thanks so much!! 

You pretty much got it right from what I see. 

Thanks again!

 @ImkeF, I believe you've helped me with another M problem. Because I don't know M still, it's hard to recognize when DAX won't do what I'm needing it to do.

Any help is greatly appreciated!!!

[ImkeF]

Thanks @Sean and @heathernicole

Lucky you - all the joys of discovering M's cool features still ahead 🙂

We do like @kcantor said: Creating filtered lookup-table with Purchase orders only. Then we merge this step (!) with the source step. So we have basically created our own lookup table within the query that we can simply adress via the stepname.

let
    Source = Table.PromoteHeaders(Table.FromColumns({ {"Type" ,"Purchase Order" ,"Sales Order" ,"Sales Order" ,"Sales Order" ,"Purchase Order" ,"Purchase Order"}, {"Number" ,"12345" ,"34567" ,"12345" ,"45769" ,"34567" ,"12567"} })),
    PurchaseOrders = Table.SelectRows(Source, each ([Type] = "Purchase Order")),
    MergeSteps = Table.NestedJoin(Source,{"Number"},PurchaseOrders,{"Number"},"NewColumn",JoinKind.LeftOuter),
    ExpandPONumber = Table.ExpandTableColumn(MergeSteps, "NewColumn", {"Number"}, {"Number.1"}),
    TextWhenNoPoNumberFound = Table.ReplaceValue(ExpandPONumber,null,"""no PO""",Replacer.ReplaceValue,{"Number.1"})
in
    TextWhenNoPoNumberFound

[Sean]

@ImkeF Thank you for the response!

So I created a small Excel file with the data @heathernicole used as a sample and loaded it... and I get this...

let
    Source = Excel.Workbook(File.Contents("C:\Users\Sean\Downloads\Compare Rows Test.xlsx"), null, true),
    TableCC_Table = Source{[Item="TableCC",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(TableCC_Table,{{"Type", type text}, {"Number", Int64.Type}})
in
    #"Changed Type"

Then I tried numerous ways to get your code to work in this file (which I'm sure it does)

What should I do??? That first line in your code Source = Table.PromoteHeaders... can't figure it out?

Thank You!!!

[ImkeF]

Ah, that's a real pain! You're using Internet Explorer? This browser is cutting off the code!!

(Since weeks!! - I've posted a ''complaint'' already, but nothing seems to happen - totally not understandable that MS is using forums software that doesn't comply with their own browser - or at least with some constellations of it.,..)

So here comes the code in plain text:

let

   Source = Table.PromoteHeaders(Table.FromColumns({ {"Type" ,"Purchase Order" ,"Sales Order" ,"Sales Order" ,"Sales Order" ,"Purchase Order" ,"Purchase Order"}, {"Number" ,"12345" ,"34567" ,"12345" ,"45769" ,"34567" ,"12567"} })),

   PurchaseOrders = Table.SelectRows(Source, each ([Type] = "Purchase Order")),

   MergeSteps = Table.NestedJoin(Source,{"Number"},PurchaseOrders,{"Number"},"NewColumn",JoinKind.LeftOuter),

   ExpandPONumber = Table.ExpandTableColumn(MergeSteps, "NewColumn", {"Number"}, {"Number.1"}),

   TextWhenNoPoNumberFound = Table.ReplaceValue(ExpandPONumber,null,"""no PO""",Replacer.ReplaceValue,{"Number.1"})

in

   TextWhenNoPoNumberFound

[ImkeF]

Don't know what else to do - but this code is cut off as well in IE. So please use different browser (Edge, Firefox and Opera seem to work)

[Sean]

@ImkeF No I have all the code you posted - I'm using Edge.

Now logged in on Firefox everything looks the same

Anyway I have all your code - the problem is I don't know how to make it work with my code here...

let
    Source = Excel.Workbook(File.Contents("C:\Users\Sean\Downloads\Compare Rows Test.xlsx"), null, true),
    TableCC_Table = Source{[Item="TableCC",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(TableCC_Table,{{"Type", type text}, {"Number", Int64.Type}})
in
    #"Changed Type"

Where does your code go?

Thanks!

@heathernicole did you get it to work?

[ImkeF]

So sorry, was too quick on that.

My code doesn't need any tables - got everything in them: http://www.thebiccountant.com/2016/03/28/how-to-deal-with-m-code-samples/

Just create a new blank query and paste everything in, replacing the defaults.

Or use your own data and combine it like this:

let  

Source = Excel.Workbook(File.Contents("C:\Users\Sean\Downloads\Compare Rows Test.xlsx"), null, true),  

TableCC_Table = Source{[Item="TableCC",Kind="Table"]}[Data],  

#"Changed Type" = Table.TransformColumnTypes(TableCC_Table,{{"Type", type text}, {"Number", Int64.Type}}),

PurchaseOrders = Table.SelectRows(   #"Changed Type", each ([Type] = "Purchase Order")),

MergeSteps = Table.NestedJoin(Source,{"Number"},PurchaseOrders,{"Number"},"NewColumn",JoinKind.LeftOuter),

ExpandPONumber = Table.ExpandTableColumn(MergeSteps, "NewColumn", {"Number"}, {"Number.1"}),

TextWhenNoPoNumberFound = Table.ReplaceValue(ExpandPONumber,null,"""no PO""",Replacer.ReplaceValue,{"Number.1"})

in

   TextWhenNoPoNumberFound

[Sean]

@ImkeF I get this...

[ImkeF]

... another ''Source'' to be replaced (sorry):

let  

Source = Excel.Workbook(File.Contents("C:\Users\Sean\Downloads\Compare Rows Test.xlsx"), null, true),  

TableCC_Table = Source{[Item="TableCC",Kind="Table"]}[Data],  

#"Changed Type" = Table.TransformColumnTypes(TableCC_Table,{{"Type", type text}, {"Number", Int64.Type}}),

   PurchaseOrders = Table.SelectRows(   #"Changed Type", each ([Type] = "Purchase Order")),

   MergeSteps = Table.NestedJoin(   #"Changed Type",{"Number"},PurchaseOrders,{"Number"},"NewColumn",JoinKind.LeftOuter),

   ExpandPONumber = Table.ExpandTableColumn(MergeSteps, "NewColumn", {"Number"}, {"Number.1"}),

   TextWhenNoPoNumberFound = Table.ReplaceValue(ExpandPONumber,null,"""no PO""",Replacer.ReplaceValue,{"Number.1"})

in

   TextWhenNoPoNumberFound

[Sean]

@ImkeF 

Now it gives another Error in the MergeSteps => JoinKind.LeftOuter <= Invalid Identifier

[ImkeF]

@Sean @heathernicole

Looks like we need a new approach 🙂

Have a look at the video

[Sean · Accepted solution]

@ImkeF Thanks! This works great!

One thing I'll say if anyone is trying this don't rename a step "Filter Table" with a space

I don't know why it doesn't work with a space - use "FilteredTable" no space

Otherwise works great! Thanks again!