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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
gjelstrup
Helper I
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)?

gjelstrup_3-1683532103018.png

 

 

 

 

9 REPLIES 9
tamerj1
Super User
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] )

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. 

 

 

gjelstrup_0-1683544177950.png

 

 

@gjelstrup 

There is one extra closing square bracket. Please delete

2.png

Hi @tamerj1 

 

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

 

gjelstrup_1-1683793885468.png

 

 

@gjelstrup 

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

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 ! 
 
gjelstrup_0-1683875578864.png

 

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] )

@gjelstrup 

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] )

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

@JamesH2405 

This is a calculated column

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.