Help assigning subsequent invoice numbers

[katyaking10]

Hello, I have identified the Last Invoice number used - SB.VIN24.000005. How do I assign a sebsequent Invoice number to each row? I need it to be SB.VIN24.000006, SB.VIN24.000007, SB.VIN24.000008 etc. Thank you!

Last No Used	Document Date	Document Type	No.
SB.VIN24.000005	5/25/2024	Invoice	SB.VIN24.000006
SB.VIN24.000005	5/25/2024	Invoice	SB.VIN24.000007
SB.VIN24.000005	5/25/2024	Invoice	SB.VIN24.000008
SB.VIN24.000005	5/25/2024	Invoice	SB.VIN24.000009

[Anonymous · Accepted solution]

Hi @katyaking10 

If you want to add the sebsequent invoice number, you need to have a unique column to rank it, but on the sample data you have offered, you don't have the unqiue column, so it is better that, add an index column in power query first, select from 1, then apply it to power bi desktop.

Then create a calculated column, you can refer to the following code.

Final_No =
VAR a =
    INT (
        RIGHT (
            [Last No Used],
            LEN ( [Last No Used] ) - SEARCH ( ".", [Last No Used], 4 )
        )
    )
VAR b = a + [Index]
RETURN
    LEFT ( [Last No Used], LEN ( [Last No Used] ) - LEN ( b ) ) & b

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.