Microsoft 365 Rapprochement avec Power Pivot

[bcharef]

Bonjour à toutes et à tous,

Je vous écris pour vous faire part d’une difficulté rencontrée dans mon travail de rapprochement de données sous Excel 365, en utilisant l’outil Power Pivot. Mon projet consiste à comparer deux bases de données, en commençant par une balance comptable des comptes sans doublons des amortissements et un fichier d'immobilisations, liées par une clé de liaison commune. Grâce à Power Pivot, j’ai pu dégager et analyser les écarts entre ces deux sources.

Pour ajuster ces écarts et obtenir un résultat équilibré, j’ai ensuite voulu intégrer une troisième base de données, soit la balance comptable de régularisation ou le journal de régularisations, tout en maintenant la même clé de liaison dans Power Pivot, afin d'atteindre des écarts nuls. Cependant, j’ai rencontré des difficultés pour lier correctement cette nouvelle source dans Excel 365 Power Pivot et réaliser ainsi un rapprochement complet entre les trois bases de données.

Je vous remercie d'avance pour votre aide et vos conseils pour m'aider à surmonter ce problème.

 

[bcharef]

Bonjour Chris,

Merci pour ton retour détaillé. Pour commencer, j’ai uniquement réussi à charger partiellement les données comme suit : j’ai ouvert l’onglet "Données" dans le ruban Excel, sélectionné "Obtenir des données", puis choisi "À partir d’un fichier" > "À partir d’un classeur Excel". Après avoir sélectionné le fichier, j’ai pu charger les données souhaitées.

Cependant, en consultant la base "BalaceAmort" et en vérifiant la feuille "balance", j’ai constaté que la première valeur en F2 était positive (100). J’ai donc modifié cette valeur en -100, mais celle-ci ne semble pas s’être répercutée dans PowerQuery.

Aussi, à l’occasion, pourrais-tu me confirmer si les noms proposés à charger concernent les noms des feuilles ou bien ceux des tables ?

Si tu as une idée de ce qui pourrait expliquer ce manque de mise à jour sur PowerQuery, je serais ravi de recevoir tes conseils.

Merci encore pour ton aide.
Bien cordialement.

 

[bcharef]

Bonjour Chris,

Je souhaitais te faire part des résultats de l’opération de chargement des données. Le chargement a été effectué avec succès comme prévu, et j’ai apporté la modification en F2 en remplaçant 100 par -100 dans la feuille "Balance" du fichier exemple. J’ai également mis à jour cette modification dans l’éditeur PowerQuery. Par ailleurs, le nom de la table a été corrigé, passant de "BalaceAmort" à "BalanceAmort".

Cependant, je constate que la modification a bien été répercutée dans PowerQuery. Pourrais tu m’orienter vers les prochaines étapes à suivre afin de finaliser cette opération ?

Je tiens à te remercier sincèrement pour tous les efforts que tu déploies pour m’aider à maîtriser PowerQuery. Tes conseils sont vraiment précieux et grandement appréciés.

Merci d’avance pour ton aide,
Bien cordialement,

 

[bcharef]

Bonjour Chris,

Je souhaite te solliciter pour obtenir des éclaircissements sur certains points concernant la visualisation des requêtes. Voici les éléments que j’ai pu constater :

• BalanceAmort​

  a. Colonnes supprimées : Il semble qu'il s'agisse de la suppression de la colonne en doublon, mais je voudrais confirmer si c’est bien cela.
  b. Requêtes fusionnées : Je n'ai pas bien saisi cette étape et son fonctionnement.
  c. JrlDeRegul développé : Je ne comprends pas exactement ce que cette action implique.
  d. Requêtes fusionnées1 : Je n'ai pas bien compris cette étape non plus.
  e. Immo développé : Je n’ai pas bien saisi cette modification.

• Immo​

  a. Lignes groupées : Je n'ai pas compris le fonctionnement de cette opération.

• JrlDeRegul​

  a. Aucune modification n’a été apportée, ce qui semble clair.

Additivement aux explications attendues, pourrais tu m'éclairer sur la raison de ces opérations afin que je puisse mieux comprendre la logique derrière chacune d’elles de manière plus rationnelle ? Par ailleurs, pourrais tu m'indiquer comment je dois procéder pour réaliser ces opérations moi-même ?

