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
Anonymous
Not applicable

Extracting text after a delimiter only if column contains specific text

I would like to perform the Text.AfterDelimiter() function on a column [COLUMN_B] but only when [COLUMN_A] contains specific text: "No Producer - Direct"

Also I want this action to occure within column b, not into a new column 🙂 

 

Desired result:

 

[COLUMN_A]                        [COLUMN_B]

No Producer - Direct,SampleA      SampleA

No Producer - Direct,SampleB      SampleB

No Producer - Direct,SampleC      SampleC

ABC                               ABC

Direct, SampleD                   Direct, SampleD

1 ACCEPTED SOLUTION
v-xulin-mstf
Community Support
Community Support

Hi @Anonymous

 

You can try this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8stXCCjKTylNTi1S0FVwySxKTS7RCU7MLchJdVSK1cGrwImQAmewAkcn50MLDi0As6HSChB5F6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [COLUMN_A = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each if Text.Contains([COLUMN_A],"No Producer - Direct") then Text.AfterDelimiter([COLUMN_A], ",") else [COLUMN_A]),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Custom", "COLUMN_B"}})
in
    #"Renamed Columns"

Here is the output:

v-xulin-mstf_0-1620283169903.png

The pbix is attached.

 

Best Regards,
Link

 

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

3 REPLIES 3
v-xulin-mstf
Community Support
Community Support

Hi @Anonymous,

 

Did my solution help you solve your problem?

If the solution helps then please consider Accept it as the solution to help the other members find it more quickly.

 

Best Regards,
Link

v-xulin-mstf
Community Support
Community Support

Hi @Anonymous

 

You can try this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8stXCCjKTylNTi1S0FVwySxKTS7RCU7MLchJdVSK1cGrwImQAmewAkcn50MLDi0As6HSChB5F6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [COLUMN_A = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each if Text.Contains([COLUMN_A],"No Producer - Direct") then Text.AfterDelimiter([COLUMN_A], ",") else [COLUMN_A]),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Custom", "COLUMN_B"}})
in
    #"Renamed Columns"

Here is the output:

v-xulin-mstf_0-1620283169903.png

The pbix is attached.

 

Best Regards,
Link

 

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

Fowmy
Super User
Super User

@Anonymous 

You need to add an IF condition to check if  "No Producer - Direct" exists then perform Text.AfterDelimite function as follows. I am adding a new column with this condition

if Text.Contains([COLUMN_A],"No Producer - Direct") then Text.AfterDelimiter([COLUMN_B], ",") else null



 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

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.