Delimiting with Conditions

[JS]

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

[v-yuezhe-msft]

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

[JS]

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! 

[v-yuezhe-msft]

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

[MarcelBeug]

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