Je te remercie par avance pour ton aide précieuse et tes explications.

Bien cordialement.

 

[chris]

Bonjour

Noooon

Tu n'as pas à Ouvrir un fichier mais utiliser ce qui est dans le classeur. (Cela alourdit et surtout ne se met pas à jour tant que le fichier n'est pas sauvegardé)
Cette option sert dans d'autres cas.

Depuis une cellule du tableau structuré : Données, à partir d'un tableau.

Il faut peut-être regarder quelques tutos pour les principes de base.

Comme je l'ai expliqué : si tu regardes le détail des étapes tu as la réponse à tes questions.

La notion de requête est celle de toutes les bases de données : on établit un lien entre les tables en choisissant les champs communs et un type de jointure.

Quand on croise des données : il faut développer les champs que l'on souhaite récupérer en utilisant la double flèche à droite du titre de la nouvelle colonne

Le regroupement sert à établir des groupes avec de opérations statistiques appliquées aux groupes (ce que fait un TCD)

Comme dit, tout est fait en cliquant sur les options du ruban : chaque commande peut agir immédiatement ou ouvrir une fenêtre de paramétrage de l'action.

On n'as pas à coder.

 

[bcharef]

Bonjour Chris,

Merci pour tes précisions, elles m’aident beaucoup à mieux comprendre le processus.

Je vois maintenant que je ne dois pas ouvrir un fichier externe, mais bien travailler avec ce qui est dans le classeur, ce qui évite d'alourdir le processus et assure que les données se mettent à jour correctement.

Je vais effectivement explorer quelques tutoriels pour me familiariser davantage avec les principes de base, notamment les notions de requêtes et de jointures. Je comprends mieux maintenant que l’objectif est de lier les tables en utilisant les champs communs et en appliquant le bon type de jointure.

Pour l’étape de fusion, j'ai bien pris note de ta remarque concernant la double flèche pour développer les champs souhaités. Je vais également suivre ton conseil pour le regroupement des données, en utilisant des opérations statistiques similaires à celles d'un TCD.

En effet, comme tu l'as mentionné, je suis en train de charger les tables une par une pour procéder à la fusion et je vais tenter d'éliminer la colonne de liaison, ce qui devrait rationaliser l'ensemble du processus.

Merci encore pour ta patience et pour ton aide précieuse.

Bien cordialement.

 

[chris]

RE

Oui on peut directement lier sur les 3 champs sans cette colonne

 

[bcharef]

Bonjour Chris,

Je vous transmets un fichier dans lequel j’ai ajouté une nouvelle requête. Voici les étapes que j’ai exécutées :

1. J’ai rechargé les données de nouveau, table par table, en sélectionnant "À partir de tableau ou plage", puis je les ai importées dans Power Query en mode connexion seule. Ensuite, j’ai fermé et chargé.
2. Dans la requête BalanceAmort, j’ai supprimé la colonne doublon et la clé de liaison.
3. Dans la requête JrlDeRégul, j’ai également supprimé la clé de liaison.
4. J’ai ensuite créé une nouvelle requête vide pour combiner les données en utilisant l’option Fusionner des requêtes comme nouvelle requête.
5. Lors de la fusion entre BalanceAmort et JrlDeRégul, j’ai sélectionné trois catégories communes dans les deux requêtes : Exercice [1], Unité de production [2], et N° Compte [3], en m’assurant qu’elles correspondaient exactement.

Malgré ces étapes, je ne suis pas certain que ma démarche soit complètement correcte. Pourriez vous, s’il vous plaît, vérifier et m’orienter afin de m’assurer que les corrections nécessaires soient apportées pour atteindre l’objectif visé ?

Merci encore pour votre accompagnement et votre disponibilité. Vos conseils sont d’une grande aide pour maîtriser ces outils.

Bien cordialement,

 

[bcharef]

Bonjour Chris,

Je me permets de vous transmettre à nouveau le fichier afin que vous puissiez vérifier si les requêtes créées sont conformes. J’espère avoir suivi correctement les étapes que vous m’aviez indiquées.

