Microsoft 365 TCD ou Powerquery - Différence entre Budget et réel -- Tout bête

GADENSEB

GADENSEB

XLDnaute Impliqué
Bonjour le forum,
je me fait un fichier de budget perso et je cherche à obtenir une différence entre Budget et Réel
j'ai mis un exemple
--> une Bdd alimente un TCD et dans ce TCD (et la BDD) je retrouve une ligne Budget et Réel par catégorie de dépenses.
Je cherche à obtenir la différence entre les deux.
et si possible avec un jeu de couleur (MEC) si en dessous du budget le résultat en vert et en rouge si résultat (Réel>Budget)



Je suis sur que c'est possible via un jeu d'éléments calculés ..... mais je bute....

Pr info; ma BDD est alimentée par un powerquery mais pas dans l'exemple.
donc possible aussi par PowerQuery :)

Est-ce que QQn aurait une idée ?

Joyeux noël en avance

Seb
 

Pièces jointes

  • DIFFERENCE DE LIGNES.xlsx
    14.9 KB · Affichages: 11
Dernière édition:
Trier par date Trier par votes
GADENSEB

GADENSEB

XLDnaute Impliqué
Hello @Hasco
Le code marche nikel
J'aimerai apporter une modif dans le résonnement.
Les lignes qui ressortent sont celles qui ont à la fois sur BUDGET et REEL.

Du coup les lignes qui ont uniquement du REEL ne ressortent pas ...
J'aimerais que le pour les Lignes sans BUDGET apparaissent un montant à zéro comme cela toutes les lignes seront présentes.

c'est faisable ?

bonne journée
SEB

