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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
TomJWhite
Frequent Visitor

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 against another column (I have a custom column [ContentType] to identify entries as being "TV" instead of "Movie"), and then if they are "TV" entries, I want to extract all characters from the string before the colon : so that I get "Brooklyn Nine-Nine". 

 

Whats the best way to go about this? Im still new to Power BI/Power Query so im not sure how best to accomplish this. M or DAX is fine im assuming its better to do this in power query before loading?

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

@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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

3 REPLIES 3
atillotson
Frequent Visitor

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!

 

atillotson_0-1700563595566.png

 

atillotson_1-1700563612122.png

 

Vera_33
Resident Rockstar
Resident Rockstar

Hi @TomJWhite 

 

Yes, M is more intuitive for text munipulation. You need to observe the data, if it is always to extract the first ":" then it is simple

Vera_33_0-1627958497285.png

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcirKz8/OqcxT8MvMS9UFEVYKwamJxfl5CsZWCu5FqekKiXkpCj6JRUWVChquBZnF+SmpCkZGmkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.BeforeDelimiter([Column1],":"))
in
    #"Added Custom"

 

Greg_Deckler
Super User
Super User

@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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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