Forum Discussion

yagusik's avatar
yagusik
Frequent Visitor
6 years ago
Solved

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 AllForwarding...
Need Help
  • ImkeF's avatar
    ImkeF
    6 years ago

    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.

     

ImkeF's avatar
ImkeF
Icon for Community Champion rankCommunity Champion
6 years ago

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's avatar
yagusik
Frequent Visitor
6 years ago

Dear ImkeF,

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

  • ImkeF's avatar
    ImkeF
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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's avatar
      yagusik
      Frequent Visitor
      6 years ago

      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?