Cependant, j’ai remarqué un point qui m’échappe : les requêtes développées ne portent pas le même nom que le fichier d’origine. Il semble qu’il s’agisse d’une astuce ou d’une règle que je n’ai pas encore assimilée. Je serais très reconnaissant si vous pouviez m’éclairer à ce sujet pour mieux comprendre la logique sous-jacente.

Par ailleurs, je souhaiterais également bénéficier de vos précieux conseils concernant la création d’un tableau croisé dynamique (TCD) à partir des données obtenues via Power Query. Mon objectif est de structurer les informations de manière optimale pour qu’elles reflètent correctement les données fusionnées.

Je vous remercie infiniment pour votre bienveillance et pour le temps que vous consacrez à m’orienter dans la maîtrise de cet outil. Votre expertise est une véritable source d’apprentissage et d’inspiration.

Dans l’attente de votre retour, je vous adresse mes salutations les plus respectueuses.

Bien cordialement,

 

[chris]

RE

Si la clé de liaison existe à l'origine, il est préférable de la garder : une relation est d'autant plus rapide qu'il y a moins de champs à connecter : donc si le source n'a pas la clé, oui, sinon garder cette clé

Le regroupement que j'ai fait pour synthétiser les amortissements permet de ne pas avoir un relation de 1 à n mais de 1 à 1 (ce que fait un TCD PowerPivot) sinon on démultiplie les infos du côté un N fois et ce n'est pas exploitable.

Ou alors :

• ne pas effectuer la seconde fusion dans la Balance,
• balancer cette balance arrêtée avant la seconde fusion et les immos dans le modèle de données
• y faire le lien entre Balance et Immos
• créer le TCD PowerPivot

PS : quand on développe décocher la toute dernière case à coche pour garder le nom d'origine des champs (en cas de nom en doublon il y a un indice mais on peut dans la barre de formule choisir un autre titre)

 

[bcharef]

Bonjour chris,

Suite à vos précieuses remarques, nous avons apporté les corrections nécessaires au fichier que nous vous transmettons ci-joint pour validation.

Nous avons pris soin de :

• Conservez la clé de liaison initiale, comme vous l'avez suggéré, afin d'optimiser la rapidité des relations et de minimiser les champs à connecter.
• Appliquer la logique de regroupement pour synthétiser les amortissements, entraînant ainsi une relation de 1 à n, conformément à vos explications, si nous avons bien compris vos suggestions.

Cependant, certains points doivent encore des éclaircissements pour être pleinement maîtrisés :

1. Vous mentionnez l'option de ne pas effectuer la seconde fusion dans la Balance et d'utiliser un lien entre Balance et Immos directement dans le modèle de données. Pourriez vous détailler la démarche à suivre pour implémenter cette alternative ?
2. Concernant la création du TCD PowerPivot, pourriez vous nous orienter sur les étapes précises à suivre pour relier les deux sources et structurer les informations efficacement ?
3. Enfin, au sujet de l'astuce liée au développement des champs, lorsque vous indiquez de décocher la toute dernière case pour conserver les noms d'origine, pourriez vous préciser l'impact que cela pourrait avoir sur l'exploitation des données ?

Nous espérons avoir interprété correctement vos suggestions et restons disponibles pour toute précision ou correction supplémentaire.

Nous vous remercions chaleureusement pour votre accompagnement et la clarté de vos explications, qui nous permettent d'avancer dans la maîtrise de ces outils complexes. Votre expertise est une aide inestimable.

Dans l'attente de votre retour, veuillez recevoir nos salutations respectueuses.

Bien cordialement,

 

[chris]

RE

Voici

1. l'exemple avec l'alternative
2. un exemple sans PowerPivot

Pour le développement, si on ne décoche pas chaque colonne récupérée de la fusion est préfixée du nom de la table ce qui fait des noms à rallonge plus difficiles à gérer pour les étapes suivantes...

 

[bcharef]

Bonjour Chris,

Merci pour votre retour et les explications fournies concernant les alternatives proposées.

Toutefois, je dois admettre que certains éléments m'échappent encore, notamment en ce qui concerne :

1. L'alternative avec et sans PowerPivot que vous mentionnez.
2. La gestion des colonnes lors du développement, où vous indiquez l'impact de ne pas décocher certains cas, qui reste pour moi difficile à comprendre.

