cancel
Showing results for 
Search instead for 
Did you mean: 
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 II
Super User II

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

View solution in original post

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
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors