Forum Discussion

tbj1011's avatar
tbj1011
Regular Visitor
4 years ago

Generate unique ID from various columns

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 suggestio...
Need Help
smpa01's avatar
smpa01
Community Champion
4 years ago

tbj1011  is it kindly possible to provide some sample data?

tbj1011's avatar
tbj1011
Regular Visitor
4 years ago

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's avatar
    smpa01
    Community Champion
    4 years ago

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

  • smpa01's avatar
    smpa01
    Community Champion
    4 years ago

    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