Afin d'avancer efficacement, je souhaiterais procéder pas à pas. Si le fichier que je vous ai transmis dans le poste 25 présente des anomalies ou des éléments nécessitant des corrections, je serais très reconnaissant si vous pouvez me suggérer les modifications ou les améliorations à y apporter.

Votre expertise et vos conseils me seront d'une grande aide pour maîtriser pleinement ces étapes complexes.

En vous remerciant par avance pour votre accompagnement et votre patience, je reste à votre disposition pour toute clarification supplémentaire.

Veuillez recevoir mes salutations respectueuses.

Bien cordialement,

 

[chris]

RE

Quand on développe la 1ère fusion :

Si on ne décoche pas la dernière case Utiliser le nom de la colonne d'origine comme préfixe on aura une colonne nommée JrlDeRegul.Solde sinon on aura Solde.1 mais on peut remplacer dans la barre de formule Solde.1 par ce que l'on veut Regul par exemple (comme je l'avais fait)
Sur ta seconde fusion tu as Immobilisations.Amort alors que Amort suffit largement...

Sur une unique colonne ce n'est pas très grave mais si on en récupère plusieurs c'est à mon avis casse pied ces noms à rallonge : les colonnes vont s'élargir et on va devoir, si beaucoup de colonnes, utiliser tout le temps la barre de translation pour aller voir les colonnes à droite et revenir à gauche.
Également taper ces noms dans des formules ajoute des complications syntaxiques...

De façon générale en informatique je fais très attention aux noms et j'y gagne du temps et de la lisibilité...

Sinon RAS sur ton fichier. Il reste

• soit à supprimer de PowerPivot les anciennes tables, la requête JrlDeRegul (qui ne sert à rien ici) pour ne garder que BalanceAmort et construire le TCD et la mesure
• soit, comme je l'indiquais au #24 :
  • ne pas effectuer la seconde fusion dans la Balance,
  • balancer cette balance arrêtée avant la seconde fusion et la requête Immos dans le modèle de données
  • y faire le lien entre Balance et Immos
  • construire le TCD et la mesure
• soit comme dans le second fichier joint au #26, tout supprimer de PowerPivot et faire le calcul d'écart directement dans PowerQuery (sans besoin de TCD)

 

[bcharef]

Bonjour chris,

Je vous transmets de nouveau le fichier comportant les corrections proposées en matière de Power Query, en espérant que ces ajustements correspondent mieux aux attentes.

Cependant, je rencontre encore des difficultés concernant la création d'un tableau croisé dynamique (TCD) avec Power Pivot. Plus précisément :

1. Je ne parviens pas à ajouter correctement la requête concernée dans le modèle de données.
2. J'ignore comment automatiser le processus afin que les modifications apportées à l'une des trois tables soient directement répercutées dans le TCD sans nécessiter une intervention manuelle.

Le résultat recherché est un TCD Power Pivot permettant une analyse fluide et cohérente des données. Votre éclairage sur la démarche à suivre pour surmonter ces obstacles me serait d'une grande aide.

Je reste à votre disposition pour toute précision ou ajustement supplémentaire.

En vous remerciant d'avance pour votre assistance et votre expertise, veuillez recevoir mes salutations respectueuses.

Bien cordialement.

 

[chris]

Bonjour

Pour ajouter une requête au modèle de données il faut, dans le volet Excel listant les requêtes, effectuer un clic droit sur la requête, Charger dans, Ne créer que la connexion et cocher tout en bas de la fenêtre l'ajout au modèle de données.

Dans votre classeur c'est la requête T_Amort_Cpt qui doit être ajoutée au modèle

Construire le TCD à partir de PowerPivot et ajouter la mesure Ecart

Il suffit d'actualiser le TCD pour qu'il reflète tout changement des sources

A noter que si on construit un TCD depuis PowerQuery on passe par Charger dans, TCD et non de manière classique afin de ne pas dédoubler la requête comme dans votre classeur.

La solution PowerQuery sans TCD est tout aussi dynamique et, comme un TCD, ne nécessite qu'une actualisation du tableau résultant

Si on n'a pas l'utilité de mesures spécifiques dans un TCD, le classeur est alors plus léger (poids du cache du TCD en moins)