VB: 
let
    Source = TousComptes,
    #"Lignes filtrées" = Table.SelectRows(Source, each ([ANNEE] = "2022") and ([BUDGET REEL] = "REEL") and ([BQ] = "OUI")),
    #"Lignes groupées" = Table.Group(#"Lignes filtrées", {"COMPTE", "DATE", "MOIS", "GROUPE", "LIGNE", "COMMERCE", "MODE RGT"}, {{"TOTAL", each List.Sum([DEBITCREDIT]), type nullable number}}),
    ChoixComptes = Table.SelectRows(#"Lignes groupées", each ([COMPTE] = "ZOLA31")),
    #"Colonnes permutées" = Table.ReorderColumns(ChoixComptes,{"DATE", "COMPTE", "MOIS", "GROUPE", "LIGNE", "COMMERCE", "TOTAL"}),
    #"Personnalisée ajoutée" = Table.AddColumn(#"Colonnes permutées", "DATE2", each [DATE]),
    #"Colonnes permutées1" = Table.ReorderColumns(#"Personnalisée ajoutée",{"DATE", "DATE2", "COMPTE", "MOIS", "GROUPE", "LIGNE", "COMMERCE", "TOTAL"}),
    #"Personnalisée ajoutée1" = Table.AddColumn(#"Colonnes permutées1", "DEBIT", each if [TOTAL]<0 then Number.Abs([TOTAL]) else 0),
    #"Personnalisée ajoutée2" = Table.AddColumn(#"Personnalisée ajoutée1", "CREDIT", each if [TOTAL]>0 then Number.Abs([TOTAL]) else 0),
    #"Colonnes permutées2" = Table.ReorderColumns(#"Personnalisée ajoutée2",{"DATE", "DATE2", "COMPTE", "MOIS", "GROUPE", "LIGNE", "DEBIT", "CREDIT", "COMMERCE", "TOTAL"}),
    #"Colonnes supprimées" = Table.RemoveColumns(#"Colonnes permutées2",{"TOTAL"})
in
    #"Colonnes supprimées"
 
Vote positif 0 Vote négatif
Hasco

Hasco

XLDnaute Barbatruc
Repose en paix
Re,


En théorie Oui

La requête que vous nous donnez n'a pas de point commun (ou si éloigné) avec ce qui a été fait et dit précédement.
De plus n'ayant pas ni accès aux données ni à la requête et ce qu'elle est censée faire, qui n'est plus visiblement l'objet de ce fil, comment voulez-vous que je vous réponde.

Votre requête à au moins 2 étapes de trop. Ne faites faire à power query qu'une seule fois l'ordonnancement des colonnes.
 
Vote positif 0 Vote négatif
GADENSEB

GADENSEB

XLDnaute Impliqué
Heuuu pardon, erreur de débutant je me suis trompé dans le copier coller
J'ai pris une autre requête

Voici le bon code

vraiment désolé

VB: 
let
     Source = Table.SelectColumns(TousComptes,{"COMPTE","LIGNE","BUDGET REEL","DEBITCREDIT","ANNEE","MOIS","BQ"}),
    #"Lignes filtrées" = Table.SelectRows(Source, each ([BQ] = "oui" or [BQ] = "OUI")),
    Partitions = Table.Partition(#"Lignes filtrées","BUDGET REEL",2,each Number.From(_="REEL")),
    Transformation = List.Transform(List.Zip({{"BUDGET","REEL"},Partitions}),each let LaTable = Table.Group(_{1}, {"COMPTE","ANNEE","MOIS","LIGNE"}, {{"DEBITCREDIT", each List.Sum([DEBITCREDIT]), type number}}) in  Table.RenameColumns(LaTable,{"DEBITCREDIT",_{0}}) ),
    Jointure = Table.Join(Transformation{0},{"COMPTE","ANNEE","MOIS","LIGNE"},Transformation{1},{"COMPTE","ANNEE","MOIS","LIGNE"}),
    #"Ecart calculé" = Table.AddColumn(Jointure, "Ecart", each Number.Abs([REEL])-Number.Abs([BUDGET]), type number),
    Pivoter = Table.UnpivotOtherColumns(#"Ecart calculé", {"COMPTE","ANNEE","MOIS","LIGNE"}, "Attribut", "Valeur"),
    #"Colonnes renommées" = Table.RenameColumns(Pivoter,{{"Attribut", "Budget Reel"}, {"Valeur", "DEBIT CREDIT"}})
in
    #"Colonnes renommées"
 
Vote positif 0 Vote négatif
Hasco

Hasco

XLDnaute Barbatruc
Repose en paix
Alors je vais faire comme vous, en plus sympa puisque je vous dis le nom de la requête concernée : Ecart
VB: 
// Ecarts
let
    Source = Table.SelectColumns(Excel.CurrentWorkbook(){[Name="Tableau3"]}[Content],{"Compte","Ligne","Budget Reel","DEBIT CREDIT"}),
    #"Texte en minuscules" = Table.TransformColumns(Source,{{"Budget Reel", Text.Lower, type text}}),
    #"Lignes groupées" = Table.Group(#"Texte en minuscules", {"Compte", "Ligne"}, {{"Datas", each _[[Budget Reel],[DEBIT CREDIT]] }}),
    #"Ligne Manquante ?" = Table.TransformColumns(#"Lignes groupées",{{"Datas", each if Table.RowCount(_)=1 then Table.InsertRows(_,1,{[Budget Reel = if Table.FirstValue(_)="reel" then "budget" else "reel",DEBIT CREDIT = 0]}) else _}}),
    #"Grouperment 2" = Table.AddColumn(#"Ligne Manquante ?", "Sommer", each Table.Group([Datas], {"Budget Reel"}, {{"DEBIT CREDIT", each List.Sum([DEBIT CREDIT]), type number}})),
    #"Lignes triées" = Table.Sort(#"Grouperment 2",{{"Compte", Order.Descending},{"Ligne",Order.Ascending}}),
    #"Insertion écart" = Table.TransformColumns( #"Lignes triées",{{"Sommer", each Table.InsertRows(_,2,{[Budget Reel = "Ecart",DEBIT CREDIT=Number.Abs(_{[Budget Reel="budget"]}[DEBIT CREDIT ])-Number.Abs(_{[Budget Reel="reel"]}[DEBIT CREDIT ])]})}}),
    #"Sommer développé" = Table.ExpandTableColumn(#"Insertion écart", "Sommer", {"Budget Reel", "DEBIT CREDIT"}, {"Budget Reel", "DEBIT CREDIT"}),
    #"Colonnes supprimées" = Table.RemoveColumns(#"Sommer développé",{"Datas"})
in
    #"Colonnes supprimées"

S'il manque une 1 alors elles est ajoutée soit avec reel soit avec budget suivant ce qu'il manque.
 
Vote positif 0 Vote négatif
GADENSEB

GADENSEB

XLDnaute Impliqué
Hello
Je vous souhaite une bonne année 2023 remplie de bons codes VBA, P.Q...... ;-)
Je reviens sur mon fichier

J'ai légèrement modifié le code en rajoutant un filtre sur la colonne "BQ"
Le code fonctionnement parfaitement sur le fichier TEST :
- Si le "BQ" <> de "OUI" ---> le code fonctionne
- S'il manque une ligne "BUDGET" ---> le code fonctionne

Par contre, cela ne fonctionne pas sur le fichier source (+20 000 lignes), impossible de le fournir car trop de données perso.
Bugs constatés :
- S'il manque une ligne "BUDGET" ---> le code ne fonctionne pas

Le fichier source est le mm que le fichier test, j'ai simplement mis des datas neutres en terme de nom

Qu'est-ce que je pourrais vérifier ?
Est-ce qu'il faudrait mettre le filtre BQ=OUI ailleurs dans le code ?

bonne journée
Seb

VB: 
// Ecarts
let
    Source = Table.SelectColumns(TousComptes,{"COMPTE","LIGNE","BUDGET REEL","DEBITCREDIT","BQ"}),
    #"Lignes filtrées" = Table.SelectRows(Source, each ([BQ] = "OUI")),
    #"Texte en minuscules" = Table.TransformColumns(#"Lignes filtrées",{{"BUDGET REEL", Text.Lower, type text}}),
    #"Lignes groupées" = Table.Group(#"Texte en minuscules", {"COMPTE", "LIGNE"}, {{"Datas", each _[[BUDGET REEL],[DEBITCREDIT]] }}),
    #"Ligne Manquante ?" = Table.TransformColumns(#"Lignes groupées",{{"Datas", each if Table.RowCount(_)=1 then Table.InsertRows(_,1,{[BUDGET REEL = if Table.FirstValue(_)="reel" then "budget" else "reel",DEBITCREDIT = 0]}) else _}}),
    #"Grouperment 2" = Table.AddColumn(#"Ligne Manquante ?", "Sommer", each Table.Group([Datas], {"BUDGET REEL"}, {{"DEBITCREDIT", each List.Sum([DEBITCREDIT]), type number}})),
    #"Lignes triées" = Table.Sort(#"Grouperment 2",{{"COMPTE", Order.Descending},{"LIGNE",Order.Ascending}}),
    #"Insertion écart" = Table.TransformColumns( #"Lignes triées",{{"Sommer", each Table.InsertRows(_,2,{[BUDGET REEL = "Ecart",DEBITCREDIT=Number.Abs(_{[BUDGET REEL="budget"]}[DEBITCREDIT ])-Number.Abs(_{[BUDGET REEL="reel"]}[DEBITCREDIT ])]})}}),
    #"Sommer développé" = Table.ExpandTableColumn(#"Insertion écart", "Sommer", {"BUDGET REEL", "DEBITCREDIT"}, {"BUDGET REEL", "DEBITCREDIT"}),
    #"Colonnes supprimées" = Table.RemoveColumns(#"Sommer développé",{"Datas"}),
    #"Type modifié" = Table.TransformColumnTypes(#"Colonnes supprimées",{{"DEBITCREDIT", type number}})
in
    #"Type modifié"
 

Pièces jointes

  • PQ-DIFFERENCE DE LIGNES - V6.xlsx
    464.6 KB · Affichages: 3
Vote positif 0 Vote négatif
Hasco

Hasco

XLDnaute Barbatruc
Repose en paix
Bonjour,

Pour moi tout fonctionne correctement et répond à la question.
Je me souviens d'avoir rajouté l'étape #"Texte en minuscules" parce que la colonne Budget Reel contenait parfois des majuscules parfois non.
Je ne serai pas étonné qu'un problème de la sorte se produise sur votre version réelle si vous dites que la seule chose qui change est la neutralisation des données.
Vérifiez les espaces trainantes et invisibles également.
D'ici je ne peux pas deviner le problème.
 
Vote positif 0 Vote négatif
GADENSEB

GADENSEB

XLDnaute Impliqué
Bonjour,
je fais des tests sur les datas perso
J'ai modifié le code pour avoir les entêtes de colonnes pour les avoir en majuscules comme dans les datas
sur le code de la source et les lignes suivantes

Source = Table.SelectColumns(TousComptes,{"COMPTE","LIGNE","BUDGET REEL","DEBITCREDIT"}),
...................................

code Général :
// Ecarts
let
Source = Table.SelectColumns(TousComptes,{"COMPTE","LIGNE","BUDGET REEL","DEBITCREDIT"}),
#"Texte en minuscules" = Table.TransformColumns(Source,{{"BUDGET REEL", Text.Lower, type text}}),
#"Lignes groupées" = Table.Group(#"Texte en minuscules", {"COMPTE", "LIGNE"}, {{"Datas", each _[[BUDGET REEL],[DEBITCREDIT]] }}),
#"Ligne Manquante ?" = Table.TransformColumns(#"Lignes groupées",{{"Datas", each if Table.RowCount(_)=1 then Table.InsertRows(_,1,{[BUDGET REEL = if Table.FirstValue(_)="reel" then "budget" else "reel",DEBITCREDIT = 0]}) else _}}),
#"Grouperment 2" = Table.AddColumn(#"Ligne Manquante ?", "Sommer", each Table.Group([Datas], {"BUDGET REEL"}, {{"DEBITCREDIT", each List.Sum([DEBITCREDIT]), type number}})),
#"Lignes triées" = Table.Sort(#"Grouperment 2",{{"COMPTE", Order.Descending},{"LIGNE",Order.Ascending}}),
#"Insertion écart" = Table.TransformColumns( #"Lignes triées",{{"Sommer", each Table.InsertRows(_,2,{[BUDGET REEL = "Ecart",DEBITCREDIT=Number.Abs(_{[BUDGET REEL="budget"]}[DEBITCREDIT])-Number.Abs(_{[BUDGET REEL="reel"]}[DEBITCREDIT ])]})}}),
#"Sommer développé" = Table.ExpandTableColumn(#"Insertion écart", "Sommer", {"BUDGET REEL", "DEBITCREDIT"}, {"BUDGET REEL", "DEBITCREDIT"}),
#"Colonnes supprimées" = Table.RemoveColumns(#"Sommer développé",{"Datas"}),
#"Type modifié" = Table.TransformColumnTypes(#"Colonnes supprimées",{{"DEBITCREDIT", type number}})
in
#"Type modifié"
Cliquez pour agrandir...


mais j'ai un message d'erreur comme cela :

1673174023141.png


Voici une V7 avec le code modfié et les datas test qui fonctionne grrrrr
--> donc c'est les datas perso qui déconnes .
grrrr


Est-ce que vous auriez une idée ?
Est-ce que le message d'erreur peut donner une idée ?
quand je swith les datas persos et les datas test je ne viens que faire du remplacement de datas dans l'onglet, je ne touche pas le code.

Bon dimanche
Seb
 

Pièces jointes

  • PQ-DIFFERENCE DE LIGNES - V7.xlsx
    463.9 KB · Affichages: 5
Vote positif 0 Vote négatif

Discussions similaires

F
  • Question
XL 2016 TCD différence Cumul M vs Cumul M-1
Réponses
8
Affichages
665
faenor86
F
LAETI-TOINOU
  • Question
Microsoft 365 Problème sur formule de calcul différence entre prévisionnel et réalisé
2
Réponses
15
Affichages
2 K
Membre supprimé 341069
M
bencheuch
  • Résolu(e)
Power Pivot Différence entre Mesure et Champ Valeurs % Total Colonne d'un TCD
Réponses
4
Affichages
860
bencheuch
bencheuch
oguruma
POWERQUERY :: Faire la somme d'un tableau horizontalement et verticalement (somme des lignes, somme des colonnes)
Réponses
0
Affichages
183
oguruma
oguruma
oguruma
POWERQUERY :: Combinaisons (Table.Combine) de dossiers contenant des fichiers .CSV ou des fichiers Excel - V2
Réponses
0
Affichages
269
oguruma
oguruma
GADENSEB
  • Question
Microsoft 365 Powerquery - si utilise TCD les requêtes se multiplient
Réponses
6
Affichages
859
chris
chris
E
  • Question
XL 2019 Connecter deux Tableaux ou TCD à un Segment
Réponses
0
Affichages
740
EstelleXL
E
M
  • Résolu(e)
Power Query Convertir un fichier PdF en Excel et restitution d'un état de synthèse
Réponses
10
Affichages
451
Merlin258413
M
L
  • Question
XL 2019 calcul de différence du nombre de jours, heures, minutes et secondes entre deux dates
Réponses
3
Affichages
870
lolo54
L
S
  • Question
XL 2016 TCD : comment insérer correctement des données via VBA dans un tableau ?
Réponses
1
Affichages
884
Sebast
S

Membres actuellement en ligne

12 membres et 274 invités.

Partager cette page

Tutos vidéos

Power Query : Ajouter des requêtes (Excel et Power BI)
Créez votre première fonction LAMBDA dans Excel en 3 étapes!
LAMBDA, l’arme secrète de Microsoft pour Excel !
Office Insider - Découvrez les nouveautés Excel avant tout le monde

Statistiques des forums

Discussions
312 184
Messages
2 086 007
Membres
103 088
dernier inscrit
Psodam