Power Query Optimisation powerquery

SophieS · 15 Mai 2025

Bonjour,
Je me suis tentée un powerquery sur la base d'un fichier.

Le fichier initial est une requete qui va chercher des séries d'informations par type d'absence.
Le résultat démarre par le matricule et se poursuit par une série de colonnes pour chaque absence.

Pour pouvoir compiler ces séries d'infos, je suis passée par le fait d'extraire chaque série avec le matricule pour les compiler ensuite.

Toutefois, le résultat est que le fichier est long à charger.

Avez vous une méthode pour simplifier ce powerquery svp ?

Je vous joins le fichier de base (réduit) + le résultat + les exemples de mon powerquery (mon fichier est trop gros même zippé)

Je vous remercie d'avance pour votre aide

Sophie

alexga78 · 22 Mai 2025

Bonjour @SophieS,

Avant le traitement des données, la table comporte 4 colonnes additionnelles situées après la colonne "Identité Matricule", qui doivent être conservées ?

Si oui, à essayer.

PowerQuery:

let
A = Table.Buffer(Previous_Step), // à adapter avec le nom de l'étape précédente
B = ((x)=> List.Transform(List.Split(List.Skip(x,5),64), each {List.Range(x,0,5) & _, List.Range(x,0,5) & List.Transform(_, each ((x)=> x{2} ? ?? x{0}) (Text.Split(_,"__")))})) (Table.ColumnNames(A)),
C = Table.Combine(List.Transform(B, (x)=> Table.FromRows(List.Transform(Table.ToRows(Table.SelectColumns(A,x{0})), each List.ReplaceRange(_,6,1, {Text.Split(x{0}{6},"__"){1}})), x{1}))),
D = Table.Sort(Table.SelectRows(C, each...

chris · 15 Mai 2025

Bonjour

Peux-tu préciser quelles infos sont utile en plus de celles de ton TCD

Par ailleurs il y a des absences de même nature qui se succèdent : ne doit-ton les regrouper (exemple : matricule 53340 du 27 au 31/03 puis du 01/04 au 18/04

Dans la mesure où tu ne regroupes rien (1 absence = 1 ligne), le TCD n'est pas la bonne restitution.

Par ailleurs

TX1 et suivants semblent être des taux donc le calcul est à préciser
NBREST1 & co sont-ils vraiment à additionner ?

SophieS · 15 Mai 2025

chris à dit:
Bonjour

Peux-tu préciser quelles infos sont utile en plus de celles de ton TCD

Par ailleurs il y a des absences de même nature qui se succèdent : ne doit-ton les regrouper (exemple : matricule 53340 du 27 au 31/03 puis du 01/04 au 18/04

Dans la mesure où tu regroupes rien, le TCD n'est pas la bonne restitution.

Par ailleurs

TX1 et suivants semblent être des taux donc le calcul est à préciser

NBREST1 & co sont-ils vraiment à additionner ?

Bonjour Chris,
A ce stade les infos sur mon TCD sont OK je l'utilise pour afficher les infos de la ligne d'absence.
Mon pb est surtout de passer du fichier de base (que j'ai réduit en nb de lignes pour pouvoir l'envoyer) à la récap sans que le fichier ne soit trop lourd et trop long à se générer.
J'ai des infos à ajouter par la suite dans la requête.
Je vous remercie

chris · 15 Mai 2025

RE

Je vois que mes questions n'ont pas vraiment de réponse et mes remarques te laissent de marbre

Tans pis... j'aurais travaillé pour rien à un résultat quasi instantané ... Bonne continuation

alexga78 · 15 Mai 2025

Bonjour @SophieS, le forum,

D'accord avec Chris, ta demande n'est pas très claire...
En supprimant les colonnes ne contenant que 0 et "", sinon doublons de nom de colonnes en ne gardant que le dernier élément après "__".

Cordialement,

SophieS · 16 Mai 2025

Bonjour,

Vraiment désolée de ne pas être claire, je vous prie de m'excuser.

J'ai un fichier de base de 833 colonnes
Il est constitué d'une colonne de matricules puis 13 ensembles de 64 colonnes.
Ces 13 ensembles concernent chacun un type d'arrêt (maladie, accident de trajet...)
Les 64 colonnes regroupent le même type d'info pour chaque type d'arrêt.

Mon attendu est un fichier de 65 colonnes (la première colonne est le matricule puis les 64 colonnes type) que je traiterai ensuite avec un TCD.

Pour l'instant, mon essai rend le fichier trop lourd en terme d'actualisation.

Ce type de fichier va être extrait pour plusieurs sociétés qui n'ont pas les mêmes règles, ni le même paramétrage.
Je ne sais donc pas encore comment je vais retraiter les infos à la suite. C'est pour cette raison que je ne suis pas prête à retraiter les infos.

Est ce que je suis plus claire ?

Je vous remercie pour votre aide

SophieS · 16 Mai 2025

SophieS à dit:
Bonjour,

Vraiment désolée de ne pas être claire, je vous prie de m'excuser.

J'ai un fichier de base de 833 colonnes
Il est constitué d'une colonne de matricules puis 13 ensembles de 64 colonnes.
Ces 13 ensembles concernent chacun un type d'arrêt (maladie, accident de trajet...)
Les 64 colonnes regroupent le même type d'info pour chaque type d'arrêt.

Mon attendu est un fichier de 65 colonnes (la première colonne est le matricule puis les 64 colonnes type) que je traiterai ensuite avec un TCD.

Pour l'instant, mon essai rend le fichier trop lourd en terme d'actualisation.

Ce type de fichier va être extrait pour plusieurs sociétés qui n'ont pas les mêmes règles, ni le même paramétrage.
Je ne sais donc pas encore comment je vais retraiter les infos à la suite. C'est pour cette raison que je ne suis pas prête à retraiter les infos.

Est ce que je suis plus claire ?

Je vous remercie pour votre aide

chris · 16 Mai 2025

RE

Comme déjà dit, si ton TCD ne regroupe rien ce n'est pas le bon moyen de restituer : tu vas alourdir inutilement le classeur avec le cache du TCD.

C'est l'objectif final concret qui doit déterminer la méthode... et non une suite de tâtonnements successifs

SophieS · 19 Mai 2025

Bonjour Chris,
Mon premier objectif est de concaténer pour recetter.
Je vais enlever l'étape TCD. merci.
S'il existe une méthode pour distinguer les ensembles de colonnes et empiler les données plus rapidement, je suis preneuse.
Merci d'avance

chris · 19 Mai 2025

RE

SophieS à dit:
distinguer les ensembles de colonnes

Qu'entends-tu par là ?

Mise à plat de base (adapter le chemin du fichier)

VB:

let
    Source = Excel.Workbook(File.Contents("T:\TEMP\Analyse gestion arrêt fichier base.xlsx"), null, true),
    solivier_57_Sheet = Source{[Item="solivier_57",Kind="Sheet"]}[Data],
    #"En-têtes promus" = Table.PromoteHeaders(solivier_57_Sheet, [PromoteAllScalars=true]),
    #"Index ajouté" = Table.AddIndexColumn(#"En-têtes promus", "Index", 0, 1, Int64.Type),
    #"Supprimer le tableau croisé dynamique des autres colonnes" = Table.UnpivotOtherColumns(#"Index ajouté", {"Index","Identité Matricule "}, "MCODE", "Valeur"),
    #"Fractionner la colonne par délimiteur" = Table.SplitColumn(#"Supprimer le tableau croisé dynamique des autres colonnes", "MCODE", Splitter.SplitTextByEachDelimiter({"__"}, QuoteStyle.Csv, true), {"MCODE", "Info"}),
    #"Valeur remplacée" = Table.ReplaceValue(#"Fractionner la colonne par délimiteur","ABS__","",Replacer.ReplaceText,{"MCODE"}),
    Etat0 = Table.TransformColumnTypes(#"Valeur remplacée",{{"Identité Matricule ", type text}}),
    Dates = Table.SelectRows(Etat0, each ([Info] = "DDEB" or [Info] = "DFIN" or [Info] = "RUNID" or [Info] = "TYPE")),
    #"Colonne dynamique" = Table.Pivot(Dates, List.Distinct(Dates[Info]), "Info", "Valeur"),
    #"Lignes filtrées" = Table.SelectRows(#"Colonne dynamique", each ([DDEB] <> "")),
    Etat1 = Table.TransformColumnTypes(#"Lignes filtrées",{{"DDEB", type date}, {"DFIN", type date},  {"TYPE", type text}, {"RUNID", type text}}),
    #"Lignes filtrées2" = Table.SelectRows(Etat0, each ([Info] <> "DDEB" and [Info] <> "DFIN" and [Info] <> "MCODE" and [Info] <> "RUNID" and [Info] <> "TYPE")),
    Autres = Table.Pivot(#"Lignes filtrées2", List.Distinct(#"Lignes filtrées2"[Info]), "Info", "Valeur"),
    #"Requêtes fusionnées" = Table.NestedJoin(Etat1, {"Identité Matricule ", "Index","MCODE"}, Autres, {"Identité Matricule ", "Index","MCODE"}, "Autres", JoinKind.Inner),
    #"Autres développé" = Table.ExpandTableColumn(#"Requêtes fusionnées", "Autres", List.RemoveItems(Table.ColumnNames(Autres),Table.ColumnNames(Etat1))),
    #"Lignes triées" = Table.Sort(#"Autres développé",{{"Identité Matricule ", Order.Ascending}, {"DDEB", Order.Ascending}}),
    #"Colonnes supprimées" = Table.RemoveColumns(#"Lignes triées",{"Index"})
in
    #"Colonnes supprimées"

alexga78 · 19 Mai 2025

Bonjour à tous,

un essai.

PowerQuery:

let
A = Table.Buffer(Excel.CurrentWorkbook(){[Name="Table1"]}[Content]),
B = ((x)=> List.Transform(List.Split(List.Skip(x),64), each {{x{0}} & _, {x{0}} & List.Transform(_, each ((x)=> x{2} ? ?? x{0}) (Text.Split(_,"__")))})) (Table.ColumnNames(A)),
C = Table.Combine(List.Transform(B, (x)=> Table.FromRows(List.Transform(Table.ToRows(Table.SelectColumns(A,x{0})), each List.ReplaceRange(_,2,1, {Text.Split(x{0}{1},"__"){1}})), x{1}))),
D = Table.Sort(Table.SelectRows(C, each List.Count(List.RemoveMatchingItems(Record.ToList(_), {0,""})) > 2), {{"Identité Matricule ",0},{"DDEB",0}})
in D

Bonne journée

SophieS · 19 Mai 2025

Super merci Alexga78. Je vais tester cela 🙂
4 lignes au lieu de mes 13 tables c'est top.
Je vais essayer de comprendre ce que tu as fait.
Un grand merci à toi et Chris pour le temps passé. Désolée encore pour la mauvaise formulation

SophieS · 20 Mai 2025

Ca fonctionne parfaitement. merci.

SophieS · 20 Mai 2025

Excellente journée à vous

SophieS · 22 Mai 2025

SophieS à dit:
Super merci Alexga78. Je vais tester cela 🙂
4 lignes au lieu de mes 13 tables c'est top.
Je vais essayer de comprendre ce que tu as fait.
Un grand merci à toi et Chris pour le temps passé. Désolée encore pour la mauvaise formulation

Bonjour Alexga78,
J'ai une question complémentaire : si j'ajoute 4 colonne d'informations après la colonne matricule et que je souhaite les conserver (hors les séries de 64), a quel endroit dois je changer le code stp ? Je te remercie

Power Query Optimisation powerquery

SophieS

XLDnaute Nouveau

Pièces jointes

alexga78

chris

XLDnaute Barbatruc

SophieS

XLDnaute Nouveau

chris

XLDnaute Barbatruc

alexga78

XLDnaute Occasionnel

Pièces jointes

SophieS

XLDnaute Nouveau

SophieS

XLDnaute Nouveau

Pièces jointes

chris

XLDnaute Barbatruc

SophieS

XLDnaute Nouveau

chris

XLDnaute Barbatruc

alexga78

XLDnaute Occasionnel

Pièces jointes

SophieS

XLDnaute Nouveau

SophieS

XLDnaute Nouveau

SophieS

XLDnaute Nouveau

SophieS

XLDnaute Nouveau

Discussions similaires

Power Query Optimisation powerquery

XLDnaute Nouveau

Pièces jointes

XLDnaute Barbatruc

XLDnaute Nouveau

XLDnaute Barbatruc

XLDnaute Occasionnel

Pièces jointes

XLDnaute Nouveau

XLDnaute Nouveau

Pièces jointes

XLDnaute Barbatruc

XLDnaute Nouveau

XLDnaute Barbatruc

XLDnaute Occasionnel

Pièces jointes

XLDnaute Nouveau

XLDnaute Nouveau

XLDnaute Nouveau

XLDnaute Nouveau

Discussions similaires

Privacy & Transparency

Privacy & Transparency