IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

POWER Discussion :

[Power Query] Recherche dans une liste historisée


Sujet :

POWER

  1. #1
    Membre habitué Avatar de LG-69
    Homme Profil pro
    Analyste statisticien
    Inscrit en
    Juillet 2014
    Messages
    162
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : Analyste statisticien

    Informations forums :
    Inscription : Juillet 2014
    Messages : 162
    Points : 189
    Points
    189
    Par défaut [Power Query] Recherche dans une liste historisée
    Bonjour à tous,

    Je vais essayer d'exposer ma problématique le plus clairement possible.

    - Je charge une liste d'enregistrements dont une des colonnes est le nom d'un agent et une autre colonne une date (s'étalant sur les 3 derniers mois)
    - J'ai besoin de savoir si à la date de l'enregistrement l'agent était dans une équipe libellée "Equipe A %" ou une "Equipe B %" ou un autre nom d'équipe (où % correspond à n'importe quels caractères) ceci pour compter mes enregistrement dans le compteur des équipes "Equipe A%" ou celui des équipes "Equipe B%" ou le 3ème compteur fourre-tout

    J'ai par ailleurs une autre table chargée me donnant tous les liens "nom de l'agent / libellé de l'équipe" avec des dates de validité (date de début, date de fin) :
    - Un agent peut passer d'une "Equipe A%" à une "équipe B%" (et suivant la date de mon enregistrement il faut que je le compte dans un des compteurs ou dans un autre)
    - Un agent appartient à plusieurs équipes (normalement pas à "Equipe A%" et "Equipe B%" en même temps)

    Agent 1 - Equipe A toto - 01/01/2022 - 31/01/2022
    Agent 1 - Equipe A bob - 01/01/2022 - 31/03/2022
    Agent 1 - Equipe lambda - 01/01/2022 - 31/06/2022
    Agent 1 - Equipe B pouet - 01/07/2022 - 31/12/2022
    Agent 2 - Equipe B pouet - 03/03/2021 - 06/06/2022
    Agent 3 - Equipe B jojo - 31/12/2020 - 31/12/2021
    ...
    J'avais réussi à faire quelque chose :
    - J'avais la liste des agents appartenant ou ayant appartenu à une équipe de type "Equipe A%"
    - J'avais la liste des agents appartenant ou ayant appartenu à une équipe de type "Equipe B%"
    - Avec un List.Contain de mon agent sur l'une ou l'autre de mes listes ça me permettait de compter sur l'un ou l'autre des compteurs
    Mais avec cette méthode je ne peux pas gérer les changements d'équipes et encore moins le basculement d'une "Equipe A%" vers une "Equipe B%" => je compte dans les 2 compteurs et c'est pas bon
    J'ai envisagé d'avoir une liste d'agents par jour mais sur 3 mois, 2 listes ça me fait 180 listes...
    Ou peut-être de générer une table avec pour tous les jours la composition des équipes puis joindre mes deux requêtes sur la clé "agent / date" mais ça me paraît alambiqué
    il y a peut-être une meilleure solution... ? En fait c'est une sorte de recherche entre deux bornes qu'il me faudrait : j'ai mon agent, j'ai une date, je veux savoir à cette date, suivant les dates de validités de mes liens s'il était dans un groupe d'équipe A% ou dans un groupe d'équipe B% ou ailleurs...

    Toute aide ou piste est la bienvenue.

  2. #2
    Expert éminent sénior

    Profil pro
    Conseil, Formation, Développement - Indépendant
    Inscrit en
    Février 2010
    Messages
    8 514
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations professionnelles :
    Activité : Conseil, Formation, Développement - Indépendant

    Informations forums :
    Inscription : Février 2010
    Messages : 8 514
    Points : 16 443
    Points
    16 443
    Par défaut
    Bonjour

    Pas très clair et pas sûr d'avoir compris ce que tu veux obtenir concrètement

    C'est quoi la date de l'enregistrement la date de chaque ligne ou bien une date unique ?

    Ta liste d'enregistrements contient une seule fois l'agent ou plusieurs fois ?

    La table historique suffit à voir les changement de d'équipe d'un agent

  3. #3
    Membre habitué Avatar de LG-69
    Homme Profil pro
    Analyste statisticien
    Inscrit en
    Juillet 2014
    Messages
    162
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : Analyste statisticien

    Informations forums :
    Inscription : Juillet 2014
    Messages : 162
    Points : 189
    Points
    189
    Par défaut
    Bonjour Chris,

    Chaque enregistrement est en fait un ticket d'incident avec :
    - la référence de l'incident
    - l'agent qui l'a résolu
    - la date de résolution
    - ... et tout un tas d'autres infos

    Donc sur chacun de mes enregistrements j'ai un seul agent (celui qui a résolu qui est potentiellement différent à chaque ligne) et la date de résolution.
    J'ai besoin de savoir à la date de résolution si mon agent était dans une des équipes "Equipes A%" ou dans une des équipes "Equipe B%" ou dans aucune d'entre elles.

  4. #4
    Membre émérite
    Homme Profil pro
    ingénieur
    Inscrit en
    Mars 2015
    Messages
    1 116
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : ingénieur
    Secteur : Finance

    Informations forums :
    Inscription : Mars 2015
    Messages : 1 116
    Points : 2 645
    Points
    2 645
    Par défaut
    Bonjour

    Vous chargez votre table avec les agents, les équipes et les dates de début et de fin dans Power Query
    Puis vous créez une fonction qui donnera la liste des équipes d'un agent à une date donnée

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    (Agent as text, Date as date) as text => Text.Combine(Table.SelectRows(Table_Agents, each [Agent] = Agent and [Début] <= Date and [Fin] >= Date)[Equipe],", ")

    A noter que le 31/06 n'existe pas

    Stéphane

  5. #5
    Membre expérimenté
    Homme Profil pro
    Ingénieur
    Inscrit en
    Août 2010
    Messages
    667
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Essonne (Île de France)

    Informations professionnelles :
    Activité : Ingénieur
    Secteur : Aéronautique - Marine - Espace - Armement

    Informations forums :
    Inscription : Août 2010
    Messages : 667
    Points : 1 432
    Points
    1 432
    Par défaut
    Bonjour,

    Power Pivot me semble bien adapté pour l'exercice avec la mesure suivante:
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    =SUMX(Dates; SUMX(Agents; CALCULATE(COUNTROWS(RELATEDTABLE(Enregistrements));
    				       FILTER(RELATEDTABLE(AgentVsEquipe);
    					   AgentVsEquipe[Date de début] <= Dates[Date]
    					       && AgentVsEquipe[Date de fin] >= Dates[Date]))))
    Pour respecter le modèle en étoile, j'ai créé via Power Query une table pour les dates, une pour les agents et une pour les équipes:
    Nom : Modèle_NbEnregistrementsParEquipe_PowerPivot.png
Affichages : 190
Taille : 19,3 Ko

    Avec cette mesure, tous les sous-totaux fonctionnent bien (par date regroupée ou non et/ou par équipe et/ou par agent):
    Nom : NbEnregistrementsParEquipe_PowerPivot.png
Affichages : 184
Taille : 38,5 Ko

    Si jamais un agent appartenait à deux équipes en même temps (ce qui n'est pas le cas dans mon exemple), l'enregistrement serait compté pour chacune des deux équipes sans pour autant créer de doublon dans les sous-totaux toute équipe.
    J'ai mis plusieurs TCDs en exemple dans le fichier en pièce jointe:
    NbEnregistrementsParEquipe_PowerPivot.xlsx

    Edit: mise à jour du fichier pour gérer le regroupement des équipes.

  6. #6
    Membre habitué Avatar de LG-69
    Homme Profil pro
    Analyste statisticien
    Inscrit en
    Juillet 2014
    Messages
    162
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : Analyste statisticien

    Informations forums :
    Inscription : Juillet 2014
    Messages : 162
    Points : 189
    Points
    189
    Par défaut
    Super, merci à vous pour ces deux solutions. Je vais les analyser, creuser le sujet et reviendrai faire mon rapport ici. Il est possible que ça me prenne quelques jours comme je traite d'autres sujets en même temps. En tout cas merci pour votre réactivité.

  7. #7
    Membre habitué Avatar de LG-69
    Homme Profil pro
    Analyste statisticien
    Inscrit en
    Juillet 2014
    Messages
    162
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : Analyste statisticien

    Informations forums :
    Inscription : Juillet 2014
    Messages : 162
    Points : 189
    Points
    189
    Par défaut
    Bonjour, me voilà de retour après quelques tests.
    Pour le moment je me suis penché sur la solution Query, ne connaissant que très peu Power Pivot.

    Déjà merci Raccourcix ta solution fonctionne après quelques petites modifications.

    - Je n'ai pas besoin de récupérer la liste des équipes d'une personne mais juste de savoir si elle fait partie d'une équipe de type A ou une équipe de type B :
    • J'ai ma liste des équipes topées : équipe de type A ou équipe de type B
    • Je complète ma liste historisée d'équipes avec le top type A et le top type B
    • Je récupère avec la fonction que tu as donnée les tops de chacune des équipes de mon agent à la date souhaitée
    • Suivant si j'ai un top type A ou un top type B la fonction renvoie "Equipe A" ou "Equipe B" ou rien


    Ca marche mais par contre au niveau performances c'est catastrophique, 24000 lignes dans mon fichier, il les charge dans Excel 10 par 10... il faut plus de 30 min pour la mise à jour...

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    (Agent as text, Date as datetime) as text => 
    let
        ListeA = Table.SelectRows(Agents, each [Agent] = Agent and [Début] <= Date and [Fin] >= Date and [Est Equipe A] = "oui")[Equipe],
        ListeB = Table.SelectRows(Agents, each [Agent] = Agent and [Début] <= Date and [Fin] >= Date and [Est Equipe B] = "oui")[Equipe],
        /*Source = Text.Combine(Liste,", ")*/
        Source = if List.IsEmpty(ListeA) then (if List.IsEmpty(ListeB) then "" else "Equipe B") else "Equipe A"
    in
        Source
    Je n'ai pas besoin de récupérer une liste au départ (ListeA et ListeB) mais juste de savoir s'il y a un oui dans le Top "Est Equipe A" ou "Est Equipe B", peut-être qu'en travaillant de ce coté là pour récupérer une valeur et non une liste ça serait plus performant...

    De manière général j'ai l'impression que le travail sur les listes n'est pas très performant

  8. #8
    Membre émérite
    Homme Profil pro
    ingénieur
    Inscrit en
    Mars 2015
    Messages
    1 116
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : ingénieur
    Secteur : Finance

    Informations forums :
    Inscription : Mars 2015
    Messages : 1 116
    Points : 2 645
    Points
    2 645
    Par défaut
    Bonjour
    merci du retour

    en effet, 24000 appels cette fonction c'est long.

    une piste en mettant la table Agents en mémoire avec Table.Buffer
    ajouter une étape avant l'appel à la fonction Table_Agent=Table.Buffer(Agents)
    ajouter un argument à la fonction pour spécifier le nom de la table et faire appel à la fonction en indiquant le troisième argument = Table_Agent sans [] car c'est une étape et non un champ.

    sinon, plutôt que d'avoir des "oui" dans les colonnes "est équipe A" et "est équipe B", pourquoi ne pas avoir une colonne avec la réponse : "A" ou "B" et renvoyer la première ligne de cette colonne. Table.SelectRows(.....)[Equipe]{0}
    on ne ferait qu'un seul test au lieu de 2

    Stéphane

  9. #9
    Membre expérimenté
    Homme Profil pro
    Ingénieur
    Inscrit en
    Août 2010
    Messages
    667
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Essonne (Île de France)

    Informations professionnelles :
    Activité : Ingénieur
    Secteur : Aéronautique - Marine - Espace - Armement

    Informations forums :
    Inscription : Août 2010
    Messages : 667
    Points : 1 432
    Points
    1 432
    Par défaut
    Bonjour,

    Je ne pense pas que tu rencontreras ces problèmes de performance avec le fichier que je t'ai proposé car Power Pivot me semble fait pour ce genre de traitement; à essayer.

  10. #10
    Membre habitué Avatar de LG-69
    Homme Profil pro
    Analyste statisticien
    Inscrit en
    Juillet 2014
    Messages
    162
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : Analyste statisticien

    Informations forums :
    Inscription : Juillet 2014
    Messages : 162
    Points : 189
    Points
    189
    Par défaut
    Bonjour à vous,

    Me voilà de retour après quelques congés et pas mal de boulot au retour.
    Donc finalement j'ai opté pour la solution suivante :
    Préparation :
    - J'ai obtenue une extraction quotidienne de mes liens agents/équipes (depuis le 10/06/2022) que je charge (tous les fichiers, 1 fichier par jour)
    - J'ai ma liste figée des équipes qui m'intéressent topées : équipe de type A ou équipe de type B (les autres équipes je m'en moque)
    - Par le biais de fusion de table je complète la liste des liens agents/équipe avec les fameux top (type A ou type B)
    - Puis je crée une référence sur la liste ci-dessus, je filtre pour n'avoir que les liens concernant une équipe de type A, je supprime le nom de l'équipe, le top (ne gardant que les dates et les noms d'agents), je dédoublonne et là je me retrouve donc pour chaque jour depuis le 10/06/2022 de la liste exhaustive de mes agents appartenant à une équipe de type A
    - Je fais la même chose (une nouvelle référence) pour avoir la liste des agents des équipes de type B (pour chaque jour)

    Utilisation :
    - Dans mon fichier d'incident, celui avec une date et un agent, il ne me reste plus qu'à faire une fusion avec mes listes d'agents de type A (clé : date et nom agent), la même chose sur la liste d'agents de type B (clé : date et nom agent)
    - Suivant si mon agent, à la date recherchée, est dans la liste de type A ou de type B je sais à quel équipe je rattache mon incident. Si je ne le trouve dans aucun des deux types d'équipe c'est que c'est un incident qui ne concerne aucun de ces deux types d'équipe
    - Pour palier au fait que mon historique ne démarre sue le 10/06, si la date à rechercher est < 10/06/2022 alors je cherche à une date par défaut (01/01/2022 et non ç la date réelle de l'incident) pour laquelle j'ai créé une extraction "par défaut" affectant chacun de mes agents à l'une ou l'autre des équipes.

    Ca marche plutôt bien et les fusions de tables m'ont l'air bien plus performantes que des fonctions. Ma seule crainte c'est que mes deux tables d'agents d'équipe de type A et agents d'équipe de type B ne deviennent trop volumineuse, mais j'ai en gros 10 agents dans chacun des type d'équipe, tous les jours, sur 1 an ça va me faire grosso modo 365 * 10 * 2 = 7000 lignes, ça me laisse le temps de voir venir.

    Désolé Promethee25 je n'aurait pas le temps de tester la méthode par power pivot, j'ai plein d'autres tâches à faire et comme j'ai une solution qui marche et qui a l'air performante.... mais je garde l'idée en tête, elle me servira peut-être pour d'autres choses.

    Raccourcix, merci pour l'idée de la fonction, je la garde pour d'autres utilisations.

    Merci encore à vous deux.

  11. #11
    Membre expérimenté
    Homme Profil pro
    Ingénieur
    Inscrit en
    Août 2010
    Messages
    667
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Essonne (Île de France)

    Informations professionnelles :
    Activité : Ingénieur
    Secteur : Aéronautique - Marine - Espace - Armement

    Informations forums :
    Inscription : Août 2010
    Messages : 667
    Points : 1 432
    Points
    1 432
    Par défaut
    Citation Envoyé par LG-69 Voir le message
    Désolé Promethee25 je n'aurait pas le temps de tester la méthode par power pivot, j'ai plein d'autres tâches à faire et comme j'ai une solution qui marche et qui a l'air performante.... mais je garde l'idée en tête, elle me servira peut-être pour d'autres choses.
    Ta solution me semble très complexe par rapport au fichier que j'ai proposé avec ses 4 lignes de DAX mais pas de souci: tu es libre (et il est probablement préférable) d'utiliser ce avec quoi tu te sens le plus à l'aise.

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. [VBA-Excel] Effectuer une recherche dans une liste view
    Par Miles Raymond dans le forum Macros et VBA Excel
    Réponses: 6
    Dernier message: 23/11/2006, 17h21
  2. Imposer une methode Equals pour une recherche dans une List
    Par petozak dans le forum Débuter avec Java
    Réponses: 5
    Dernier message: 03/10/2006, 10h41
  3. Réponses: 2
    Dernier message: 07/07/2006, 10h00
  4. Réponses: 2
    Dernier message: 10/10/2005, 02h25
  5. Recherche dans une liste non trié
    Par Oberown dans le forum Algorithmes et structures de données
    Réponses: 7
    Dernier message: 13/09/2004, 13h56

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo