Generate unique ID from various columns

[tbj1011]

Hello,

I have 8 columns of financial system account data (approx 45 digits total). I need to generate a unique ID of 8 digits for each unique combination of the other 8 columns.

Any suggestions?

Thanks

[AlexisOlson]

It's generally easier to create an index rather than a suitable hash value.

Also, be aware that restricting to 8 decimal digits limits you to no more than 1 million unique IDs, so you'll need to worry about hash collisions if your data is close to that size or larger.

[tbj1011]

I've used indices before - unfortunately, in this case, the 8-digit number needs to be a "shorthand" version of the longer account string so that it can readily identify the account. I shouldn't wind up with more than a few hundred of these IDs - no worries about millions.

[AlexisOlson]

Uniquely representing 8 different structures corresponding to 8 digits won't be possible unless all of these structures have 10 or fewer distinct values. Even if this is the case, you'd have to map, e.g., "52305" and "52210" to a single digit, in which case it won't be a shorthand since you cannot map both of these to "5".

[smpa01]

@tbj1011  is it kindly possible to provide some sample data?

[tbj1011]

Fund                     1000                     1000

Function              03                          03

Department        300                       300

Division                5100                     5100

Function     52210                   52305

Subfunction 005                       001

Location              5100                     5100

Program              0000                     0000

So, these two accounts differ by function and subfunction. However, I have thousands of accounts that differ on every one of the 8 account structures. I need an 8-digit "code" to identify unique accounts.

[smpa01]

@tbj1011  you can try something like this

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcivNS1HSUTI0MDCAUbE6YOHkksz8PKCYgTGEAAm7pBYkFpXkpuaVAMWMwVqMoTpcMssyiyE6TA3BMmAKzTBTIyNDsJyRsYEpWDK4NCkNyTKgKIg0BMv55CcnlmA3M6AoP70oMResGCwDpmJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CAT = _t, Value1 = _t, Value2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CAT", type text}, {"Value1", type text}, {"Value2", type text}}),
    src=Table.RemoveColumns(#"Changed Type",{"CAT"}),
    DH = Table.DemoteHeaders(src),
    ColNames = Record.ToList(DH{0}),
    Loop = List.Generate(
()=>[i=0,j=try Text.Combine(List.Difference(Table.Column(src,ColNames{i}),Table.Column(src,ColNames{i+1}))) otherwise "last"],
each [i]<List.Count(ColNames),
each [i=[i]+1,j=try Text.Combine(List.Difference(Table.Column(src,ColNames{i}),Table.Column(src,ColNames{i+1}))) otherwise "last"],
each [j]
)
in
    Loop

[smpa01]

@tbj1011  so if you have 5 accounts in the dataset , how does the dataset look?