Power Query : optimisation code avec condition IF

**Malick** · 02/04/2022, 02h11

Bonjour,

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

**EricDgn** · 02/04/2022, 15h46

Bonjour,

Une piste:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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.

**78chris** · 02/04/2022, 16h06

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 : Sélectionner tout - Visualiser dans une fenêtre à part
```
if Number.From(Text.Start(Text.From([Compte]),2))>=39 and Number.From(Text.Start(Text.From([Compte]),2))<=48 then ...
```

**Raccourcix** · 02/04/2022, 23h05

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

**Raccourcix** · 02/04/2022, 23h24

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

**Malick** · 03/04/2022, 01h21

Bonsoir à tous,

Tout d'abord je vous remercie pour les différentes propositions

Je vais les tester toutes et vous revenir pour informer de la suite.

encore

Envoyé par Raccourcix

Bonsoir
Attention les lignes 2 et 24 sont identiques
Stephane

Bien vu

Merci

**Raccourcix** · 03/04/2022, 10h26

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

Stéphane

**Malick** · 03/04/2022, 19h41

Bonjour,

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.

Nom : Capture.PNG
Affichages : 296
Taille : 11,6 Ko

Nom : Capture.PNG
Affichages : 296
Taille : 11,6 Ko

**Raccourcix** · 03/04/2022, 22h45

Bonsoir

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

**Malick** · 04/04/2022, 00h19

Bonsoir,

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.

**Pierre Fauconnier** · 04/04/2022, 08h52

Salut Malick,

si j'ai bien compris:

Créer une table des rubriques avec le type de compte (actif/passif);
Dans Power Query, amener les comptes et les rubriques;
Transformer N° de compte et rubriques en valeurs textuelles;
Ajouter une colonne rubrique aux comptes pour extraire la rubrique;
Fusionner les requêtes sur la colonne commune.

Nom : 2022-04-04_085033.png
Affichages : 278
Taille : 206,8 Ko

**Raccourcix** · 04/04/2022, 09h07

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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
2022_04_04 Correspondance début code .xlsx

Stéphane

**Malick** · 05/04/2022, 16h01

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.

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.

**Pierre Fauconnier** · 05/04/2022, 16h06

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?

**Malick** · 05/04/2022, 16h24

Salut,

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).

C'est effectivement cela, parfaitement en phase avec toi

==> J'ai mélangé les explication je pense

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.

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.

bien

**Malick** · 05/04/2022, 16h26

Bonjour Stéphane,

Envoyé par Raccourcix

Bonjour

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

Stéphane

Merci beaucoup

Je regarde cela et essaie également une mise en pratique.

**Pierre Fauconnier** · 05/04/2022, 16h29

Envoyé par Malick

[...]

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).

**Malick** · 05/04/2022, 16h30

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

**Malick** · 06/04/2022, 14h50

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

POWER QUERY, un outil à connaître et utiliser pour automatiser des tâches récurrentes.

Power Query : optimisation code avec condition IF

POWER

Discussions similaires

Partager

Partager