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

Hasco · 8 Janvier 2023

Bonjour,

Que voulez-vous qu'on vous dise ? On ne peut pas deviner ce qui se passe ?
Avez-vous fait ce que je vous ai dit au post #29, à savoir parcourir les étapes une à une, voir et nous dire celle qui coince. L'erreur arrive obligatoirement à une étape quelconque, trouvez laquelle.
Quant aux données source vérifiez leurs types (texte à la place de nombre par exemple).

Essayez dans la requête "Ecarts",
Au lieu de

Source = Table.SelectColumns(TousComptes,{"COMPTE","LIGNE","BUDGET REEL","DEBITCREDIT"}),
#"Texte en minuscules" = Table.TransformColumns(Source,{{"BUDGET REEL", Text.Lower, type text}}),

mettez

Source = TousComptes,
Colonnes = Table.SelectColumns(Source,{"COMPTE","LIGNE","BUDGET REEL","DEBITCREDIT"}),
#"Texte en minuscules" = Table.TransformColumns(Colonnes"BUDGET REEL", Text.Lower, type text}}),

La requête Ecarts fait appel à la requête "TousComptes"
Si vous n'avez pas besoin de cette dernière pour autre chose, vous pouvez combiner les deux en une seule.

let
Source = Excel.CurrentWorkbook(){[Name="TousComptes"]}[Content],
Colonnes = Table.SelectColumns(Source,{"COMPTE","LIGNE","BUDGET REEL","DEBITCREDIT"}),
#"Type modifié1" = Table.TransformColumnTypes(Colonnes,{{"DEBITCREDIT", type number}, {"BUDGET REEL", type text}, {"LIGNE", type text}, {"COMPTE", type text}}),
#"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é"

Pour moi tout fonctionne. Et je pense que mes petits camarades on certainement testé sur leur machine et nous aurait signalé le problème s'il y en avait un.

Bon courage

GADENSEB · 9 Janvier 2023

Bonsoir,
Merci des conseils,
J'ai parcouru chaque étape.
et je pense que l'erreur de viens de cette partie du code

#"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 ])]})}})

sur
certaines détails de "Table" ressortent en error ... donc un soucis de format de nombre ?

Bonne soirée
Seb

Hasco · 10 Janvier 2023

Bonjour
Vous avez certainement trouvé.
Sois c'est une erreur de type. Soit vous avez des null qui ne sont rien, ne se comptent pas puis engendrent des erreurs.
Pour remédier à ça remplacer les valeurs null par zéro, dans la requête ou les cellules vides par des zéros également.

Si vous avez des valeurs textuelles telles que "- 1 234.56", corrigez
D'une façon ou d'une autre faites ce qu'il faut pour que vos opérations se fassent sur des nombres

Je viens de voir que vous avez une espace trainante dans le deuxième [DEBITCREDIT ] dans :

#"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 ])]})}})

???

GADENSEB · 10 Janvier 2023

Merci du retour ;-)
Je check.
Bonne journée

GADENSEB · 11 Janvier 2023

Hello
Je faire des test sur les formats et rien ne change.
Pour test, je fais une V9 en fichier test.
et je prends des datas de mon fichier perso (lignes en jaune) qui arrivent en erreur comme dans le post #32.
j'obtiens le même message d'erreur

Donc ce sont bien les datas qui posent soucis.

Quand j’enlève les datas perso (lignes en jaune) le code marche .....
Je vois le bout du tunnel mais que dois-je changer dans le format de mes datas pour que cela fonctionne ?

Bonne am
et merci du soutien.
Seb

Hasco · 11 Janvier 2023

Bonjour,

Si vous aviez bien voulu nous donner cet exemple plus tôt les choses seraient résolues depuis longtemps

Jusqu'à present le groupement par Compte et Ligne donnait des tables de une ou deux lignes, une pour le réel et une pour le budget.
Et nous avons travaillé sur cette base.
Maintenant si vous regardez vos données,

vous verrez que les données pour le compte "Commun" et la ligne "Piscine" ne peuvent donner qu'un groupe avec TROIS lignes ce qui n'est pas prévu.

Dans le premier Groupement il faut rajouter le mois et l'affaire est résolue
.1
Voyez la requête Ecart (2)

Voilà pour moi j'ai répondu largement et plus à la question initiale.
Maintenant à vous de comprendre et de tenter des choses, car jusqu'à présent j'ai été le seul à proposer des solution et vous vous êtes contenté de dire "ça marche pas".

C'est à vous de connaître vos données et ce que vous désirez en faire, pas à nous.

Hasco · 11 Janvier 2023

Re,

Voici une dernière version qui vous groupe par Compte Ligne Mois pour totaliser [DEBITCREDIT] puis Regroupe par Comte Ligne avant d'ajouter les écarts.

Après, je ne sais plus quoi faire pour vous.

GADENSEB · 11 Janvier 2023

Un Grand merci
Tout fonctionne parfaitement.
J'ai enfin compris pourquoi.
Je suis en voiture.
Je fais un retour ce soir

Bonne fin de journée
Seb

GADENSEB · 11 Janvier 2023

en fait c'était tout bête.
Je me suis focalisé sur le soucis de Format nombre.
Mais en fait je n'avais pas compris que dans le code cela fonctionné sur 2 lignes puis on insére l'"écart".
comme la base de Datas fait 20 000 lignes fatalement j'ai plusieurs un "reel" pour le même couple de datas
Compte/Année/Mois/Ligne .....
Pour contourné ce soucis, j'ai donc rajouté une "ligne groupé" en début de code tt de suite aprés avoir appelé la source.

#"Lignes groupées1" = Table.Group(Source, {"COMPTE", "ANNEE", "MOIS", "LIGNE", "BUDGET REEL"}, {{"DEBITCREDIT", each List.Sum([DEBITCREDIT]), type nullable number}}),

Ce qui contourne le soucis
et le tour est joué

J'ai beaucoup à apprendre sur PowerQuery mais cela viendra, chaque chose en son temps.

Encore un grand merci pour le code et l'aide.
Bonne fin de soirée
Seb

let
Source = Table.SelectColumns(TousComptes,{"COMPTE","LIGNE","MOIS","ANNEE","BUDGET REEL","DEBITCREDIT"}),
#"Lignes groupées1" = Table.Group(Source, {"COMPTE", "ANNEE", "MOIS", "LIGNE", "BUDGET REEL"}, {{"DEBITCREDIT", each List.Sum([DEBITCREDIT]), type nullable number}}),
#"Lignes filtrées" = Table.SelectRows(#"Lignes groupées1", each ([BUDGET REEL] = "BUDGET" or [BUDGET REEL] = "REEL")),
#"Texte en minuscules" = Table.TransformColumns(#"Lignes filtrées",{{"BUDGET REEL", Text.Lower, type text}}),
#"Lignes groupées" = Table.Group(#"Texte en minuscules", {"COMPTE", "LIGNE","MOIS","ANNEE"}, {{"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="reel"]}[DEBITCREDIT])-Number.Abs(_{[BUDGET REEL="budget"]}[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é"

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

GADENSEB

XLDnaute Impliqué

Pièces jointes

Hasco

XLDnaute Barbatruc

GADENSEB

XLDnaute Impliqué

Pièces jointes

Hasco

XLDnaute Barbatruc

GADENSEB

XLDnaute Impliqué

GADENSEB

XLDnaute Impliqué

Pièces jointes

Hasco

XLDnaute Barbatruc

Pièces jointes

Hasco

XLDnaute Barbatruc

Pièces jointes

GADENSEB

XLDnaute Impliqué

GADENSEB

XLDnaute Impliqué

Discussions similaires