Power Query : optimisation code avec condition IF

Version imprimable

Bonjour,

j'utilise le code ci-après qui est fonctionnel :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
//Identification des éléments de l'actif circulant
if (Text.Start(Text.From([Compte]),2)="33") and [Solde]<0 then "Actif Circulant" else 
if(Text.Start(Text.From([Compte]),2)="39") and [Solde]>0 then "Actif Circulant" else
if(Text.Start(Text.From([Compte]),2)="40") and [Solde]<0 then "Actif Circulant" else
if(Text.Start(Text.From([Compte]),2)="41") and [Solde]<0 then "Actif Circulant" else
if(Text.Start(Text.From([Compte]),2)="42") and [Solde]<0 then "Actif Circulant" else 
if(Text.Start(Text.From([Compte]),2)="43") and [Solde]<0 then "Actif Circulant" else
if(Text.Start(Text.From([Compte]),2)="44") and [Solde]<0 then "Actif Circulant" else
if(Text.Start(Text.From([Compte]),2)="45") and [Solde]<0 then "Actif Circulant" else
if(Text.Start(Text.From([Compte]),2)="46") and [Solde]<0 then "Actif Circulant" else
if(Text.Start(Text.From([Compte]),2)="47") and [Solde]<0 then "Actif Circulant" else
if(Text.Start(Text.From([Compte]),2)="48") and [Solde]<0 then "Actif Circulant"
 else
if(Text.Start(Text.From([Compte]),3)="490") and [Solde]>0 then "Actif Circulant" else 
if(Text.Start(Text.From([Compte]),3)="491") and [Solde]>0
then "Actif Circulant" else 
if(Text.Start(Text.From([Compte]),3)="497") and [Solde]>0
then "Actif Circulant"
   else 
 
//Identification des éléments du Passif Circulant
 
if (Text.Start(Text.From([Compte]),2)="33") and [Solde]>0 then "Passif Circulant" else 
if(Text.Start(Text.From([Compte]),2)="39") and [Solde]>0 then "Passif Circulant" else
if(Text.Start(Text.From([Compte]),2)="40") and [Solde]>0 then "Passif Circulant" else
if(Text.Start(Text.From([Compte]),2)="41") and [Solde]>0 then "Passif Circulant" else
if(Text.Start(Text.From([Compte]),2)="42") and [Solde]>0 then "Passif Circulant" else 
if(Text.Start(Text.From([Compte]),2)="43") and [Solde]>0 then "Passif Circulant" else
if(Text.Start(Text.From([Compte]),2)="44") and [Solde]>0 then "Passif Circulant" else
if(Text.Start(Text.From([Compte]),2)="45") and [Solde]>0 then "Passif Circulant" else
if(Text.Start(Text.From([Compte]),2)="46") and [Solde]>0 then "Passif Circulant" else
if(Text.Start(Text.From([Compte]),2)="47") and [Solde]>0 then "Passif Circulant" else
if(Text.Start(Text.From([Compte]),2)="48") and [Solde]>0 then "Passif Circulant"
 else
if(Text.Start(Text.From([Compte]),3)="490") and [Solde]<0 then "Passif Circulant" else 
if(Text.Start(Text.From([Compte]),3)="491") and [Solde]<0
then "Passif Circulant" else 
if(Text.Start(Text.From([Compte]),3)="497") and [Solde]< 0 then "Passif Circulant" else 
if(Text.Start(Text.From([Compte]),3)="499") and [Solde]> 0 then "Passif Circulant" 
   else " "

Auriez-vous une solution pour l'optimiser, car j'imagine que cela peut-être raccourci ?

Merci d'avance

02/04/2022, 15h46
EricDgn

Bonjour,

Une piste:

Code:

if Text.Contains("33-39-40-42-42-43-44-45-46-47-48-", Text.Start(Text.From([Compte]),2) & "-")> 0 and [Solde]<0 then "Actif Circulant" else

Cordialement.
02/04/2022, 16h06
78chris
Bonjour

