Forum Discussion
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?
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.
5 Replies
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)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.
