Nested If statement for Duplicate and Unique ID

[IfaZ_1]

Hi, 

I have tried to create a custom column based on nested if statement. However I think it doesnt give me the outcome that I want. 

This is my formula:

if [Account ID]> 1 and Text.EndsWith([Sourcing Individual],"Dir") then "Direct Sourced"
else
if [Account ID]> 1 and Text.EndsWith([Sourcing Individual],"Lic") then "Licensee Shared"
else
if [Account ID]= 1 and Text.EndsWith([Sourcing Individual],"Dir") then "Direct Sourced"
else
if [Account ID] = 1 and Text.EndsWith([Sourcing Individual],"Lic") then "Licensee Only"
else null

Basically, if I have a unique Account ID and its Sourcing Individual ends with "Dir" it should be Direct Sourced if end with "Lic" should be Licensee only. 

if the Account ID has duplicate and its Sourcing Individual ends with "Dir" it should be Direct Sourced and if it ends with "Lic" should be Licensee Shared. 

Here is my sample file: https://we.tl/t-Kv0HabVlgQ

[Vijay_A_Verma]

Use this in your custom column

[cnt=List.Count(List.Select(#"Changed Type"[Account ID], (x)=> x=[Account ID])),
result = if Text.EndsWith([Sourcing Individual],"Dir") then "Direct Sourced" 
else if Text.EndsWith([Sourcing Individual],"Lic")
then if cnt > 1 then "Licensee Shared" else "Licensee Only" else null][result]

[BA_Pete · Accepted solution]

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

[IfaZ_1]

Hi Pete, thank you for replying. I tried to used your code however, it is showing me Token Eof expected. 

[BA_Pete]

The code I provided was not to be entered into a custom column, it was a complete example query.

Copy all of the code I sent, then create a new blank query in Power Query. Open Advanced Editor and paste all of my code over the default code in there.

You can now see the full example query working with each step.

Pete

[IfaZ_1]

Thank youuuuuu so much!!! I got it already. 

[BA_Pete]

Cool, glad it's working for you.

Don't forget to give a thumbs-up on any posts that helped you 👍

Pete