requête redondante

Accueil – Le CFO masqué Forums Power Query requête redondante

Mots-clés :  requête redondante hiérarchie

  • Ce sujet contient 9 réponses, 2 participants et a été mis à jour pour la dernière fois par Stéphane Lorin, le il y a 3 mois.
10 sujets de 1 à 10 (sur un total de 10)
  • Auteur
    Articles
  • #85330
    sylvain_laurent
    Participant

    Bonjour,

    Je travaille avec une 10aine de tables BDD réseau (OF, clients, produits, commandes, composants, achats, …)
    OF : ordre de fabrication

    Un OF donné peut avoir des OF fils, et cet OF fils peut lui-même être le père d’un autre OF.
    Donc j’ai une hiérarchie d’OF. (père, fils, petit-fils, petit-petit-fils, …)
    Jusqu’à une descendance max à 5 niveaux.
    Attention, il s’agit d’une hiérarchie pyramidale.

    Ma collègue aux achats souhaiterait avoir la liste de la descendance pour un OF donné.
    En pièce jointe, j’ai mis un exemple simple de table avec explication du cas.

    La source de donnée est une table dans Power Query (liée à la BDD) avec une colonne “OF père” et une autre “OF fils”, comme dans l’exemple.
    L’idée est de saisir un n° d’OF (père), et PQ s’occupe de rechercher tous les OF de la descendance et de les mettre dans un tableau pour un post traitement.

    J’ai bidouillé des copies de tables puis fusions de requêtes en cascade, ….
    ça me parait compliqué, il bien y avoir un algo ou une astuce plus simple.

    Merci d’avance pour votre aide.
    Sylvain

    #85382
    Stéphane Lorin
    Participant

    Bonjour

    Vous n’avez pas joint de fichier comme indiqué dans votre message

    Je vous propose une solution en pièce jointe avec une fonction qui filtre la liste Père/Fils sur le champ “Père” suivant un paramètre et renvoie la liste des “Fils” associés

    J’applique cette fonction 5 fois avec comme départ le ou les père(s) choisi(s) par l’utilisateur afin d’avoir les 5 niveaux en gérant les null avec un try … otherwise puis je décroise la table pour obtenir l’ensemble des descendants avec leur niveau respectif.

    Il est peut être possible d’utiliser les fonctions récursives de Power Query avec le @ mais je n’ai pas testé.

    Cordialement

    Stéphane

    Attachments:
    You must be logged in to view attached files.
    #85384
    sylvain_laurent
    Participant

    Merci SL pour la travail.

    Faut que j’analyse tout ça pour comprendre le principe mais en idée la solution me convient.

    Si je traduis ce que je comprends dans mon analyse :
    – La fonction filtre la colonne Père avec le Choix_Filtre.
    – Et le résultats est transformé en une Liste. (là j’ai du mal à comprendre pourquoi une liste ?)
    Donc 1 liste pour le A et 1 liste pour le F
    – Et ensuite tu ajoutes une colonne personnalisée avec appel de la fonction et comme paramètre d’entrée le Niveau 1.
    (ça je comprends)
    Dans la colonne personnalisée, c’est toi qui as ajouté le Try… Otherwise ou bien ça peut se faire en automatique ?

    Quel est l’avantage ou la subtilité de la liste ?

    Et pour créer la fonction, tu as écrit directement le code M ?

    ———————————-
    Pour aller plus loin, je me dis que là j’ai 5 niveaux, mais en réalité il faudrait boucler (comme un “while” en C) sur la fonction tant que l’ensemble de la liste du dernier niveau <> null.
    ça peut se faire ça en langage M ?

    Encore merci.

    #85392
    Stéphane Lorin
    Participant

    Re-bonjour

    J’ai peu de temps pour répondre, je ferme le PC pour l’année 2020 dans quelques minutes !

    Donc la fonction renvoie une liste pour pouvoir facilement développer les lignes ensuite (c’est plus simple qu’une table)

    Oui, j’ai ajouté le try… otherwise à la main pour gérer le cas où la fonction est alimenté par un null ce qui génère une erreur. J’aurai pu faire une condition du style if [niveaux]<>null then fonction else null

    Désolé, j’ai “bidouillé” une solution ce matin en quelques minutes, ce n’est peut être pas très “propre” mais c’est pour donner une piste (c’est l’objet d’un forum)

    La fonction n’est pas créé à la main, c’est une requête avec un paramètre que je transforme en fonction (par clic droit), c’est plus facile pour modifier ensuite cette fonction car la requête et la fonction sont “liées”

    Enfin, pas de boucle type while à ma connaissance dans Power Query, mais il existe des fonctions récursives voir l’aide Microsoft, il faut ajouter un @ devant la fonction.
    https://docs.microsoft.com/fr-fr/powerquery-m/m-spec-functions

    Bon courage et bonne fin d’année

    Stéphane

    #85394
    sylvain_laurent
    Participant

    Merci Stéphane pour ton temps.

    Je regarde la fonction récursive, et à l’occasion, je t’enverrai des news sur le sujet.

    Bonne fin d’année.
    Sylvain.

    #85659
    Stéphane Lorin
    Participant

    Bonjour

    Pour bien commencer ce premier post de l’année, je vous adresse tous mes voeux pour 2021 et en particulier à Sophie qui nous apprends toujours beaucoup de choses. Merci !

    Je reviens sur ce sujet laissé avant les fêtes de fin d’année.

    J’ai testé rapidement des solutions via des fonctions Power Query qui s’appelle elle-même ou un List.Generate.
    Le résultat était correct mais le temps de réponse dès qu’il y avait plus de 3 ou 4 niveaux était trop long.

    J’ai donc essayé la fonction PATH en DAX. Cette fonction est très pratique pour remonter les ascendants dans une relation Père/Fils. A partir d’un élément la recherche du père, du grand-père… est très rapide.
    Ainsi pour connaître les descendants, il suffit de filtrer sur tous les éléments dont un des ascendants est l’OF que l’on cherche.

    Dans mon exemple j’ai donc 2 tables : “Choix” avec le n° de l’OF dont on cherche les descendants et “Liste” qui est la liste des relations Père/Fils (999 OF dans mon exemple)

    Trois colonnes calculées dans la table “Liste”
    Ascendance=PATH([N° Ordre];[N° Ordre père])
    Position dans ascendance=FIND(CONCATENATEX(Choix;[Choix];””);[Ascendance];;0)
    Rang=IF([Position dans ascendance]>0;(FIND([N° Ordre];[Ascendance])-[Position dans ascendance])/8)

    Pour trouver tous les OF qui dépendent d’un autre on commence par créer la liste de tous les ascendants de tous les OF avec la fonction PATH qui renvoie le résultat sous la forme : OF00001 | OF00002 | OF00006
    La fonction CONCATENATEX renvoie l’OF recherché format texte
    La fonction FIND cherche ce texte dans l’ascendance et renvoie la position du texte ou 0 si non trouvé
    Pour obtenir le rang on calcule l’écart entre la position de l’élément en cours (qui est en dernier) et de celui qu’on recherche.
    Comme le nombre de caractères de l’OF est 7 et qu’il y a un séparateur, on obtient l’écart de position en divisant par 8.
    Résultat = (vide) si ce n’est pas un descendant, 0 pour l’OF lui-même, puis 1 pour les fils, 2 pour les petit-fils…

    A chaque changement d’OF, il faut actualiser le TCD. (ajouter une macro événementielle pour automatiser ce dernier point au besoin)

    Cordialement

    Stéphane

    Attachments:
    You must be logged in to view attached files.
    #85661
    sylvain_laurent
    Participant

    Bonjour SL et à tous.

    Tous mes meilleurs voeux pour 2021.

    Merci SL pour ta réponse très concrète.
    J’ai mis en œuvre une solution qui prend beaucoup de temps à faire la recherche.
    Sachant que je suis en lien avec une BDD qui est sur un serveur distant sur notre réseau.
    Temps approximatif 20 secondes pour avoir les OF père-fils…. + d’autres infos liés aux OF.

    Je vais essayer votre solution qui va certainement améliorer la vitesse.

    #85673
    sylvain_laurent
    Participant

    Hello SL.

    J’ai essayé ta proposition sur mon fichier et ça fonctionne à merveille !
    La rapidité est top.

    Maintenant je n’ai pas qu’une table à gérer…

    J’ai, par exemple, une table “Composant” dans laquelle se trouve une liste de composants associés à des OF.
    Donc il y a une relation possible avec les N° d’OF de la hiérarchie.

    A partir de la hiérarchie d’OF que tu as créé, je dois maintenant affecter les composants associés.
    Dans ma application, les relations ne fonctionnent pas et je me retrouve avec la totalité des composants pour chaque OF.
    Avec PowerQuery ça fonctionnait bien donc j’imagine qu’avec Pivot ça devrait le faire.

    Pour l’exemple, j’ai modifié ton fichier et j’y ai ajouté une table “T_Composant” sur la feuille2.
    J’ai ensuite créé une relation dans Pivot et intégrer le composant dans le TCD.
    Résultat : ça fait la même chose. Je dois certainement louper qq chose.

    PS : je ne peux pas vous donner mon fichier directement car il y a des relations avec une BDD externe, désolé.

    Cordialement.
    Sylvain.

    Attachments:
    You must be logged in to view attached files.
    #85675
    sylvain_laurent
    Participant

    Aussi pour ce même projet, peut-on créer un tableau sur une feuille à partir de données provenant de Pivot (notamment de nouvelles colonnes calculées)

    Voir l’exemple en PJ. Je souhaiterai par exemple descendre ce tableau sur une feuille excel et que l’actualisation recalcule et mette à jour le tableau de la feuille.

    Attachments:
    You must be logged in to view attached files.
    #85680
    Stéphane Lorin
    Participant

    Bonjour

    Après actualisation du TCD, pour afficher uniquement les composants des OF il faut ajouter un champ Valeur dans le TCD (somme ou nombre de composants par exemple). Par contre les OF fils sans composant ne sont pas affichés.

    Pour la seconde question, il faut créer une table inversée par le menu Données/Connexions existantes puis l’onglet “Tables”.

    Cordialement

    Stéphane

10 sujets de 1 à 10 (sur un total de 10)
  • Vous devez être connecté pour répondre à ce sujet.