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

[yagusik]

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?

[ImkeF]

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)

[yagusik]

Dear ImkeF,

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

[ImkeF · 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.

[yagusik]

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? 

[ImkeF]

HI @yagusik 

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