Ceci est une page optimisée pour les mobiles. Cliquez sur ce texte pour afficher la vraie page.

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

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

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

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

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

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

XLDnaute Impliqué
Hello @Hasco
J'ai fais un fichier "anonyme" et installé ton code (avec qq modifs de noms de colonne pr adapter ;-) )
TT fonctionne parfaitement !!!
Un grand merci à toi

Bonne aprem
 

Pièces jointes

  • PQ-DIFFERENCE DE LIGNES - V4.xlsx
    459 KB · Affichages: 3
Vote positif 0 Vote négatif

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

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

XLDnaute Impliqué
Hello,
J'ai fais le test d'inclure mes datas persos dans le fichier TEST et effectivement, et cela ne fonctionne pas.... j'ai donc un bug dans mes datas perso..... je dois enquêter.
Merci des conseils.

Bon We
Seb
 
Vote positif 0 Vote négatif

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 :


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



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
Identifiez-vous ou inscrivez-vous pour participer.

Discussions similaires

POWERQUERY :: Importation d'un fichier texte .csv et sélection dynamique entre deux dates
Réponses
1
Affichages
279
oguruma
F
  • Question
XL 2016 TCD différence Cumul M vs Cumul M-1
Réponses
8
Affichages
839
faenor86
F
  • Question
Microsoft 365 Problème sur formule de calcul différence entre prévisionnel et réalisé
2
Réponses
15
Affichages
3 K
Membre supprimé 341069
M
  • Résolu(e)
Power Pivot Différence entre Mesure et Champ Valeurs % Total Colonne d'un TCD
Réponses
4
Affichages
1 K
bencheuch
POWERQUERY :: Faire la somme d'un tableau horizontalement et verticalement (somme des lignes, somme des colonnes)
Réponses
0
Affichages
625
oguruma
POWERQUERY :: Combinaisons (Table.Combine) de dossiers contenant des fichiers .CSV ou des fichiers Excel - V2
Réponses
0
Affichages
562
oguruma
E
  • Question
XL 2019 Connecter deux Tableaux ou TCD à un Segment
Réponses
0
Affichages
1 K
EstelleXL
E
  • Question
Microsoft 365 Powerquery - si utilise TCD les requêtes se multiplient
Réponses
6
Affichages
1 K
chris
M
  • Résolu(e)
Power Query Convertir un fichier PdF en Excel et restitution d'un état de synthèse
Réponses
10
Affichages
871
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
1 K
lolo54
L
Menu
Connexion
S'inscrire
Les cookies sont requis pour utiliser ce site. Vous devez les accepter pour continuer à utiliser le site. En savoir plus…