Nested If statement for Duplicate and Unique ID

Hi IfaZ_1 ,

Try the following code:

let
    Source = Excel.Workbook(File.Contents("C:\Excel\If Statement_Acoount ID duplicate.xlsx"), null, true),
    Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
    chgTypes = Table.TransformColumnTypes(Table1_Table,{{"Account ID", Int64.Type}, {"Account Name", type text}, {"Sourcing Individual", type text}}),

// Relevant steps ----->
    groupAccountID = Table.Group(chgTypes, {"Account ID"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"data", each _, type table [Account ID=nullable number, Account Name=nullable text, Sourcing Individual=nullable text]}}),
    expandData = Table.ExpandTableColumn(groupAccountID, "data", {"Account Name", "Sourcing Individual"}, {"Account Name", "Sourcing Individual"}),
    addOutput =
        Table.AddColumn(
            expandData,
            "output",
            each let __suffix = Text.End([Sourcing Individual], 3) in
            if __suffix = "Dir" then "Drect Sourced"
            else if [Count] > 1 and __suffix = "Lic" then "Licensee Shared"
            else if [Count] = 1 and __suffix = "Lic" then "Licensee Only"
            else "Unmapped"
        )
        
in
    addOutput

The trick here is to use Group By & Count Rows to identify if an [Account ID] is unique or not.

It gives this output:

Pete