Adding conditional unique index column based on several criterias

[MarianneElver]

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 unique ID , that is previously tied up to this number

- If the customer no is not recognized, then look up the email , and if this email is recognized, then use the unique ID that is previously tied up to this number.

- If neither the customer no , nor email matches previous records, then lookup phone AND name . If they match, then use existing unique ID , otherways create new unique ID .

The condtional column with the unique ID can either be added in the existingtable, which is a XML file that will be updated occassionally( loaded into Power BI. All columns are expanded). Or, the unique ID can be created in a new, separate table. I have lowercased names and emails in separate columns to ensure match.

The purpose is to always have a column or table with unique index numbers tied to a customer name, so that statistics are based on unique clients.

Is this possible, and if yes, what language would be the best option to use - M or DAX?

Any kind of concrete suggestion is highly appreciated as I have been searching in vain for anything that matches this.

Thanks a lot in advance. Marianne

[Greg_Deckler]

M or DAX is going to depend, can you post some sample data and an idea of your various tables involved? Not even sure what your unique index column is, is it just a unique integer number?

[MarianneElver]

Hi, thanks for yor quick reply! 

Sure, here is a little sample. The first four columns are currently provided and will receive new records when updated, and what I need is an additional column that, by checking for customer number or email, or name and phone, provides the ID that already exists, or creates a new unique interger number:

Customer Number	email	Customer name	Phone	ID
10135000	newname@gmail.com	Benny Johnson	4329463662	1
10031030	mhy@du.com	Pete Madsen	4346123746	2
10056030	pete@gmail.com	Pete Madsen	4346123746	2
10164001	janet.nielsen@gmail.com	Donna D. Nielsen	4346272296	3
10258006	sannypeterson9@gmail.com	Sanny Peterson	4346414642	4
10258032	sannypeterson9@gmail.com	Sanny Peterson	4346414642	4

Hope that clarifies it, otherwise please let me know!

Thanks again,

Marianne

[Anonymous]

Hi @MarianneElver,

You can write a custom function to call the hash generate api, then input these condition columns to generate the unique code.

Regards,

Xiaoxin Sheng

[MarianneElver]

Hi Xiaoxin, Thanks for the idea, unfortunately writing a function is a little ahead of my current capabilities where I'm having trouble finding the right M-code or DAX to figure it out how to come along this challenge! But thanks anyway, Marianne

[ImkeF]

Hi Marianne,

if my understanding of  your request is correct, then PowerBI isn't the right tool for it:

You're importing data in Power BI and want to create a value that sticks to the imported data, also when the next refresh is done?

Problem is, that there is currently no concept for storing such data in PBI. So you would need to export your new table with the newly generated ID's in order for them to be kept ... & reimported & merged with the new data (2nd run), where again new rows with empty ID's will come in.

[MarianneElver]

Hi Imke, Thanks for your reply. I see, would data also not be stored if the generated IDs were fed/added into another table? I no, then is there any other method withing PBI for calculating the unique numbers of customers (and use this number in various other calculations) if the criteria for not counting the same customer twice are met? Thanks in advance, Marianne

[ImkeF]

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.

[MarianneElver]

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

[ImkeF]

Great & yes, this will be stored and executed everytime you hit the refresh-button 🙂

[MarianneElver · Accepted solution]

Thank you so much, looks very useful. I will try to implement this and get back with feedback as soon as possible. Thanks again!