Unique Reference Powe Query

Power Query
1

Hello,

I am a basic user of power query. I have a data of bank deposits from my bank statement that comprise of cash deposit in the bank and money received from Merchant payment.

I want to create a unique reference as a new column that will shows me of:

  1. Case No: 1 Deposit slip number as a whole. Example in Deposit Details in Column F row No 17 - 940060151. I want this to come as Unique reference in new column.

  2. Case No: 2 for Alphanumeric deposit slip No example CAM056 row 22. I want only first 3 characters from this slip No in new column.

  3. Case No 3 where deposit slips are combination of delimiter like space or command example 0105, 32592505. I want only last 8 digits of deposit details reference No to be my unique reference No.

4 Case No 4 where deposit slips are combination of delimiter like space or command example ECOMM, 2005. I want only first 5 characters from left deposit slip No to be my unique reference No.

  1. Case No 5 where deposit slips are combination of delimiter like space or command example 37548011 ,1207. I want only first 8 digits from left reference No to be my unique reference No.

I tired to split column in power query but results is not what I want

Please suggest a solution for this
Power Query.xlsx (11.3 KB)

I have attached the data file.

Best Wishes and Thank you in advance.

Antonio

2

Hi @libru255,

Welcome to the Forum!

I’ve created some conditional logic that follows the rules you described. Just paste this code into a new blank query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZbLTh0xDIZf5Yi1kXy3s6RAL2qBCugK8f6vUSeZOcBmSCudRaIzn+3Yf+y8vFy0S7tkZLmAi8Z2ujzd3P5+ePrxXPv6ERNo6wsUQYTGtUZUJVOpP17h5ULfW5Cy8OXq/ufp+fHq/unr7WPtrx9vb3aDkgxstVDxlgLWjSOhwUnYKgK0fzdKpBUmdatpRg4Wtb69fri7gxPj/5hs1gC1WwxuVhb7WsI0kegExBjDqBymzxhH7rBlJdL6uWlgdpx1CsB+HNfACOB2BgkPSVawXiJn1waNVrkk8IGlKPAq5QhiAwtCYF/llEF0csbgi1lhk5ESk9JN6xXmQbF+crSRPQlpDtQ/KiGXSlYCbdjrZ1lxTtUWGoHT7SEZCSNcYo5sDJn93NOp0CFaFcARcmoEAfciGt5j0wXFZSfFiNLBxzeK6EjbeY9h5Qlji48wL8A01CaG6fSRlhWadNKtUv2BXjk1oU/aAz/S8+rTcbFIRuiKGg4jCddXd2i+xIZP1kvKb2yssJYT7bfnDc21kHmyqe/dthW2bWgrpZ1RX1E1JzhNVadIm43p2+MViq/QvQfmhlfBQX3HYwGnrdsM57Xx3OlcoKs6sdPkMa/VoNsC7Q4ou++agyIbrStpK3fzRhfNlfQzPO/V8Qg2BR+BY7muDmY7LAswYcxYi24qoHvOVFdoN8DpuxK+dbRB2wotW9hR0+UctS+QIdusrEdHlExih7db9cn09ITZbdUpYdeYzUpRHGu0EzoXJDll8v3PL2650ofGdap5r1HvB9XJCq30MKl3i9qGq2wdYeC2NPqr9bU5jRllambQvkR7mzl38vLNZzpXaGq6PVhI6lEQO9xWUi40RcYUjdp8u3WalwpGNWk5J56Z8/kz8Jp4r38B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Transaction Type" = _t, Debit = _t, Credit = _t, Balance = _t, #"Deposit Details" = _t]),
    ChType = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Credit", Currency.Type}, {"Balance", Currency.Type}}),
    AddCustom = Table.AddColumn(ChType, "Custom", each 
        if Text.Length(Text.Select( [Deposit Details], {"0".."9"})) = Text.Length([Deposit Details]) 
        then [Deposit Details]
        else if List.Contains( {"A".."Z"}, Text.Start([Deposit Details], 1)) 
        then Text.Select( [Deposit Details], {"A".."Z"}) 
        else List.Select( Text.Split([Deposit Details], " " ), each Text.Length(_)=8 ){0}? 
        ?? [Deposit Details]
    )
in
    AddCustom

There is one item that does not follow any of the rules, its: 50N094 therefore, that item is returned as is
I hope this is helpful

3 Likes