Forum Discussion

tgo's avatar
tgo
Frequent Visitor
8 years ago
Solved

Find/Search functions

Hi everybody,

 

I'm struggling with the Find/Search function (maybe it's not adapted for my case). I have a set of data that looks like that:

 

Data1: Miche_Georges+Family_FR

Data2:Ronald_Douglas_EN+FR
Data3:Michel_Lecompte+Children_DE+NL

 

I would like to create a second/third column to retrieve the information after the third "_" so in this case it would be

Column 1 Column 2

FR                 

EN               FR

DE                NL

 

I'm not certain if it's possible to retrieve the information like that.

 

Thanks in advance for the answer

Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    tgo,

    Please create the following columns in your table. For more details, please check attached PBIX file.

    Column = RIGHT(Table1[Data1],LEN(Table1[Data1])-FIND("@",SUBSTITUTE(Table1[Data1],"_","@",LEN(Table1[Data1])-LEN(SUBSTITUTE(Table1[Data1],"_",""))),1))
    Column 1 = LEFT(Table1[Column],(FIND("+",Table1[Column],1,LEN(Table1[Column])+1)-1))
    Column 2 = RIGHT(Table1[Column],(FIND("+",Table1[Column],1,1)-1))



    Regards,
    Lydia

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    8 years ago

    So, you will need to use a nested SEARCH/FIND like:

     

    Column 2 = 
SEARCH(
  "_",
  [Column 1],
  SEARCH(
    "_",
    [Column 1],
    SEARCH(
      "_",
      [Column 1],
      ,
      LEN([Column 1])
    ),
    LEN([Column 1])
  )
  ,BLANK()
)
    • tgo's avatar
      tgo
      Frequent Visitor
      8 years ago

      Thanks for the answer, when I tried it says that the text in the search function was not found. Which is weird because the "_" is in every data.

       

      I found a way that is working on Excel 

      =RIGHT(Data1;LEN(E1435)-FIND("@";SUBSTITUTE(Data1;"_";"@";LEN(Data1)-LEN(SUBSTITUTE(Data1;"_";"")));1))

      But when I introduce the formula in Power BI I have an error message "An arugment of function 'SUBSTITUTE' has the wrong data type or has an invalid value. But I have no clue about what is going wrong :/.

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    tgo,

    Please create the following columns in your table. For more details, please check attached PBIX file.

    Column = RIGHT(Table1[Data1],LEN(Table1[Data1])-FIND("@",SUBSTITUTE(Table1[Data1],"_","@",LEN(Table1[Data1])-LEN(SUBSTITUTE(Table1[Data1],"_",""))),1))
    Column 1 = LEFT(Table1[Column],(FIND("+",Table1[Column],1,LEN(Table1[Column])+1)-1))
    Column 2 = RIGHT(Table1[Column],(FIND("+",Table1[Column],1,1)-1))



    Regards,
    Lydia