Power Query - Requête + Planning - Correspondance erronée

Bonjour à tous,

Je me suis récemment lancé dans la création d'un fichier Excel me permettant de travailler avec différents collaborateurs.
Avant mon arrivée, chaque collaborateur travaillait avec son propre fichier Excel. Cependant, l'objectif final restait le même, la création d'un calendrier.
J'ai donc rassemblé l'ensemble des fichiers Excel en un seul. Chacun de mes collaborateurs (au nombre de 03) dispose de sa propre page de classeur qu'il peut adapter comme il le souhaite.

J'ai un collaborateur qui s'occupe des renforts traiteurs, un autre, des renforts cuisine et le dernier des renforts boulangerie.
En tant que responsable de cette équipe, mon objectif est de créer une page synthèse avec les informations essentielles dont j'ai besoin pour créer un planning complet des renforts (cuisine, boulangerie, traiteur) en fonction des dates.

Au fur et à mesure que je travaillais sur ce fichier, des fonctions supplémentaires se sont avérées nécessaires. J'ai donc, via youtube, découvert que je pouvais atteindre mon objectif via PowerQuery (que je ne connaissais pas du tout). J'ai également découvert que certaines fonctions utilisées dans les diagrammes de gantt pourraient m'être très utiles. Trouvé dans la rubrique téléchargement - Plannings

Le résultat obtenu n'est pas mal mais il comporte un problème que je n'arrive pas à solutionner.
Je vais essayer d'être le plus clair possible.

La feuille "Synth" est composée de 02 grandes "entités":

1. Le résultat de la query - de la colonne A à la colonne G
2. Un calendrier de la colonne H à la colonne DF

Le résultat de la Query est une extraction des données "Name - Statut - Start Date - End Date - Country - City - PoC" présentes dans les feuilles "Rft Traiteur - Rft Cuisine - Rft boulangerie"

Grâce à ce système, lorsqu'un ajout, une suppression ou une modification est faite (par un collaborateur) dans une des feuilles (Rft Traiteur, cuisine, boulangerie) cela se reproduit automatiquement dans la feuille "Synth".

Le premier problème auquel j'ai du faire face est que lorsque j'effectuais un tri sur la colonne A, B, C, D, E, F ou G les données contenues dans les colonnes H à DF ne correspondaient plus. J'ai donc étendu le tableau du résultat de la Query (Colonne A, B, C, D, E, F et G jusqu'à DF). De cette manière, un tri sur la colonne A,B,C,... engendre automatiquement un suivi des colonnes jusqu'à DF. Raison pour laquelle, on retrouve l'option filtre en dessous de chaque jour... S'il existe une solution plus adaptée, je suis preneur :). De cette manière, un tri sur la colonne A,B,C,... engendre automatiquement un suivi des colonnes jusqu'à DF

Par ailleurs, le plus gros soucis que j'ai actuellement est le suivant :

Lorsque je rajoute une ligne dans une des feuilles (Rft Cuisine, Traiteur, Boulangerie), cette ligne s'ajoute normalement (après actualisation des données) dans la feuille Synth mais produit un décalage au niveau du calendrier...

Exemple concret :

• Étape 1 : Voici la situation avant l'ajoute d'une ligne/enregistrement par un collaborateur :

• Étape 2 : ajout d'une ligne/enregistrement dans la feuille " Rft Traiteur" - Voir ligne 08 :

• Étape 3 : Mise à jour de la requête et résultat :

On constate que "Traiteur Ajout" apparait bien dans la Query mais qu'en s'ajoutant, il provoque un décalage complet au niveau des chiffres qui se trouvaient dans les différentes cellules.
Je souhaite que ces chiffres, une fois associés à une ligne, restent en lien avec cette ligne même après ajout d'un nouvel enregistrement dans une des feuilles (Rft Traiteur, cuisine, boulangerie)

Pour info, ces chiffres me permettent de calculer le nombre de renfort qu'il me reste par jour en fonction des différents enregistrements faits par mes collaborateurs.Il s'agit d'une donnée que j'encode moi-même et qui est varie en fonction du jour. Par exemple cela pourrait être la situation suivante :

J'espère que je suis suffisamment claire dans mes explications. Si ce n'est pas le cas, n'hésitez pas à me poser des questions et j'y répondrai rapidement.
Si jamais, j'ai repéré un post avec un problème similaire dont le titre est : "importation tableau de données, ajout de colonnes, décallage de donnée". Je ne peux pas vous donner le lien, car je n'ai pas encore posté 30 messages... et Oui j'ai fait qqes recherches avant de poster

