Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to 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
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
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
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