Column values based on previous column with logic

[naylorpsu]

If I have 1 column that defines the customer (9 digit number) and the system (_eS1 or _eS2).

BDPC_System

156168383_eS1

156168383_eS1

156168383_eS2

156168383_eS2

156168388_eS1

156168389_eS1

<tbody>
</tbody>

I want to add another column in PowerQuery that looks at the BDPC_System column and says if I see the '156168383_eS1' AND '156168383_eS2' then it reads 'Upgrade' if the customer number does not have _eS1 and _eS2 for the same number, it is just blank.

I hope this makes sense. Any help would be appreciated.

TIA.

 

[horseyride]

Below should do the trick

Basically (a) duplicate input column (b) split the es? to a new column (c) filter for eS1 and save (d) filter for es2 and save (e) inner join to find items with both es1 and es2 (f) combine with original data (g) if statement to create custom column with appropriate text (h) remove leftover columns

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Duplicated Column" = Table.DuplicateColumn(Source, "BDPC_System", "BDPC_System - Copy"),
    ZTable = Table.SplitColumn(#"Duplicated Column", "BDPC_System", Splitter.SplitTextByEachDelimiter({"_"}, QuoteStyle.Csv, false), {"BDPC_System.1", "BDPC_System.2"}),
    ATable = Table.SelectRows(ZTable, each ([BDPC_System.2] = "eS2")),
    BTable = Table.SelectRows(ZTable, each ([BDPC_System.2] = "eS1")),
    CTable = Table.NestedJoin(ATable,{"BDPC_System.1"},BTable,{"BDPC_System.1"},"BTable",JoinKind.Inner),
    #"Merged Queries1" = Table.NestedJoin(ZTable,{"BDPC_System.1"},CTable,{"BDPC_System.1"},"FTable",JoinKind.LeftOuter),
    #"Expanded FTable" = Table.ExpandTableColumn(#"Merged Queries1", "FTable", {"BDPC_System.1"}, {"FTable.BDPC_System.1"}),
    #"Added Custom" = Table.AddColumn(#"Expanded FTable", "Upgrade", each if [FTable.BDPC_System.1]=null then "Upgrade" else ""),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"BDPC_System.1", "BDPC_System.2", "FTable.BDPC_System.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"BDPC_System - Copy", "BDPC_System"}})
in
    #"Renamed Columns"