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
yagusik
Frequent Visitor

How to extract delimited text from one column based on condition from another?

I have forwarding information stored in the AllForwardingDetails column. Depending on the type of forwarding in the ForwardingType column, I need to extract specific parameters from the AllForwardingDetails column. I came up with 2 variations of code but neither of them is working. Please help.

 

Version 1: Results in the error: Expression.Error: We cannot convert Type to List type.

Details:

    Value=[Type]

    Type=[Type]

 

= Table.AddColumn(#"Duplicated Column", "ForwardingDetailsExtract", each if Text.Contains([ForwardingType], "FILESYSTEM") then Text.BetweenDelimiters([#"AllForwardingDetails"], "; ForwardingPath\", "; ", type text) else if Text.Contains([ForwardingType], "MAIL") then Text.BetweenDelimiters([#"AllForwardingDetails"], "; ForwardingReceiver\", "; ", type text) else "null")

 

Version 2: Returns a list. But I need a specific value.

 

= Table.AddColumn(#"Duplicated Column", "ForwardingDetailsExtract", each if Text.Contains([ForwardingType], "FILESYSTEM") then {{"AllForwardingDetails", each Text.BetweenDelimiters(_, "; ForwardingPath\", "; "), type text}} else   if Text.Contains([ForwardingType], "MAIL") then {{"AllForwardingDetails", each Text.BetweenDelimiters(_, "; ForwardingReceiver\", "; "), type text}} else null)

 

How to make it work?

1 ACCEPTED SOLUTION

Yes, sorry, that code couldn't work.

How about this?:

 

= Table.AddColumn(
#"Duplicated Column", 
"ForwardingDetailsExtract", 
each if Text.Contains([ForwardingType], "FILESYSTEM") 
then  Text.BetweenDelimiters([ForwardingType], "; ForwardingPath\", "; ") 
else if Text.Contains([ForwardingType], "MAIL") 
then Text.BetweenDelimiters([ForwardingType], "; ForwardingReceiver\", "; ")
else null)

 

Otherwise please share a bit more about your table and desired results - it's a bit tough to guess what shall actually happen here.

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

5 REPLIES 5
ImkeF
Super User
Super User

Hi @yagusik 

please try this:

 

= Table.AddColumn(
#"Duplicated Column", 
"ForwardingDetailsExtract", 
each if Text.Contains([ForwardingType], "FILESYSTEM") 
then  each Text.BetweenDelimiters(_, "; ForwardingPath\", "; ") 
else if Text.Contains([ForwardingType], "MAIL") 
then each Text.BetweenDelimiters(_, "; ForwardingReceiver\", "; ")
else null)

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

yagusik
Frequent Visitor

Dear ImkeF,

In your case return is Function, sample (t6006) =>

Yes, sorry, that code couldn't work.

How about this?:

 

= Table.AddColumn(
#"Duplicated Column", 
"ForwardingDetailsExtract", 
each if Text.Contains([ForwardingType], "FILESYSTEM") 
then  Text.BetweenDelimiters([ForwardingType], "; ForwardingPath\", "; ") 
else if Text.Contains([ForwardingType], "MAIL") 
then Text.BetweenDelimiters([ForwardingType], "; ForwardingReceiver\", "; ")
else null)

 

Otherwise please share a bit more about your table and desired results - it's a bit tough to guess what shall actually happen here.

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

yagusik
Frequent Visitor

Thank you so much, that worked.

 

= Table.AddColumn(#"Duplicated Column", "ForwardingDetailsExtract", each if Text.Contains([ForwardingType], "FILESYSTEM") then Text.BetweenDelimiters([AllForwardingDetails], "; ForwardingPath\", "; ") else if Text.Contains([ForwardingType], "MAIL") then Text.BetweenDelimiters([AllForwardingDetails], "; ForwardingReceiver\", "; ") else null)

 

So, my mistake was to include "type text" at the end? I start working with Power BI 2 weeks ago, and lucking fundamentals. What would you recommend as a crash course? 

HI @yagusik 

please check my learning resource page here: https://www.thebiccountant.com/learning-resources/ 

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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.

Top Solution Authors
Top Kudoed Authors