Forum Discussion

AlexLiang's avatar
AlexLiang
Frequent Visitor
8 years ago
Solved

retrieve text/string from cells in excel

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 extr...
General Comment
Need Help
Show and Tell
Tutorial Requests
  • v-chuncz-msft's avatar
    v-chuncz-msft
    8 years ago

    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 ) )
Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
8 years ago

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's avatar
AlexLiang
Frequent Visitor
8 years ago

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's avatar
    Phil_Seamark
    Microsoft Employee
    8 years ago

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

    • AlexLiang's avatar
      AlexLiang
      Frequent Visitor
      8 years ago

      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.