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.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.