Quelques autres pistes :
- Soit créer un table des préfixes et croiser avec la table
- Soit utiliser la conversion en nombre pour travailler sur des groupes :
  
  Code:
  
  if Number.From(Text.Start(Text.From([Compte]),2))>=39 and Number.From(Text.Start(Text.From([Compte]),2))<=48 then ...
02/04/2022, 23h05
Raccourcix

Bonsoir
Attention les lignes 2 et 24 sont identiques

Sinon il est possible également de passer par une table de seuils code et montant
Puis de faire l'union des deux tables, de trier par ordre croissant des codes et des montants.
Ensuite il faut recopier vers le bas la rubrique.

Stephane
02/04/2022, 23h24
Raccourcix

En complément
Toutes les conditions de 40 à 48 peuvent être regroupées puisqu'elles ont la même règle :
Si commence par 4 et ne commence pas par 49 ...

Stéphane
03/04/2022, 01h21
Malick

Bonsoir à tous,

Tout d'abord je vous remercie pour les différentes propositions :ccool:
Je vais les tester toutes et vous revenir pour informer de la suite.

:merci: encore

Citation:

Envoyé par Raccourcix

Bonsoir
Attention les lignes 2 et 24 sont identiques
Stephane

Bien vu :king:
Merci
03/04/2022, 10h26
Raccourcix

Bonjour
Personnellement je ne passerai pas pas une formule conditionnelle sur toutes les lignes de la table. Car il y en a potentiellement beaucoup
J'aurai une table des codes avec deux champs "positif" et "négatif" pour les règles dans chaque cas
Ensuite je ferai une fusion de la table des écritures comptables avec cette table des codes uniquement sur le champ codes. Pour obtenir une colonne avec la table de correspondance.
Sans même avoir besoin de développer cette colonne "correspondance" je créerai une colonne conditionnelle du style

Code:

if [montant] > 0 and not Table.IsEmpty([correspondance]) then [correspondance][positif]{0} else [correspondance][négatif]{0}

Stéphane
03/04/2022, 19h41
Malick

1 pièce(s) jointe(s)

Bonjour,

Citation:

Envoyé par Raccourcix

Bonjour
Personnellement je ne passerai pas pas une formule conditionnelle sur toutes les lignes de la table. Car il y en a potentiellement beaucoup
J'aurai une table des codes avec deux champs "positif" et "négatif" pour les règles dans chaque cas

Effectivement on en a beaucoup et l'idée me plait bien. Cependant, pour les deux champs à créer "Positif" et "Négatif", qu'est ce qu'il faut mettre dans les cellule correspondante ? Je ne comprends pas trop comment les alimenter.

Pièce jointe 616680
03/04/2022, 22h45
Raccourcix

Bonsoir

Citation:

Cependant, pour les deux champs à créer "Positif" et "Négatif", qu'est ce qu'il faut mettre dans les cellule correspondante ? Je ne comprends pas trop comment les alimenter.

Tu indiques dans chaque colonne "Actif" ou "Passif" suivant la condition.

