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

GADENSEB · 23 Décembre 2022

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

merinos-BernardEtang · 23 Décembre 2022

Bonjour,

J'ai pas créé toutes les mesures... mais en voici quelques unes qui peuvent aider...

J'ai séparé les table budget (mensuel) et réel... les pas de temps sont differents...

Mérinos

GADENSEB · 24 Décembre 2022

hello,
Merci du Job, Mais je souhaiterais ne pas splitté les tables Budget et Réel,
J'ai refais un modèle pour éclairer plus mon propos.
J'ai fais un peu de P.Q.

Bonne AM
Seb

merinos-BernardEtang · 26 Décembre 2022

Salut @GADENSEB ,

Pour faire cela tu dois employer des filtres dans tes mesures.

Voici un exemple réduit de l'utilisation de filtres.
Tu risques de devoir passer par de nombreuses mesures... "Debit" et "Réel" ...

Je persiste à penser que se sera plus simple de passer par des tables séparées.
Ce n'est pas le même crayon qui remplis le budget et celui qui note les transactions.

Hasco · 26 Décembre 2022

Bonjour,

Voici avec une proposition par powerquery.

VB:

let
    Source = Table.SelectColumns(Excel.CurrentWorkbook(){[Name="Tableau3"]}[Content],{"Compte","Ligne","Budget Reel","DEBIT CREDIT"}),
    #"Lignes groupées" = Table.Group(Source, {"Compte", "Ligne"}, {{"Datas", each _[[Budget Reel],[DEBIT CREDIT]] }}),
    #"Insertion écart" = Table.TransformColumns( #"Lignes groupées",{{"Datas", each Table.InsertRows(_,2,{[Budget Reel = "Ecart",DEBIT CREDIT= _{0}[DEBIT CREDIT ]-_{1}[DEBIT CREDIT ]]})}}),
    #"Datas développé" = Table.ExpandTableColumn(#"Insertion écart", "Datas", {"Budget Reel", "DEBIT CREDIT"}, {"Budget Reel", "DEBIT CREDIT"})
in
    #"Datas développé"

Cordialement

GADENSEB · 26 Décembre 2022

Hello
Merci pour a tous les deux, je regarde cette am ;-)

Amilo · 26 Décembre 2022

Bonjour à tous,

Une proposition Power query/TCD,

Cordialement

GADENSEB · 26 Décembre 2022

GADENSEB à dit:
Hello
Merci pour a tous les deux, je regarde cette am ;-)

GADENSEB · 26 Décembre 2022

Bonjour à vous
Les 3 solutions me conviennent
@merinos comment fais-tu cette partie là des TCD ? je n'arrive pas à comprendre

@Hasco : Super fomule ;-)
= Table.TransformColumns( #"Lignes groupées",{{"Datas", each Table.InsertRows(_,2,{[Budget Reel = "Ecart",DEBIT CREDIT= _{0}[DEBIT CREDIT ]-_{1}[DEBIT CREDIT ]]})}})

@Hasco : Est-ce que l'on peut rajouter de la MFC dans P.Q. ?

@Amilo : Simple et efficace !! Merci

Merci et bonne am à tous

merinos-BernardEtang · 26 Décembre 2022

Salut @GADENSEB

C'est simple: c'est juste une création de mesure. Après il faut connaitre le DAX pour faire les mesure de façon correcte.

Avec des tables liées a un calendrier on peut créer des mesures liées au temps (Total cumulé sur le mois/l'année, croissance d'une année sur l'autre, ...

2 pistes pour le faire:
dans les menus:
et dirrectement dans les pivot:

Tu peux aussi directement corriger les mesures dans les pivot:

.

GADENSEB · 26 Décembre 2022

@merinos merci je n'ai pas encore testé DAX

Hasco · 26 Décembre 2022

GADENSEB à dit:
Est-ce que l'on peut rajouter de la MFC dans P.. ?

La MFC dans PQ non. Power Query manipule des données, mais ne les mets pas en forme.
Par contre vous pouvez dans le tableau résultant comme dans le fichier joint ici.

La requête est modifiée afin que vous puissiez avoir pour chaque couple Compte/Ligne, plusieurs ligne de Budget ou de Reel. Elle prévoit que les lignes Budget ne soient pas forcément devant les lignes Reel.

Modification dernières minutes (17:20)

une autre requête qui "applatit" le fichier après partition des Budget et réels puis repivote le tout
Merci @GADENSEB , je m'amuse bien avec votre fichier

Cordialement

GADENSEB · 26 Décembre 2022

@Hasco merci , je suis entrain de tester la version
content que tu t'amuses ;-)

GADENSEB · 26 Décembre 2022

Re hello
@Hasco : moi aussi je m'amuse bien aussi avec ton code ;-)
Je l'ai replacé dans mon fichier de travail
J'ai fait qq modifs, le fichier de travail n'avait pas tt à fait les mm noms de colonnes que le fichier exemple :
DEBIT CREDIT --> DEBITCREDIT par exemple ...
.......

Dans le code la source est un tableau d'un onglet, ici : Excel.CurrentWorkbook(){[Name="TAB_COMPTES"]}
mais j'aimerai que la source soit une table de PowerQuery appellée "TousComptes"
Je n'arrive pas à modifier le code de la ligne " Source = ........."
--> Comment changer ?

VB:

let
     Source = Table.SelectColumns(Excel.CurrentWorkbook(){[Name="TAB_COMPTES"]}[Content],{"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"

Hasco · 26 Décembre 2022

Re,
Changez le premier argument de Table.SelectColumns par le nom de votre requête source:
De 'Excel.CurrentWorkbook à [Content]' par 'TousComptes'.
Je réponds de mon téléphone. Si problème, je verrai demain.
Bonne soirée

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

GADENSEB

XLDnaute Impliqué

Pièces jointes

merinos-BernardEtang

XLDnaute Accro

Pièces jointes

GADENSEB

XLDnaute Impliqué

Pièces jointes

merinos-BernardEtang

XLDnaute Accro

Pièces jointes

Hasco

XLDnaute Barbatruc

Pièces jointes

GADENSEB

XLDnaute Impliqué

Amilo

XLDnaute Accro

Pièces jointes

GADENSEB

XLDnaute Impliqué

GADENSEB

XLDnaute Impliqué

merinos-BernardEtang

XLDnaute Accro

GADENSEB

XLDnaute Impliqué

Hasco

XLDnaute Barbatruc

Pièces jointes

GADENSEB

XLDnaute Impliqué

GADENSEB

XLDnaute Impliqué

Hasco

XLDnaute Barbatruc

Discussions similaires