Power query actualisation assez longue

Le 14/07/2023 à 14:32

Bonjour à tous,

Suite à une aide du membre du forum, j'avais réalisé ces deux fichiers joints qui permettent de déterminer les absences consécutives (l'un avec une fonction, l'autre sans fonction),

Mon problème est la lenteur de l'actualisation de la requête P_finale,

Sur les deux fichiers ça va assez vite, mon problème c'est que rien que pour deux années je dépasse largement les 55 000 lignes sur la base planning , et la ça mets 20 secondes environ à s'actualiser,

Pourriez vous me dire si c'est possible d'améliorer la performance des requêtes, ou d'avoir une approche différente,

Merci d'avance,

Cordialement,

5test-avec-fonction-absences.zip (670.32 Ko)
3test-sans-fonction-absences.zip (616.97 Ko)
Le 14/07/2023 à 18:08

Bonjour à tous !

Une proposition de requête globale, fonction de ma compréhension de votre besoin :

let
    Source = Excel.CurrentWorkbook(){[Name="PLANNING"]}[Content][[USAGER],[UNITE],[GROUPE],[DATE R],[#"P/A"],[#"MOTIF PAIE/FACT"]],
    Tri = Table.Sort(Source,{{"USAGER", Order.Ascending}, {"DATE R", Order.Ascending}}),
    RemplNull = Table.ReplaceValue(Tri,"",null,Replacer.ReplaceValue,{"UNITE", "GROUPE"}),
    VersBas = Table.FillDown(RemplNull,{"UNITE", "GROUPE"}),
    Abs = Table.AddColumn(VersBas, "Abs", each if [#"P/A"] = "Absent" then 1 else 0),
    NomMois = Table.AddColumn(Abs, "Nom du mois", each Date.MonthName([DATE R]), type text),
    FiltreAbsent = Table.SelectRows(NomMois, each ([#"P/A"] = "Absent")),
    GroupBy = Table.Group(FiltreAbsent, {"USAGER", "Nom du mois"}, {{"tbl", each Table.AddIndexColumn(_,"Absences Cumulées",1)}}),
    Combine = Table.Combine( GroupBy[tbl]),
    AjtPrésent = Table.Combine({Combine, Table.SelectRows(NomMois, each ([#"P/A"] = "Présent"))}),
    RemplNull1 = Table.ReplaceValue(AjtPrésent,null,0,Replacer.ReplaceValue,{"Absences Cumulées"}),
    DuréeeAbs = Table.AddColumn(RemplNull1, "DUREE ABS", each if [Absences Cumulées] = 0 then [#"P/A"] else if [Absences Cumulées] > 3 then "Absences > à 72 h" else "Absences < ou = à 72 h"),
    TypeDate = Table.TransformColumnTypes(DuréeeAbs,{{"DATE R", type date}})
in
    TypeDate

Remarques :

  1. Tous les champs de la requête finale sont ou seront utilisés ? Compte tenu de votre TCD ce n'est pas le cas.
  2. Il faudra revoir votre FillDown : Certains usagers se retrouvent avec des informations inappropriées.
Le 14/07/2023 à 20:24

Bonjour JFL,

Merci pour votre retour, oui je ne conserverait que les étiquettes que j'ai besoin lors de l'import dans la source, c'est parce que j'ai besoin de certains champs pour d'autres TCD d’où leur présence dans la requête,

J'ai effectivement revu Filldown dans votre solution, en mettant DateR en type date et en effectuant un tri par Noms et date avant le FillDown,

Par contre j'ai un souci sur les absences cumulées, le chiffre indiqué doit être le cumul des absences d'une période d'absence, il n'y a pas de séparation entre les mois,

Je rejoins le fichier test avec un onglet Test JFL, un extrait de la requête de JFL est a gauche, celle de P_Final a droite et j'ai mis sur le coté les écarts éventuels sur les colonnes essentielles,

On remarque rapidement sur le nom AmoR un écart sur la notion d' absences cumulées,

Merci d'avance pour votre aide,

Cordialement,

6test-sans-fonction-absences.zip (588.21 Ko)
Le 14/07/2023 à 21:35

Bonsoir à tous !

massari59264 a écrit :

Par contre j'ai un souci sur les absences cumulées, le chiffre indiqué doit être le cumul des absences d'une période d'absence, il n'y a pas de séparation entre les mois,

Pouvez-vous expliciter ce que vous entendez par "période d’absence" ? Comment se calcule-t-elle ?

Le 14/07/2023 à 21:53

Bonsoir Jfl,

Merci pour votre retour, j'entends par période d'absence la période consécutive constaté et donc pour chague nom, et sur chaque ligne (afin de déterminer les

absences > ou < = à 3 jours) le nombre total d'absences consécutives,

Je joins un fichier plus explicite,

Merci d'avance,

Cordialement,

2test-sans-fonction-absences.zip (589.97 Ko)
Le 15/07/2023 à 19:06

Bonjour à tous !

massari59264 a écrit :

Merci pour votre retour, j'entends par période d'absence la période consécutive constaté et donc pour chague nom, et sur chaque ligne (afin de déterminer les

absences > ou < = à 3 jours) le nombre total d'absences consécutives,

Je comprends que l'aspect consécutif n'est pas lié aux dates mais aux enregistrements typés en "Absent". Vous confirmez ?

Le 15/07/2023 à 19:39

Bonsoir jfl,

Cest lié aux dates d'absences mais comme on tri les donnes en fonction des dates et des noms, effectivement on peut considéré laspect consecutif lié aux donnes absences , tout en distinguant le changement si ce nest pas le meme nom .

je ne sais pas si je se suis assez claire dans mes explications ...

merci d'avance,

cordialement

Le 15/07/2023 à 21:20

Bonsoir à tous de nouveau !

Une version amendée ! (A tester...)

let
    Source = Excel.CurrentWorkbook(){[Name="PLANNING"]}[Content][[USAGER],[UNITE],[GROUPE],[DATE R],[#"P/A"],[#"MOTIF PAIE/FACT"]],
    Filtre = Table.SelectRows(Source, each ([DATE R] <> null and [#"P/A"]<> null)),
    TypeChamps = Table.TransformColumnTypes(Filtre,{{"USAGER", type text}, {"DATE R", type date}}),
    Tri = Table.Sort(TypeChamps,{{"USAGER", Order.Ascending}, {"DATE R", Order.Ascending}}),
    RemplNull = Table.ReplaceValue(Tri,"",null,Replacer.ReplaceValue,{"UNITE", "GROUPE"}),
    VersBas = Table.FillDown(RemplNull,{"UNITE", "GROUPE"}),
    ValRempl_PA = Table.ReplaceValue(VersBas,null,"Présent",Replacer.ReplaceValue,{"P/A"}),
    GroupBy = Table.Group(ValRempl_PA, {"USAGER", "P/A"}, {{"Nombre", each Table.RowCount(_), Int64.Type}, {"tbl", each _}},GroupKind.Local),
    Expand = Table.ExpandTableColumn(GroupBy, "tbl", List.Select(Table.ColumnNames(ValRempl_PA), each _<>"USAGER" and _<>"P/A")),
    DuréeeAbs = Table.AddColumn(Expand, "DUREE ABS", each if [#"P/A"] = "Présent" then [#"P/A"] else if [Nombre] > 3 then "Absences > à 72 h" else "Absences < ou = à 72 h"),
    TypeDate = Table.TransformColumnTypes(DuréeeAbs,{{"DATE R", type date}}),
    Mois = Table.AddColumn(TypeDate, "Mois", each Date.Month([DATE R]), Int64.Type),
    NomMois = Table.AddColumn(Mois, "Nom du mois", each Date.MonthName([DATE R]), type text),
    Année = Table.AddColumn(NomMois, "Année", each Date.Year([DATE R]), Int64.Type)
in
    Année
Le 15/07/2023 à 22:09

Bonsoir JFL,

Merci pour votre retour, c'est parfait je vais m'y plonger pour comprendre vos étapes, mais le résultat est au rendez vous, c'est parfait,

J'aurai une autre demande sur cette même base afin de comparer les résultats données par cette deuxième méthode :

est il possible pour déterminer les + ou - 72 h de considérer que : pour chaque nom les trois premières absences consécutives sont < à 72 h et au delà de trois, à partir donc du 4 ème jour consécutif on qualifie à partir de celui la les absences comme supérieures à 72 h ? si il a une période de présence entre deux périodes d'absence, on réapplique cette règle pour chaque période?

Merci d'avance,

Bonne vacances à vous,

Cordialement,

Le 16/07/2023 à 10:17

Bonjour JFL,

J'ai cherché de mon côté, et pour la deuxième méthode (inspirait de votre proposition) donnerait cette partie de code modifié :

    GroupBy = Table.Group(VersBas, {"USAGER", "P/A"}, {{"tbl", each _}},GroupKind.Local),
    #"Personnalisée ajoutée" = Table.AddColumn(GroupBy, "Index", each Table.AddIndexColumn([tbl] ,"Index",1)),
    #"Index développé" = Table.ExpandTableColumn(#"Personnalisée ajoutée", "Index", {"UNITE", "GROUPE", "DATE R", "MOTIF PAIE/FACT", "Index"}, {"UNITE", "GROUPE", "DATE R", "MOTIF PAIE/FACT", "Index.1"}),
    #"Colonnes supprimées" = Table.RemoveColumns(#"Index développé",{"tbl"}),

Qu'en pensez vous?, peut on mieux l'écrire?,

Merci d'avance,

Cordialement,

Le 16/07/2023 à 18:03

Bonjour à tous !

Je reviens sur le souci signalé précédemment (FillDown) : Certains usagers se retrouvent avec des informations inappropriées.

Vue partielle de l'étape "RemplNull" :

image

La même vue partielle de l'étape "VersBas" :

image

Vous remarquerez que le premier enregistrement de l'usager BenL est affecté des informations de l'usager BegE ! (Parfaitement logique)

Pour remédier à cette fâcheuse situation (), et sous réserve d'une stabilité des usagers au cours de la période analysée, je vous propose d'étudier la version 3.

La création d'une table de correspondance tirée de la source donne, via une jointure, l’affectation (UNITE et GROUPE) à chacun des usagers.

Cette version 3 intègre aussi la différenciation de l'absence <=72h et >72h. (C'est, me semble-t-il, ce que j'avais proposé dans la version 1 !)

Version 3 (à tester !) :

let
    Source = Excel.CurrentWorkbook(){[Name="PLANNING"]}[Content][[USAGER],[UNITE],[GROUPE],[DATE R],[#"P/A"],[#"MOTIF PAIE/FACT"]],
    Filtre = Table.SelectRows(Source, each [DATE R] <> null),
    TabCorrespAff = let
                        a= Table.Group(Filtre, {"USAGER"}, {{"UNITE", each List.Max([UNITE]), type text}, {"GROUPE", each List.Max([GROUPE]), type text}})
                    in
                        Table.SelectRows(a, each ([UNITE] <> "")),
    TypeChamps = Table.TransformColumnTypes(Filtre,{{"USAGER", type text}, {"DATE R", type date}}),
    Tri = Table.Sort(TypeChamps,{{"USAGER", Order.Ascending}, {"DATE R", Order.Ascending}}),
    ValRempl_PA = Table.ReplaceValue(Tri,null,"Présent",Replacer.ReplaceValue,{"P/A"}),
    GroupBy = Table.Group(
        ValRempl_PA,
        {"USAGER", "P/A"},
        {
            {"Nombre", each Table.RowCount(_), Int64.Type},
            {"tbl", each Table.AddIndexColumn(_,"DUREE ABS",1), type table [DATE R=nullable date, #"MOTIF PAIE/FACT"=text]}
        },
        GroupKind.Local
        ),
    Jointure = Table.NestedJoin(GroupBy, {"USAGER"}, TabCorrespAff, {"USAGER"}, "GroupBy", JoinKind.Inner),
    Expand = Table.ExpandTableColumn(Jointure, "tbl", {"DATE R", "MOTIF PAIE/FACT","DUREE ABS"}),
    ExpandTCAff = Table.ExpandTableColumn(Expand, "GroupBy", {"UNITE", "GROUPE"}),
    DuréeeAbs = Table.ReplaceValue(
        ExpandTCAff,
        each [DUREE ABS],
        each if [#"P/A"] = "Présent" then [#"P/A"] else if [DUREE ABS] > 3 then "Absences > à 72 h" else "Absences < ou = à 72 h",
        Replacer.ReplaceValue,
        {"DUREE ABS"}
        ),
    Mois = Table.AddColumn(DuréeeAbs, "Mois", each Date.Month([DATE R]), Int64.Type),
    NomMois = Table.AddColumn(Mois, "Nom du mois", each Date.MonthName([DATE R]), type text),
    Année = Table.AddColumn(NomMois, "Année", each Date.Year([DATE R]), Int64.Type)
in
    Année

Les différences constatées proviennent, a priori, de la nouvelle méthode d'affectation appliquée.

Le 16/07/2023 à 21:56

Bonsoir Jfl,

Merci pour votre retour, la solution par la table de correspondance n'est pas possible car en effet l'affectation des noms dans le temps est sucpetible de changer,

J'ai utiliser cette version que j'ai réalisé en renommant dans le planning les champs UNITE et GROUPE par UNITE R et GROUPE R puis ajouter deux colonnes conditionnelles. Pour la suite en reprenant une partie de votre code proposé :

let
    Source = Excel.CurrentWorkbook(){[Name="PLANNING"]}[Content][[USAGER],[UNITE T],[GROUPE T],[UNITE R],[GROUPE R],[DATE R],[#"P/A"],[#"MOTIF PAIE/FACT"]],
    ValRempl_null= Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"UNITE T", "GROUPE T", "UNITE R", "GROUPE R", "MOTIF PAIE/FACT"}),
    ColCond_Groupe = Table.AddColumn(ValRempl_null, "GROUPE", each if [GROUPE R] = null then [GROUPE T] else [GROUPE R]),
    ColCond_Unite = Table.AddColumn(ColCond_Groupe, "UNITE", each if [UNITE R] = null then [UNITE T] else [UNITE R]),
    FillDown = Table.FillDown(ColCond_Unite,{"GROUPE", "UNITE"}),
    Supp_Col_T_R = Table.RemoveColumns(FillDown,{"UNITE T", "GROUPE T", "UNITE R", "GROUPE R"}),
    Filtre = Table.SelectRows(Supp_Col_T_R, each ([DATE R] <> null and [DATE R] <> "" and [#"P/A"] = "Présent" or [#"P/A"] = "Absent")),
    TypeChamps = Table.TransformColumnTypes(Filtre,{{"USAGER", type text}, {"DATE R", type date}}),
    Tri = Table.Sort(TypeChamps,{{"USAGER", Order.Ascending}, {"DATE R", Order.Ascending}}),
    ValRempl_PA = Table.ReplaceValue(Tri,null,"Présent",Replacer.ReplaceValue,{"P/A"}),
    GroupBy = Table.Group(
        ValRempl_PA,
        {"USAGER", "P/A"},
        {
            {"Nombre", each Table.RowCount(_), Int64.Type},
            {"tbl", each Table.AddIndexColumn(_,"DUREE ABS",1), type table [DATE R=nullable date, #"MOTIF PAIE/FACT"=text]}
        },
        GroupKind.Local
        ),
    Expand = Table.ExpandTableColumn(GroupBy, "tbl", {"UNITE","GROUPE","DATE R", "MOTIF PAIE/FACT","DUREE ABS"}),
    DuréeeAbs = Table.ReplaceValue(
        Expand,
        each [DUREE ABS],
        each if [#"P/A"] = "Présent" then [#"P/A"] else if [DUREE ABS] > 3 then "Absences > à 72 h" else "Absences < ou = à 72 h",
        Replacer.ReplaceValue,
        {"DUREE ABS"}
        ),
    ValRempl_null2 = Table.ReplaceValue(DuréeeAbs,null,"Présences",Replacer.ReplaceValue,{"MOTIF PAIE/FACT"}),
    Mois = Table.AddColumn(ValRempl_null2, "Mois", each Date.Month([DATE R]), Int64.Type),
    NomMois = Table.AddColumn(Mois, "Nom du mois", each Date.MonthName([DATE R]), type text),
    Année = Table.AddColumn(NomMois, "Année", each Date.Year([DATE R]), Int64.Type)
in
    Année

Pour gagner en rapidité dans le traitement, est il possible, dés la source, de faire apparaitre les col UNITE et GROUPE qui tiennent compte des conditions écrites sur les lignes ColCond_Groupe et ColCond-Unite?,

Merci d'avance pour votre collaboration et du temps passé,

Cordialement,

Le 16/07/2023 à 22:13

Bonsoir à tous de nouveau !

massari59264 a écrit :
Pour gagner en rapidité dans le traitement, est il possible, dés la source, de faire apparaitre les col UNITE et GROUPE qui tiennent compte des conditions écrites sur les lignes ColCond_Groupe et ColCond-Unite?,

Le retraitement, l'enrichissement, etc.... des données suppose que celles-ci soient préalablement chargées. On ne peut agir en même temps sur le chargement et sur le retraitement.

Pouvez-vous poster vos nouvelles données ?

Le 18/07/2023 à 08:44

Bonjour @JFL,

Désolé pour le retour tardif, je joins le fichier actualisé,

Merci d'avance pour votre avis,

Cordialement,

1test-sans-fonction-absences.zip (874.92 Ko)
Le 18/07/2023 à 10:24

Bonjour à tous !

Le FillDown utilisé ne règle pas, à mon avis, l’éventualité d’affectation erronée d’informations liées à un autre usager. (Le dernier jeu d’essai ne permet pas d’étayer cette hypothèse)
Par ailleurs, rien ne permet d’exclure que le FillDow ne masque pas un changement (non encore reporté dans la source) d’affectation de l’usager pour le jour concerné.

Le 18/07/2023 à 10:35

Bonjour @JFL,

Je suis de votre avis, je vais donc chercher a faire une autre extraction afin d'obtenir de manière certaine l'affectation évolutive ou pas, d'un nom à son groupe et son unité,

N'ayant pas encore travaillé sur cette extraction, je clos le sujet et merci de votre aide,

J'aurai très certainement besoin de votre aide sur un autre sujet à venir, toujours très attentif à vos précieux conseils,

Bonne journée à vous,

Cordialement,

Rechercher des sujets similaires à "power query actualisation assez longue"