Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!!!
Solved! Go to 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!
You have to create a Relationship to the new Lookup Table you've just created...
Here's what I had to do:
the actual table I'm working with has a lot more columns and more values for that "Type" column. So what I did was
In order to correct the date issue - I redid the steps and didn't take out any columns. Apparently one of the columns was a link to the Calendar table.
Once I did that - I manually created the relationship between the Referenced table (with all the calculations and filters) and the Calendar table. All slicers work and the data is correct!! 😄
THANK YOU! THANK YOU! THANK YOU!
I've been working on this one for about 2 1/2 weeks so this was a huge win. Greatly appreciated the collaboration and desire to help! Thanks again!
POs = IF ( [Type] = "Purchase Order", [Number], "NO PO")
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!
What are the columns in your data?
What do you mean exactly?
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
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.
Proud to be a Super User!
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.
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
@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!!!
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
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
@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!!!
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
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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)
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
@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?
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
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
... 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
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Looks like we need a new approach 🙂
Have a look at the video
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
@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!
User | Count |
---|---|
96 | |
87 | |
78 | |
72 | |
68 |
User | Count |
---|---|
113 | |
105 | |
84 | |
65 | |
64 |