Ensuite tu récupères la liste de tous les codes existants et tu appliques la règle (donc une fois sur chacun, il n'est pas utile de faire les calculs à chaque occurrence du compte)

Enfin tu fusionnes ta base comptable avec cette liste de code pour obtenir le résultat souhaité (en positif ou en négatif).

je vais essayer de préparer un mini exemple demain lorsque j'aurai Excel sous la main.

Stéphane
04/04/2022, 00h19
Malick

Bonsoir,

Citation:

Envoyé par Raccourcix

je vais essayer de préparer un mini exemple demain lorsque j'aurai Excel sous la main.

Stéphane

Merci pour tes explications, ce sera top. Merci encore pour l'aide proposée et bien appréciée.:ccool:
04/04/2022, 08h52
Pierre Fauconnier

1 pièce(s) jointe(s)
Salut Malick,

si j'ai bien compris:
1. Créer une table des rubriques avec le type de compte (actif/passif);
2. Dans Power Query, amener les comptes et les rubriques;
3. Transformer N° de compte et rubriques en valeurs textuelles;
4. Ajouter une colonne rubrique aux comptes pour extraire la rubrique;
5. Fusionner les requêtes sur la colonne commune.
Pièce jointe 616701

1 pièce(s) jointe(s)

Bonjour

Comme convenu voici une illustration de la méthode que je propose

Soit 2 tables que l'on charge dans Power Query
- "Règles" avec les conditions
- "Données" avec des codes et les montants

A partir des "données" on récupère une table des codes uniques. on fait l'union avec les règles, on trie par ordre croissant et on recopie vers le bas ces règles.
On définie ainsi la règle à appliquer pour chaque code

Code:

1
2
3
4
5
6
7
8
9
let
    Source = Données,
    #"Autres colonnes supprimées" = Table.SelectColumns(Source,{"Code"}),
    #"Doublons supprimés" = Table.Distinct(#"Autres colonnes supprimées", {"Code"}),
    #"Requête ajoutée" = Table.Combine({#"Doublons supprimés", Règles}),
    #"Lignes triées" = Table.Sort(#"Requête ajoutée",{{"Code", Order.Ascending}}),
    #"Rempli vers le bas" = Table.FillDown(#"Lignes triées",{"Positif", "Négatif"})
in
    #"Rempli vers le bas"

Ensuite plusieurs possibilités

On fusionne, on développe et on ajoute une colonne conditionnelle

Code:

1
2
3
4
5
6
7
let
    Source = Table.NestedJoin(Données, {"Code"}, Comptes, {"Code"}, "Comptes", JoinKind.LeftOuter),
    #"Comptes développé" = Table.ExpandTableColumn(Source, "Comptes", {"Positif", "Négatif"}, {"Positif", "Négatif"}),
    #"Colonne conditionnelle ajoutée" = Table.AddColumn(#"Comptes développé", "Type", each if [Montant] > 0 then [Positif] else [Négatif]),
    #"Colonnes supprimées" = Table.RemoveColumns(#"Colonne conditionnelle ajoutée",{"Positif", "Négatif"})
in
    #"Colonnes supprimées"

sinon, on peut éviter de développer et on ajouter la condition directement depuis la table

Code:

1
2
3
4
5
6
let
    Source = Table.NestedJoin(Données, {"Code"}, Comptes, {"Code"}, "Comptes", JoinKind.LeftOuter),
    #"Colonne conditionnelle ajoutée" = Table.AddColumn(Source, "Type", each if [Montant] > 0 then [Comptes][Positif]{0} else [Comptes][Négatif]{0}),
    #"Colonnes supprimées" = Table.RemoveColumns(#"Colonne conditionnelle ajoutée",{"Comptes"})
in
    #"Colonnes supprimées"

Ou encore, on dépivote la table des comptes et on remplace "Positif" par 1 et "Négatif" par -1

Code:

1
2
3
4
5
6
7
8
9
10
let
    Source = Comptes,
    #"Lignes filtrées" = Table.SelectRows(Source, each [Commentaire] = null),
    #"Colonnes supprimées" = Table.RemoveColumns(#"Lignes filtrées",{"Commentaire"}),
    #"Supprimer le tableau croisé dynamique des autres colonnes" = Table.UnpivotOtherColumns(#"Colonnes supprimées", {"Code"}, "Attribut", "Valeur"),
    #"Valeur remplacée" = Table.ReplaceValue(#"Supprimer le tableau croisé dynamique des autres colonnes","Positif","1",Replacer.ReplaceText,{"Attribut"}),
    #"Valeur remplacée1" = Table.ReplaceValue(#"Valeur remplacée","Négatif","-1",Replacer.ReplaceText,{"Attribut"}),
    #"Type modifié" = Table.TransformColumnTypes(#"Valeur remplacée1",{{"Attribut", Int64.Type}})
in
    #"Type modifié"

et on ajoute une colonne "Signe" aux données avec Number.Sign pour fusionner sur les codes et les signes

Code:

1
2
3
4
5
6
7
8
let
    Source = Données,
    #"Signe inséré" = Table.AddColumn(Source, "Signe", each Number.Sign([Montant]), Int64.Type),
    #"Requêtes fusionnées" = Table.NestedJoin(#"Signe inséré", {"Code", "Signe"}, #"Comptes - signes dépivotés", {"Code", "Attribut"}, "Comptes - signes dépivotés", JoinKind.LeftOuter),
    #"Comptes - signes dépivotés développé" = Table.ExpandTableColumn(#"Requêtes fusionnées", "Comptes - signes dépivotés", {"Valeur"}, {"Valeur"}),
    #"Colonnes supprimées" = Table.RemoveColumns(#"Comptes - signes dépivotés développé",{"Signe"})
in
    #"Colonnes supprimées"

voici un fichier exemple
Pièce jointe 616702

Stéphane

05/04/2022, 16h01
Malick
Citation:
Envoyé par Pierre Fauconnier

Salut Malick,

si j'ai bien compris:

Créer une table des rubriques avec le type de compte (actif/passif);
Merci pour ton retour.

:fleche: Il manque une petite chose qui chamboule l'astuce en fait et c'est ce qui a suscité la question. En fait, Pour que le compte soit is en Actif circulant ou Passif circulant, il y a le solde du compte (positif ou négatif) ) à prendre en compte. Et suivant le solde, on affecte en Actif circulant ou passif circulant.
05/04/2022, 16h06
Pierre Fauconnier

