cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper I

## IF function _ I need to find a 6 digit number in a textcolumn

Dear Community,

I hope you can help me. I need to create a calculated column to identyfy the lines with a 6 digit number in the text.

Is there a function to identify the notifictn(unic number) that has a 6 digit number in the material text(placed at different positions and with different 6 digit numbers)?

9 REPLIES 9
Super User

Hi @gjelstrup
Please test the following. I did not have the chance to test from my side.

``````6 digit number =
VAR String = 'Table'[Material Description]
VAR Items =
SUBSTITUTE ( String, " ", "|" )
VAR Length1 =
COALESCE ( PATHLENGTH ( Items ), 1 )
VAR T1 =
GENERATESERIES ( 1, Length1, 1 )
VAR T2 =
SELECTCOLUMNS ( T1, "@Item1", PATHITEM ( Items, [Value] ) )
VAR T3 =
FILTER (
T2,
VAR Item1 =
COALESCE ( [@Item1], 1 )
VAR Length2 =
LEN ( Item1 )
VAR T4 =
GENERATESERIES ( 1, Length2, 1 )
VAR T5 =
SELECTCOLUMNS ( T4, "@Item2", MID ( Item1, 1, [Value] ) )
VAR T6 =
GENERATESERIES ( 0, 10, 1 )
RETURN
LEN ( Item1 ) = 6
&& ISEMPTY ( EXCEPT ( T5, T6 ) )
)
RETURN
MAXX ( T3, [@Item] )``````
Helper I

Hi @tamerj1 I really apprecialte your help. Thank you.

I cannot make it Work. I think that I need to change what is marked by ned in below scrren dump, however, I am not certain what I need to change it to.

Super User

There is one extra closing square bracket. Please delete

Helper I

Hi @tamerj1

Thank you very much for your feedback. Now it only gives me one error :

Super User

Yes, this is a typo mistake, the price paid for not testing. It should be [@Item2]

Helper I
Dear @tamerj1 - It is unfortunately still not Working. I appreciate very much your support. I have put the error message below in a screendump and the coding below.
Thank you for helping me out !

6 digit number =
VAR String = 'QM11'[Material description]
VAR Items =
SUBSTITUTE ( String, " ", "|" )
VAR Length1 =
COALESCE ( PATHLENGTH ( Items ), 1 )
VAR T1 =
GENERATESERIES ( 1, Length1, 1 )
VAR T2 =
SELECTCOLUMNS ( T1, "@Item1", PATHITEM ( Items, [Value] ) )
VAR T3 =
FILTER (
T2,
VAR Item1 =
COALESCE ( [@Item1], 1 )
VAR Length2 =
LEN ( Item1 )
VAR T4 =
GENERATESERIES ( 1, Length2, 1 )
VAR T5 =
SELECTCOLUMNS ( T4, "@Item2", MID ( Item1, 1, [Value] ) )
VAR T6 =
GENERATESERIES ( 0, 10, 1 )
RETURN
LEN ( Item1 ) = 6
&& ISEMPTY ( EXCEPT ( T5, T6 ) )
)
RETURN
MAXX ( T3, [@Item2] )
Super User

Apologies again. It should be [@Item1]

6 digit number =
VAR String = 'QM11'[Material description]
VAR Items =
SUBSTITUTE ( String, " ", "|" )
VAR Length1 =
COALESCE ( PATHLENGTH ( Items ), 1 )
VAR T1 =
GENERATESERIES ( 1, Length1, 1 )
VAR T2 =
SELECTCOLUMNS ( T1, "@Item1", PATHITEM ( Items, [Value] ) )
VAR T3 =
FILTER (
T2,
VAR Item1 =
COALESCE ( [@Item1], 1 )
VAR Length2 =
LEN ( Item1 )
VAR T4 =
GENERATESERIES ( 1, Length2, 1 )
VAR T5 =
SELECTCOLUMNS ( T4, "@Item2", MID ( Item1, 1, [Value] ) )
VAR T6 =
GENERATESERIES ( 0, 10, 1 )
RETURN
LEN ( Item1 ) = 6
&& ISEMPTY ( EXCEPT ( T5, T6 ) )
)
RETURN
MAXX ( T3, [@Item1] )
Frequent Visitor

Sorry, im really new to this BI stuff, where are you putting this code, in a measure or a new column?

Super User

This is a calculated column

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors