Forum Discussion

JS's avatar
JS
Helper II
9 years ago

Delimiting with Conditions

Hello. 

 

Is there anyway I can delimit a column based on a condition with another column. 

 

Example. 

 

Team                 Summary 

   A         This is an example - 100

   B         This is not an example - 200

   A         Hello - 500
   C         This is an example - 150

 

I only want to delimit the Summary column with "-" where team = A. 

 

Thanks in advance! 

 

JS

 

Need Help

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi JS,

    Create the following columns in your table.

    FirstCol = IF(Table1[Team ]="A",LEFT(Table1[Summary],FIND("-",Table1[Summary])-1),Table1[Summary])
    SecondCol = IF(Table1[Team ]="A",REPLACE(Table1[Summary],1,FIND("-",Table1[Summary]),""),Table1[Summary])


    Regards,
    Lydia Zhang

    • MarcelBeug's avatar
      MarcelBeug
      Community Champion
      9 years ago

      Power Query is the more appropriate tool for these kinds of transformations.

       

      My suggstion would be to split the table in team A and other teams, split the column for team A and combine the 2 parts back tiogether.

       

      let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Typed1 = Table.TransformColumnTypes(Source,{{"Team", type text}, {"Summary", type text}}),
    FilteredA = Table.SelectRows(Typed1, each ([Team] = "A")),
    Splitted = Table.SplitColumn(FilteredA, "Summary", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Summary.1", "Summary.2"}),
    Typed2 = Table.TransformColumnTypes(Splitted,{{"Summary.1", type text}, {"Summary.2", Int64.Type}}),
    Renamed = Table.RenameColumns(Typed2,{{"Summary.1", "Summary"}}),
    FikteredNotA = Table.SelectRows(Typed1, each [Team] <> "A"),
    Combined = Table.Combine({Renamed,FikteredNotA})
in
    Combined

       

    • JS's avatar
      JS
      Helper II
      9 years ago

      Thanks Lydia. 

       

      I have tried - but I receive the following error message. "The search Text provided to function 'FIND' could not be found in the given text." Any idea what is wrong? 

       

      Many thanks! 

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        Hi  JS,

        Everything works as expected when I use your sample data. Do you copy the "-" from your column to FIND function? Would you mind sharing me your PBIX file so that I can troubleshoot?

        Regards,
        Lydia