Power Query Optimisation powerquery

Créer une discussion
Répondre
  • Initiateur de la discussion Initiateur de la discussion SophieS
  • Date de début Date de début

Boostez vos compétences Excel avec notre communauté !

Rejoignez Excel Downloads, le rendez-vous des passionnés où l'entraide fait la force. Apprenez, échangez, progressez – et tout ça gratuitement ! 👉 Inscrivez-vous maintenant !

S'inscrire
S

SophieS

XLDnaute Nouveau
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
 

Pièces jointes

Solution
A
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...
Trier par date Trier par votes
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 ?
 
Dernière édition:
Vote positif 0 Vote négatif
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 ?
Cliquez pour agrandir...
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
 
Vote positif 0 Vote négatif
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
 
Vote positif 0 Vote négatif
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
Cliquez pour agrandir...
 

Pièces jointes

Vote positif 0 Vote négatif
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
 
Dernière édition:
Vote positif 0 Vote négatif
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
 
Vote positif 0 Vote négatif
RE
SophieS à dit:
distinguer les ensembles de colonnes
Cliquez pour agrandir...
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"
 
Vote positif 0 Vote négatif
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
 

Pièces jointes

Dernière édition:
Vote positif 0 Vote négatif
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
 
Vote positif 0 Vote négatif
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
Cliquez pour agrandir...
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
 
Vote positif 0 Vote négatif
Rejoins nos membres "Supporter XLD" et booste ton expérience Excel !
- Navigue sans publicité
- Accède à Cléa, notre assistante IA experte Excel... et pas que...
- Profite de fonctionnalités exclusives
Ton soutien permet à Excel Downloads de rester 100% gratuit et de continuer à rassembler les passionnés d'Excel.
Je deviens Supporter XLD

Discussions similaires

D
  • Résolu(e)
Microsoft 365 Assistance pour optimisation d'un code VBA qui ne fonctionne pas comme souhaité
2
Réponses
21
Affichages
462
desto
D
O
  • Question Question
Power Query Ecritures comptables Power Query
Réponses
2
Affichages
513
oobiz
O
oguruma
POWERQUERY :: LES DATES REMARQUABLES
Réponses
5
Affichages
267
oguruma
oguruma
D
  • Résolu(e)
XL 2021 Selection listview et Envoie Emails automatique
Réponses
4
Affichages
317
delsart59
D
Calvus
  • Question Question
Microsoft 365 Recherche incomplète planning (problème de dates)
Réponses
13
Affichages
412
p56
p56
oguruma
POWERQUERY :: Importation et optimisation des étapes - Table.TransformColumns - Table.TransformRows - Record.AddField - Record.TransformFields
Réponses
0
Affichages
295
oguruma
oguruma
L
  • Résolu(e)
Microsoft 365 Ratio dans un TCD
Réponses
4
Affichages
397
Laurent78
L
B
  • Question Question
Microsoft 365 VBA optimisation chargement listview
Réponses
7
Affichages
500
barry white
B
oguruma
POWERQUERY :: Lancement de requêtes à tiroir - dynamiques - paramétrables - code des requêtes sur disque - Exemple de mise en oeuvre
Réponses
0
Affichages
613
oguruma
oguruma
B
  • Résolu(e)
XL 2013 comptage des défectueux
Réponses
5
Affichages
468
bruno972
B
oguruma
POWERQUERY :: Cumuls par trimestre ou par période groupées
Réponses
0
Affichages
474
oguruma
oguruma
oguruma
POWERQUERY :: Ranking par catégorie :: TopN variable d'une liste
Réponses
0
Affichages
348
oguruma
oguruma
L
  • Résolu(e)
Microsoft 365 Cumul progressif en PowerQuery
Réponses
7
Affichages
712
oguruma
oguruma
G
  • Question Question
XL 2016 Répartition mensuelle de la charge par équipe en fonction d'une date de début et d'une date de fin
Réponses
4
Affichages
744
Lolote83
L
oguruma
POWERQUERY :: Ajout d'une colonne avec calculs multiples & TCD à l'issue des calculs
Réponses
0
Affichages
328
oguruma
oguruma
A
  • Résolu(e)
Microsoft 365 PowerPivot-comparer 2 périodes
Réponses
12
Affichages
796
Antho1234
A
P
  • Question Question
Microsoft 365 VBA - EXCEL Compiler plusieurs dates en périodes suivant un code absence
Réponses
6
Affichages
533
Pb68
P
M
  • Résolu(e)
Microsoft 365 Power Query - Regroupement date début/date de fin
Réponses
9
Affichages
999
Maxou69
M
oguruma
POWERQUERY :: Extraire des caractères selon un masque
Réponses
1
Affichages
582
oguruma
oguruma
D
  • Question Question
XL 2021 excel - vbs - csv
2
Réponses
28
Affichages
2 K
Deleted member 441486
D
Retour