Forum Discussion

TomJWhite's avatar
TomJWhite
Frequent Visitor
5 years ago
Solved

Extract part of string before specific character

Hi there,   Im working with a Netflix dataset that has titles (column name [Title]) for shows like "Brooklyn Nine-Nine: Season 3: Greg and Larry (Episode 22)".   For each entry, I want to check a...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    5 years ago

    TomJWhite Well, in Power Query you could split the column based on the : but there is also Text.BeforeDelimiter so you could do:

     

    if [ContentType] = "TV" then Text.BeforeDelimiter([Title],":") else [Title]

     

    In DAX you would do something like:

     

    Column = 
  IF(
    [ContentType] = "TV",
    LEFT([Title],SEARCH(":",[Title])-1),
    [Title]
  )

     

    Text.BeforeDelimiter - PowerQuery M | Microsoft Docs

atillotson's avatar
atillotson
Frequent Visitor
2 years ago

Hi there,

 

I've just tried the suggestion from you above for a similar situation but I seem to get the error 'The search Text provided to function 'SEARCH' could not be found in the given text' (see attached) but the "-" character is present (see attached).

 

Not sure if I am missing something - no "if" component is required because it applies to all rows in the table so just took the nested function from within. Thanks!