Forum Discussion
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([S...
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 addOutputThe trick here is to use Group By & Count Rows to identify if an [Account ID] is unique or not.
It gives this output:
Pete
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
Hi Pete, thank you for replying. I tried to used your code however, it is showing me Token Eof expected.
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