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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
AlexLiang
Frequent Visitor

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 extract text from a certain cell in excel? The following picture is the example, how do I retrieve the red words? 

retrieve text from excel.JPG

 

Really appreciate for your support. Thank you. 🙂

1 ACCEPTED SOLUTION
v-chuncz-msft
Community Support
Community Support

@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 ) )
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
v-chuncz-msft
Community Support
Community Support

@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 ) )
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-chuncz-msft

 

Really appreciate for your kindly support.

Thank you 🙂

 

Phil_Seamark
Employee
Employee

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

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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. 🙂

 

 

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


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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