A part pour les comptes d'attente (499), on laisse les comptes d'actif à l'actif et les comptes de passif au passif (dans le bilan belge, c'est comme cela qu'on faisait lorsque j'étais comptable). Et pour les comptes d'attente, un prof nous avait conseillé de créé deux comptes bilantaires, un à l'actif si total au débit, un au passif si total au crédit, et on passait l'écriture au moment de la clôture.

Il faudrait voir ta table d'écritures pour mieux comprendre la conception du classeur. Pourrais-tu produire une copie d'écran?
05/04/2022, 16h24
Malick

Salut,

Citation:

Envoyé par Pierre Fauconnier

A part pour les comptes d'attente (499), on laisse les comptes d'actif à l'actif et les comptes de passif au passif (dans le bilan belge, c'est comme cela qu'on faisait lorsque j'étais comptable).

:fleche: C'est effectivement cela, parfaitement en phase avec toi :ccool: ==> J'ai mélangé les explication je pense :aie:

En fait, la gestion des positifs ou négatifs pour moi c'était de prendre en compte dans le traitement les soldes anormalement créditeurs ou bien anormalement débiteurs. Un compte passif qui se trouve avec un solde anormalement débiteur ne sera pas intégré dans la rubrique du Passif. ==> tu vois ce que je veux dire.

:fleche: Mais j'ai adopté la démarche que tu as proposée pour d'autres reportings, je vais la mettre en pratique pour ce cas-ci aussi.

:merci: bien
05/04/2022, 16h26
Malick

Bonjour Stéphane,

Citation:

Envoyé par Raccourcix

Bonjour

Comme convenu voici une illustration de la méthode que je propose

Stéphane

Merci beaucoup :ccool: Je regarde cela et essaie également une mise en pratique.

:merci:
05/04/2022, 16h29
Pierre Fauconnier

Citation:

Envoyé par Malick

[...]
:fleche: Mais j'ai adopté la démarche que tu as proposée pour d'autres reportings, je vais la mettre en pratique pour ce cas-ci aussi.[...]

Pour le fun, tu pourrais montrer ton tableau d'écritures ou ta balance (anomymisé(e), bien sûr).
05/04/2022, 16h30
Malick

Citation:

Envoyé par Pierre Fauconnier

Pour le fun, tu pourrais montrer ton tableau d'écritures ou ta balance (anomymisé(e), bien sûr).

Bien noté. Je prépare quelque chose et vous reviens ;)
06/04/2022, 14h50
Malick

Bonjour à tous,

avec le tableau bien paramétré suivant le plan comptable, le croisement avec les comptes de la balance comptable permettent bien d'obtenir le résultat attendu.

Merci beaucoup à tous pour l'intérêt porté à ma requête :king:

https://www.developpez.com/template/kit/kitidea.png POWER QUERY, un outil à connaître et utiliser pour automatiser des tâches récurrentes.