Forum Discussion

IfaZ_1's avatar
IfaZ_1
Helper I
3 years ago
Solved

Nested If statement for Duplicate and Unique ID

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

 

Need Help
  • BA_Pete's avatar
    BA_Pete
    3 years ago

    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

6 Replies

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    3 years ago

    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's avatar
    BA_Pete
    Super User
    3 years ago

    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's avatar
      IfaZ_1
      Helper I
      3 years ago

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

      • BA_Pete's avatar
        BA_Pete
        Super User
        3 years ago

         

        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