Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi everyone,
I'm looking at data in 3 different columns. The product number I need could be in any column. The columns currently have junk with things like cities and upc numbers in them where the product number is not. The product numbers are also only 5-6 digits long. Is there a way to write a ISNUMBER dax formula in a new columa to isolate the product number from the citiies and a count of the digits to make sure only ones with 5-6 digits come back as True?
Thanks for the help!
Solved! Go to Solution.
@vanessafvg take it easy! we all here to assist.
@jboschee34 if for some reason you cannot use PQ (I would recommend that, my previous solution), you can use the following DAX expression, add it as a column
Product Number =
VAR __Table = { MyProduct[Reference 1], MyProduct[Reference 2], MyProduct[Reference 3] }
VAR __AddRules = ADDCOLUMNS ( __Table, "@Length", LEN ( [Value] ), "@IsNumber", NOT ISERROR ( CONVERT ( [Value], INTEGER ) ) )
RETURN
MAXX ( FILTER ( __AddRules , [@Length] IN { 5, 6 } && [@IsNumber] ), [Value] )
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@jboschee34 in PQ try to add another custom column and copy this expression. Change column names as per your table:
try List.RemoveItems(List.Transform({[Reference 1],[Reference 2],[Reference 3]},(x as any) =>
let
length = if x is null then null else Text.Length(x),
value = try Number.From(x) otherwise null,
value1 = if length = 5 or length =6 then value else null
in value1
),{null}){0} otherwise null
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@jboschee34 what is the ask here? The product number can be any of these 3 columns with the original rules you defined. Is this what you are looking for?
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@parry2k Correct. The product number can live in any of the 3 columns, but will never contains any letters or characters such as dashes. Also, the product number will either be 5 or 6 digits long.
@jboschee34 if you want a value instead of true/false, change to this:
if (try Number.FromText([Column1]) otherwise null) = null then null else if Text.Length([Column1]) = 5 or Text.Length([Column1]) = 6 then [Column1] else null
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
I got all null results
you will probably need to share some data to understand what it looks like. hard to a code a solution without fully understanding the requirements.
Proud to be a Super User!
Here's some example data @parry2k
Reference 1 | Reference 2 | Reference 3 |
1570027-15 | 154505 | |
1489920 | 152582 | |
Furniture | SO674111 | 146761 |
Furniture | SO674111 | 146761 |
Furniture | SO674111 | 146761 |
Furniture | SO677170 | 147904 |
Furniture | SO677170 | 147904 |
Furniture | SO677170 | 147904 |
Furniture | SO677170 | 147904 |
155237 |
Most of the time, the right data will be in the Reference 1 column but I provided some of the problem childs.
@vanessafvg good solution but how do you find out if this is a valid number, I think that is one of the rules along with length check.
ABCDEF = 6 but not a product because it is not a number
123456 = 6, it is a number and meet the length criteria
🙂
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
thanks parry, pure slip of the memory, i forgot to include that !
here is another version.. @parry2k didn't mean to double post, i think we responded at the same time howver, didn't see your response before i responded.
a million ways (well not quite) to do this
productno =
VAR isno =
IF ( ISNUMBER ( field ), 1 )
VAR islen =
IF ( OR ( LEN ( [field] ) = 5, LEN ( [field] ) = 6 ), 1 )
RETURN
IF ( isno + islen = 2, field, BLANK () )
Proud to be a Super User!
@vanessafvg take it easy! we all here to assist.
@jboschee34 if for some reason you cannot use PQ (I would recommend that, my previous solution), you can use the following DAX expression, add it as a column
Product Number =
VAR __Table = { MyProduct[Reference 1], MyProduct[Reference 2], MyProduct[Reference 3] }
VAR __AddRules = ADDCOLUMNS ( __Table, "@Length", LEN ( [Value] ), "@IsNumber", NOT ISERROR ( CONVERT ( [Value], INTEGER ) ) )
RETURN
MAXX ( FILTER ( __AddRules , [@Length] IN { 5, 6 } && [@IsNumber] ), [Value] )
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@parry2k Thanks! I tried to get the PQ expression to work but kept getting null values. I'm not very experienced in the PQ side of things. The DAX expression worked perfectly though. I need to definitely get more familiar with the PQ. Thanks to you and @vanessafvg for all the help!
@parry2k all easy here 🙂 just sayin...
@jboschee34 did you attempt the new solution i posted if you can't get the other to work? but do think pushing the calculation into power query is a a better practice.
Proud to be a Super User!
this might work?
productno =
IF ( OR ( LEN ( [field] ) = 5, LEN ( [field] ) = 6 ), field, BLANK () )
Proud to be a Super User!
perfect! Thank you!
@jboschee34 in PQ add a column to mark it true or false
if (try Number.FromText([Column1]) otherwise null) = null then false else if Text.Length([Column1]) = 5 or Text.Length([Column1]) = 6 then true else false
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
I got all False results. Do they need to start in a certain format?