Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin 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.
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?
Solved! Go to Solution.
@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
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!
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
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"
@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
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
65 | |
63 | |
52 | |
37 | |
36 |
User | Count |
---|---|
82 | |
67 | |
61 | |
46 | |
45 |