D'avance, je vous remercie pour le temps que vous m'accorderez afin de trouver une solution à ce soucis.
Je vous souhaite une agréable semaine.

Bonne journée.

Bonjour à tous,

A mon avis, c'est normal, Tes nombres (de quoi ?) arrivent ex nihilo dans ton tableau de synthése (avec des mfc foireuses en passant)sans rapport avec les autres données. L'utilisation d'une colonne supplémentaire pourrait résoudre ce problème, colonne alimentée par une data dans les 3 autres feuilles.

Crdlmt

Bonsoir Djidji,

1. Les nombres correspondent au nombre de personnel que j'attribue par "Renfort" P.ex une personne pendant une durée de 5 jours. Mais cela peut changer en fonction de la date. J'entends par là, une personne les deux premiers jours et puis 4 personnes pour les 03 suivants...
2. MFC foireuses ? c'est à dire ?
3. la colonne supplémentaire : OK mais cela impose alors le même nombre de personne pour toute la période et donc je ne sais plus faire varier le nombre de personne en fonction des jours (comme expliqué au point 1).

Merci pour votre réponse.

Re

mfcs foireuses dans le sens ou 2 mfc suffisent pour tout ton tableau (pour l'instant). Méchanceté gratuite !

Par contre a partir du moment ou il n'y a pas le même nombre de personnes , c'est peut-être la même prestation, mais qui s'étale sur 3 périodes différentes. Si tu veux automatiser, il faut avoir toutes les billes pour le faire.

C'est pour ça, qu'ajouter des colonnes aux trois feuilles atelier ne serait que bénéfique (que je pense! ) pour le tableau de synthése.

D'autre part, c'est super bien expliqué !

Y aura surement d'autres avis.

Crdlmt

Bonjour à tous

Un tableur issu d'une requête n'est pas fait pour être complété manuellement

On peut via le self referencing le faire pour quelques colonnes stables mais là tu ajoutes 103 colonnes à une requête de 7 colonnes et j'imagine que cela va continuer au fil des mois.

Par ailleurs quel est l'intérêt de 3 tableaux Rft au lieu d'un seul précisant la spécialité ?

Il faudrait plutôt noter dans un unique tableau avec les colonnes

Name, Statut, Start Date, End Date, Country, City, PoC
(où, à part le nom, on peut récupérer les autres infos par un RECHERCHEV)

+ une colonne Nombre

et générer le planning dans la requête

Bonsoir à tous,

Un début de fichier avec power query.

Cordialement.

Edit

Bonjour tous

J'ai repris cela à tête reposée : à priori le nombre devrait venir du nombre de noms mais ce n'est pas le cas semble-t'il et il faudrait alors préciser la logique

Ci-joint planning se basant sur le nombre de noms : déjà une requête Synth0 de synthèse des rtf (mais en excluant la requête de synthèse sinon c'est le serpent qui se mord la queue) qui calcule pour chaque jour le nombre de noms
A noter qu'elle traite jusqu'à 6 noms mais on pourrait adapter.

Un calendrier, du 1er du 1er mois à la fin du dernier mois, croisé avec Synth0 affichant le nombre à l'intersection des dates.

Reste les MFC : elles suivent mal les actualisations des tableaux issus de requêtes et nécessitent en général, une reconstruction par VBA à l'actualisation

Bonjour à tous,

Tout d'abord, je tiens à vous remercier (Djidji, Chris & Zebulon) pour le temps que vous m'accordez afin de m'aider à trouver une solution.
Je ne suis en aucun cas un spécialiste excel et encore moins en power Query. Votre aide m'est donc très précieuse.

Je vais essayer de répondre à toutes vos remarques et questions :

1. DjiDji
   1. "MFCS" : mise en forme conditionnelle ? ==> Tu veux dire qu'il y a moyen de concentrer les différentes mise en forme en max 02 MFC ?
   2. "Par contre a partir du moment ou il n'y a pas le même nombre de personnes , c'est peut-être la même prestation, mais qui s'étale sur 3 périodes différentes. Si tu veux automatiser, il faut avoir toutes les billes pour le faire" : ==> je suppose que tu veux dire qu'il va falloir "découper" la période en fonction du nombre de personne ? Si oui, je m'y attendais un petit peu, j'essayais justement d'éviter cela car cela va devenir un casque tête assez important pour mes collaborateurs, étant donné que le nombre de personnes varie énormément... Pour une période globale, je pourrais me retrouver avec 4 ou 5 sous période...
      1. imagine, on parle d'une période globale du 01/01/22 au 30/01/22 (exemple concret)
         1. du 01/01 au 10/01 ==> 02 personnes
         2. du 11/01 au 15/01 ==> 14 personnes
         3. du 16/01 au 18/01 ==> 20 personnes
         4. du 19/01 au 23/01 ==> 10 personnes
         5. du 24/01 au 28/01 ==> 04 personnes
         6. du 29 au 30/01 ==> 01 personnes
   3. "C'est pour ça, qu'ajouter des colonnes aux trois feuilles atelier ne serait que bénéfique (que je pense! ) pour le tableau de synthèse" ==> ajouter des colonnes pour découper la période en fonction du nombre de personne impliquée ?
2. Chris
   1. "J'ai repris cela à tête reposée : à priori le nombre devrait venir du nombre de noms mais ce n'est pas le cas semble-t'il et il faudrait alors préciser la logique" ==> Alors, oui, en effet, les noms (nom 1, nom 2 , nom 3) n'ont rien avoir avec le chiffre que l'on retrouve dans le tableau synthèse. Ils sont à considérer comme des chefs d'équipe. J'avais effectivement penser à automatiser l'encodage du chiffre sur base des noms mais lorsque je sors 35 personnes pour un renfort, je me vois mal devoir remplir 35 colonnes dans la feuilles Rft Traiteur, Cuisine, Boulangerie. Surtout que je peux monter jusqu'à 65 personnes par moment.
   2. "Reste les MFC : elles suivent mal les actualisations des tableaux issus de requêtes et nécessitent en général, une reconstruction par VBA à l'actualisation" ==> VBA est encore plus inconnu pour moi que la Query, peux tu me renseigner ?

Petite info supplémentaire : Ce fichier Excel est une version "édulcorée" de celle que j'utilise cependant, je ne peux vous partager l'originale pour des raisons de RGPD. Cependant, je me rends compte que la version édulcorée est peut-être un peu trop simpliste et ne vous oriente pas dans la bonne direction. Je vais essayer de rediriger le tir :

1. Chaque collaborateur est responsable d'une feuille du classeur. Ils ont chacun un tableau différent qui est "customisé" en fonction de leurs besoins et méthode de travail. Ils ont chacun des entêtes de colonne différentes. La seule chose que chaque feuille du classeur a en commun c'est Name, Statut, Start Date, End Date, Country, City, PoC. Raison pour laquelle j'ai basé ma query sur ces colonnes. C'est aussi les informations dont j'ai essentiellement besoin à mon niveau.
   1. J'ai le collaborateur "rft Traiteur" qui se sert de ce fichier excel comme une simple synthèse de tous les rft qu'il doit fournir. Sachant qu'il reçoit à chaque fois un mail lorsqu'il faut fournir un renfort (=Rft). Moi, je ne vois pas tous les mails, mais grâce à ce document j'ai une vision claire de ce qu'il prépare.
   2. J'ai un autre collaborateur "rft Boulangerie" qui se sert du fichier excel comme d'un véritable outil de préparation et de planification. C'est d'ailleurs chez lui que le tableau comporte 40 colonnes différentes... et ceci sans identifier chaque nom. Imaginez s'il doit prévoir un renfort (=Rft) boulangerie avec 35 personnes. Cela va énormément complexifier son tableau (à 75 colonnes)
2. Le nombre de colonne des feuilles "Rft Traiteur - Rft Cuisine - Rft boulangerie" dans l'exemple Rft-cuisine ne correspond à au nombre de colonnes dans le fichier original. Il faut compter en moyenne 20 à 40 colonnes par feuille dans le fichier original.
3. L'utilisation des chiffres me permet de déterminer les périodes critiques durant lesquelles je serai en manque de personnel et pour lesquelles je ne pourrai plus accepter de demande de renfort. C'est un outil de planification dont j'ai absolument besoin.
4. Je dois garder ce fichier accessible et facile à comprendre car il doit continuer à être alimenter même quand je ne suis pas là. Je ne sais pas la solution PowerQuery + Tableau EST la bonne solution. C'est, ce à quoi je suis arriver jusqu'ici... et c'est probablement la forme la plus aboutie que j'ai pu obtenir jusqu'à présent... sachant que j'y ai déjà consacré une grosse cinquantaine d'heure... Si vous estimez que cela n'est pas la bonne solution, je suis prêt à l'entendre à travailler sur une autre solution.

Je vous souhaite une excellente soirée !

RE

Et un simple tableau de saisie de 3 colonnes Name, Date, Nombre (avec liste déroulante pour la 1ère) que l'on croiserait dans PowerQuery avec les rft pour obtenir un planning comme celui que j'ai proposé ne pourrait pas convenir ?

Hello Chris,

Si je comprends bien ton idée :

1. Créer une nouvelle feuille avec uniquement Name, Date, Nombre. Sachant que Name serait un menu déroulant regroupant l'ensemble des Name présents dans les feuilles Rft Traiteur, Cuisine, Boulangerie
2. Croiser cette nouvelle feuille dans Power Query pour faire correspondre le Nombre avec les données déjà existantes ? Rajouter une colonne Nombre dans les feuilles existantes ne revient pas au même ?

Maintenant, je me demande comment va fonctionner ce système si le Nombre varie durant la Date, qui est souvent une période, et à de rare moment, une journée unique. Ou alors, sous entends-tu qu'il faudra découper une date en sous période ? Un peu comme je l'ai expliqué à DjiDji au au point 2 ?

Bien à toi,

Re

Date c'est un jour

Sachant que l'on peut facilement taper une date et étirer pour créer n jours et que l'on peut saisir dans n cellules un même chiffre en une fois, la saisie est optimisable

Sinon tu peux aussi construire un tableau dans l'autre sens comme tu l'as fait mais pas au bout du résultat de la requête : une colonne Name, et tes jours (en date et non colonne 1, colonne2...)

Et on croise dans PowerQuery...

ok, donc,

"Sachant que l'on peut facilement taper une date et étirer pour créer n jours et que l'on peut saisir dans n cellules un même chiffre en une fois, la saisie est optimisable" donnerait une feuille supplémentaire avec ceci :

qu'il faudrait croiser avec Synth via Power Query ?

RE

Non, on prend les rft et ce tableau et on croise le tout.

Le tout est de savoir quelles colonnes des rft il faut et si les titres de ces colonnes ne sont pas les mêmes il faut une table de correspondance mais s'accorder sur 7 colonnes ne doit pas être insurmontable...

il y a bien 7 colonnes communes à chaque feuille, il s'agit de Name, Statut, Start Date, End Date, Country, City, PoC.

Si je comprends bien, ces 07 colonnes doivent également se retrouver dans le tableau ci-dessous ?

Bonjour

Non on récupère les 6 à partir de la 1ère colonne via PowerQuery ou tu peux aussi les récupérer via une formule RECHERCHEV à partir d'une simple synthèse des rft faite par PowerQuery

Par rapport à ton tableau initial tu saisirais la première et les colonnes des nombres par date et les 6 colonnes entre la 1ère et les dates seraient alimentées par formules grâce à la synthèse PQ : ainsi pas de décalage

Cela suppose que la 1ère colonne n'existe qu'un fois dans la synthèse. Est-ce bien le cas ?

Ok, j'ai repris notre discussion depuis le début et je pense avoir compris vers quoi tu veux aller.
Je vais faire les adaptations dans le fichier et je re post dans la foulée la version modifié.

Merci pour l'aide !

Bon !
J'ai bien capté comment aller chercher les colonnes : Name, Statut, Start Date, End Date, Country, City, PoC dont j'ai besoin. J'ai capté comment créer les dates ( à savoir ce que tu as fait dans la version rft-cuisine-pq2.
Par contre, je n'arrive pas à comprendre comment je dois procéder pour ajouter mes fameux nombres en fonction de la date.

"Cela suppose que la 1ère colonne n'existe qu'un fois dans la synthèse. Est-ce bien le cas ?" oui la colonne NAME n'existe qu'une seule fois dans la feuille Synth

RE

1. Tu fais ta requête Synth avec les colonnes Name, Statut, Start date, End date, Country, City, PoC et tu stockes le résultat dans nouvel un onglet, nommé par exemple Synthèse rtf
2. Tu crées un tableau avec les colonnes Name, Statut, Start date, End date, Country, City, PoC, et les colonnes dates du 1/01/2022 au ...
3. Tu saisis la colonne Name et tes nombres dans les colonnes de date.
4. Pour chacune des colonnes Statut, Start date, End date, Country, City, PoC tu ajoutes une formule à base de RECHERCHEV pointant sur le tableau de l'onglet Synthèse rtf