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...
Need Help
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