How to make a case if for a lot of possibilities

[nicolast29]

Hi

I import balancesheet from my client, here a example

N°  Compte	Libellé	Débit	Crédit	Date
10130000	CAPITAL APPELE ET VERSE		1709604	31/12/2020
10611000	RESERVE LEGALE		170960.4	31/12/2020

I need to add a column, whith the Cycle for each account number (N° compte),

The account number must be between two values (Compte Début & Compte Fin) in order to determine the Cycle

here a sample 

Compte Début	Compte Fin	Cycle	Catégorie_bilan
10000000	14999999	Capitaux propres - Provisions pour risques & charges	Capitaux propres
15000000	15299999	Capitaux propres - Provisions pour risques & charges	Provisions pour risques & charges
15000000	15499999	Capitaux propres - Provisions pour risques & charges	Provisions pour risques & charges
15300000	15399999	Personnel	Personnel
15500000	15599999	Impôts & Taxes	Impôts & Taxes

I have more than 55 possibities, that why i don't know who to do in power bi

Thanks

[nicolast29]

I add a comment

I was making like this under Excel, but as now i want to import the balancesheet directrly under power bi and made the transformation:

Sub test()
Dim ligne As Integer
    ligne = 1
    Do While Cells(ligne, 1).Value <> ""
        Select Case Cells(ligne, 1).Value
        Case 10000000 To 14999999: Worksheets("Balance N").Cells(ligne, 5).Value = "Capitaux propres"
        Case 15000000 To 15299999: Worksheets("Balance N").Cells(ligne, 5).Value = "Provisions pour risques & charges"
        Case 15400000 To 15499999: Worksheets("Balance N").Cells(ligne, 5).Value = "Provisions pour risques & charges"
        Case 15300000 To 15399999: Worksheets("Balance N").Cells(ligne, 5).Value = "Personnel"
        Case 15500000 To 15599999: Worksheets("Balance N").Cells(ligne, 5).Value = "Impôts & Taxes"
        Case 15600000 To 15999999: Worksheets("Balance N").Cells(ligne, 5).Value = "Provisions pour risques & charges"
        Case 16000000 To 16599999: Worksheets("Balance N").Cells(ligne, 5).Value = "Emprunts & dettes"
        Case 16600000 To 16699999: Worksheets("Balance N").Cells(ligne, 5).Value = "Personnel"
        Case 16700000 To 16999999: Worksheets("Balance N").Cells(ligne, 5).Value = "Emprunts & dettes"
        Case 17000000 To 18999999: Worksheets("Balance N").Cells(ligne, 5).Value = "Immobilisations financières"
        Case 20000000 To 20999999: Worksheets("Balance N").Cells(ligne, 5).Value = "Immobilisations incorporelles"
        Case 21000000 To 23999999: Worksheets("Balance N").Cells(ligne, 5).Value = "Immobilisations corporelles"
        End Select
        ligne = ligne + 1
    Loop
End Sub

Thanks

[Icey]

Hi @nicolast29 ,

Have you tried to use USERELATIONSHIP function? It specifies the relationship to be used in a specific calculation as the one that exists between columnName1 and columnName2.

Best Regards,

Icey

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