retrieve text/string from cells in excel

[AlexLiang]

Hi everyone,

Thanks for your attention. I would like to retrieve several information to made counts of a certain information as for ETL. My question is that how can I write the DAX script to extract text from a certain cell in excel? The following picture is the example, how do I retrieve the red words? 

Really appreciate for your support. Thank you. 🙂

[v-chuncz-msft · Accepted solution]

@AlexLiang,

You may add a calculated column as follows.

Column =
VAR s1 = "3. Defect type:"
VAR s2 = "4. Initiator:"
VAR p1 =
    SEARCH ( s1, Table1[Abnormal Description] )
VAR p2 =
    SEARCH ( s2, Table1[Abnormal Description], p1 )
RETURN
    MID ( Table1[Abnormal Description], p1 + LEN ( s1 ), p2 - p1 - LEN ( s1 ) )

[AlexLiang]

Hi @v-chuncz-msft

Really appreciate for your kindly support.

Thank you 🙂

[Phil_Seamark]

Hi @AlexLiang

This is a rough approach but you could introduce calculated columns to detect it using DAX

something like

Column contains Crack = 
          IF(
            FIND(
                "Crack",
                'Table1'[Column3], 
                1,
                blank()
                ) > 0 , TRUE(), FALSE())

[AlexLiang]

Hi @Phil_Seamark

Thanks for your feedback.

What if I want to retrieve all kinds of "defect type" and put them in a new column?

Because I may need to calculate the frequency of each defect type.

Thank you again. 🙂

[Phil_Seamark]

Do you have a predefined list of defects?  Do you need to maintain a separate count for each type?

[AlexLiang]

I think we have the predefined list of defect. The separate count for each type is the column that I want to input to powerBI for visualization. I am not sure if it's the same meaning as maintain, thank you.