Forum Discussion
Solved
Adding conditional unique index column based on several criterias
The challenge is the need for having a column listing an existing unique ID or create new unique ID based on the following criteria: - If the customer no is recognized, then use the existing uniq...
- Thank you so much, looks very useful. I will try to implement this and get back with feedback as soon as possible. Thanks again!
Yes, you can create an Index-column like this, that will be recreated completely everytime you refresh your query like this:
let
Source = Tabelle2,
#"Grouped Rows" = Table.Group(Source, {"Phone"}, {{"All", each Table.AddIndexColumn(_, "NestedIndex",1,1), type table}}),
TelIndex = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1),
HasMatches = Table.AddColumn(TelIndex, "Custom", each List.Count(List.Union({[All][email], [All][Customer Number], [All][Customer name]})) <> List.Count(List.Distinct(List.Union({[All][email], [All][Customer Number], [All][Customer name]})))),
#"Expanded All" = Table.ExpandTableColumn(HasMatches, "All", {"Customer name", "Customer Number", "email", "NestedIndex"}, {"Customer name", "Customer Number", "email", "NestedIndex"}),
#"Added Custom" = Table.AddColumn(#"Expanded All", "NewIndex", each if [Custom] = false then Text.From([Index])&"-"& Text.From([NestedIndex]) else [Index]),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"NestedIndex", "Index", "Custom"})
in
#"Removed Columns1"
Just replace "Tabelle2" in the Source-step by the name of your query/table.
Question is what shall be returned, if there are more than 2 rows per telephone-number, and only 2 are matching an one is distinct. Using this, code, the non-matching row would also get the same ID. Not sure if this is what you want.
Hi, This IS actually what I am looking for! Will this be "stored forever" in Power BI alongside with the rest of the data? I think I will also change the grouping from group by Phone to Customer Number, to follow the Customer number-email-phone check in that order. Thanks a lot, Marianne
Great & yes, this will be stored and executed everytime you hit the refresh-button :-)