Améliorer performance requête

Bonjour à tous,

Je dispose d un dossier sur un serveur d entreprise. Ce dossier contient 1 csv généré chaque jour. J ai donc utiliser power query pour combiner tout ces csv et faire une synthèse. Si j'ai bien compris ; à chaque actualisation ma requête va chercher l ensemble des données et y appliqué mes traitements. L actualisation se fait en 1m30. Je me demande si il est possible d enregistrer le résultat et de recharger chaque jour seulement le nouveau csv creer. J ai mis la fonction table buffet sur ma source mais aucun effet...

Une piste : query folding sur active directory peut être mais comment ça fonctionne ? Ou peut être une autre solution

merci d avance

Bonjour

Peux-tu mettre à jour ton profil car tu as laissé 2017 comme version Excel

voir cet article https://www.lecfomasque.com/power-query-ameliorer-la-performance-de-vos-requetes/

il indique notamment le csv ne supporte pas le Query folding

Je pense que le plus simple est de combiner avec VBA :

• faire une première synthèse complète avec une colonne indiquant les dates des fichiers, puis délier le tableau résultant de la source (Synthèse_Préc)
• renommer la requête Synthèse_Finale et la modifier pour filtrer sur les dates des fichiers csv plus récentes que la date la plus récente du tableau Synthèse_Préc
• créer une nouvelle requête pour croiser Synthèse_Finale avec Synthèse_Préc et obtenir l'ensemble
• par VBA : ajouter les lignes de Synthèse_Finale au tableau Synthèse_Préc

Ainsi à chaque extraction on n'importe donc que le delta et il est ajouté à l'historique en fin de traitement

Les TCD doivent être construits sur le tableau Synthèse_Préc, complet à l'issue de la copie VBA.

Très bien merci beaucoup j ai à peu près compris ta démarche mais ce n est pas dans mes compétences. J essayerai tous de même.

La source étant une url n'est il pas possible de mettre des filtres directement dans l url ? Pourquoi la fonction buffer n'est pas adapté ? Ce n'est précisément pas le but de cette fonction ?

Mais synthèse_prec c'est mon tcd actuel avec le tableau et son top 20 ou un autre fichier créé pour l occasion ?

Je viens d'avoir une idée tu sais comment est mon fichier puisque tu as fait une grande partir du travail : donc j ai mon tableau avec mon top 20 erreur... oublions toute les requêtes précédente pour y arriver. Ne pourrait on pas simplement : importer le csv j-1 dans le tcd puis d enregistrer le fichier tout sa dans un bouton VBA ou à l ouverture du fichier ? Au final l opération que ke veux faire c est ajouter des données (max 100ko) à un tableau qui n à plus aucun lien avec mes requêtes précédentes ? J espère je suis clair. Merci d'avance

Bonjour

Skynet57380 a écrit :

Pourquoi la fonction buffer n'est pas adapté ? Ce n'est précisément pas le but de cette fonction ?

Buffer stocke en mémoire entre le début de la requête et la fin, pas de façon éternelle...

Il faut procéder de la façon suivante

• Historisation
  • Dupliquer la requête Data et renommer la copie en Histo
  • Modifier la requête de façon à conserver la colonne Name et supprimer la dernière étape de regroupement
  • Charger Histo dans un tableau puis supprimer la requête Histo
• Récupération des données récentes
  • Créer une requête à partir du tableau Histo
  • Dupliquer la requête Data et renommer la copie en Data_Filtre
    • Ne conserver que les étapes Source et les premiers filtres sur les csv
    • fusionner la requête avec Histo sur Name en jointure externe gauche
    • développer Histo : Name seulement
    • filtrer la colonne Name.1 pour ne conserver que null
    • Ne conserver que la colonne Name
  • Dupliquer la requête Data et renommer la copie en Data_New
    • Après les étapes Source et les premiers filtres sur les csv insérer les étape suivantes
      • fusionner la requête avec Data_Filtre sur Name en jointure externe gauche
      • développer Data_Filtre : Name seulement
      • filtrer la colonne Name.1 pour éliminer null
    • supprimer la dernière étape de regroupement
  • Modifier la requête Data en remplaçant tout son contenu par l'ajout des requêtes Data_New et Histo :
    • Remplacer la ligne source par
      =Histo
    • Supprimer toutes les autres étapes puis ajouter la requête Data_New
    • Ajouter le regroupement par Date et Erreur avec calcul de Nb
    • Fermer PowerQuery puis charger Data_New dans un tableau à côté de Histo
• VBA
  • Dans le module ThisWorkbook

    Private Sub Workbook_Open()
    Dim Plage As Range
    Application.ScreenUpdating = False
    
        With Range("Data_New").ListObject
            If .DataBodyRange Is Nothing Or .ListColumns(1).DataBodyRange.Cells(1, 1) = "" Then Exit Sub
        End With
    
        Set Plage = Range("Data_New")
        With Range("Histo").ListObject
            X = .Range.Rows.Count
            For I = 1 To Plage.Rows.Count
                .ListRows.Add
            Next I
            .DataBodyRange.Range(Cells(X, 1), Cells(X + I - 2, 4)) = Plage.Value
        End With
        ThisWorkbook.RefreshAll
    End Sub

Le fichier résultant

merci beaucoup en ouvrant le fichier ce matin j'ai une erreur : en débogage :

Private Sub Workbook_Open()
Dim Plage As Range
Application.ScreenUpdating = False

With Range("Data_New").ListObject
If .DataBodyRange Is Nothing Or .ListColumns(1).DataBodyRange.Cells(1, 1) = "" Then Exit Sub
End With

Set Plage = Range("Data_New")
With Range("Histo").ListObject
X = .Range.Rows.Count
For I = 1 To Plage.Rows.Count
.ListRows.Add
Next I
.DataBodyRange.Range(Cells(X, 1), Cells(X + I - 2, 4)) = Plage.Value
End With
ThisWorkbook.RefreshAll
End Sub

erreur définie par l'action ou par l'objet

pardon ça marche quelques réglages de confidentialités dans les requêtes. Merci beaucoup en tous cas. c'est magique 1 sec d'actualisation.

oups j'ai encore été trop vite quand j'ouvre le fichier dans un premier temps il a mis l'erreur VBA ensuite j'ai été sur les requêtes et les ai rechargées et réglées les problèmes de confidentialité. J'ai vu que le fichier vba compte les lignes. Il y a 41000 lignes j'ai donc attendu qu'il les compte. Et ensuite j'ai enregistré j'ai rouvert le fichier mais toujours le même problème sa met plus de temps à ouvrir le fichier qu'à actualiser mes lignes de production. Je comprends pas.

RE

Le comptage est instantanée surtout sur un tableau structuré.

Éventuellement ajouter
Application.Calculation=xlCalculationManual

en debut de sub et

Application.Calculation=xlCalculationAutomatic

en fin

Mais je ne pense pas que le problème vienne de là

Tu es supposé partir du classeur qui fonctionnait et suivre pas à pas ce que j'ai décrit comme manip

Donc déjà tes histoires "réglages de confidentialités dans les requêtes" m'interpelle : ton classeur fonctionnait donc pourquoi ces réglages ?

Le refresh de tes requêtes ne doit pas être prévu à l'ouverture : sinon forcément cela crée un conflit

Pardon oui je viens de comprendre que mes représentants sont tous automatique à l ouverture.. pour commencer j ai pris ton fichier pour voir et j ai eu cette erreur mais à voir avec mon fichier final. J y travail cet après midi

Voila se que j'ai fait et j'ai une erreur. j'ai bien suivi la procédure avec les étapes hyper détaillées que tu m'as donné. Or j'ai une erreur avec le VBA :

Private Sub Workbook_Open()
Dim Plage As Range
Application.ScreenUpdating = False

With Range("Data_new").ListObject
If .DataBodyRange Is Nothing Or .ListColumns(1).DataBodyRange.Cells(1, 1) = "" Then Exit Sub
End With

Set Plage = Range("Data1_new")
With Range("Histo").ListObject
X = .Range.Rows.Count
For I = 1 To Plage.Rows.Count
.ListRows.Add
Next I
.DataBodyRange.Range(Cells(X, 1), Cells(X + I - 2, 4)) = Plage.Value
End With
ThisWorkbook.RefreshAll
End Sub

je te met le fichier complet pour te montrer ou j"en suis du projet, j'ai désactivé l'actualisation sur mes requêtes

RE

Dans l’historisation on garde le champ Name dans le requête Data.
Ce champ doit donc se retrouver dans TOUTES les copies faites après et donc dans Data_New

Comment comparer des noms de fichiers s'ils sont absents ?

pardon du coup dans la procédure que tu as écris :

1.Modifier la requête Data en remplaçant tout son contenu par l'ajout des requêtes Data_New et Histo :
2.Remplacer la ligne source par
=Histo
3.Supprimer toutes les autres étapes puis ajouter la requête Data_New
4.Ajouter le regroupement par Date et Erreur avec calcul de Nb
Fermer PowerQuery puis charger Data_New dans un tableau à côté de Histo

sur le 4. je regroupe par date name erreur ?

RE

Je me suis décarcassé à bien montrer l'enchaînement : on a dupliqué Data AVANT de la modifier

En plus tu vas avoir autant de Histo et de Data_New que de dossiers sources donc il va falloir adapter

Donc commence par un dossier, et quand il fonctionnera tu pourras adapter...

excuse moi je pense que tu m'as mal compris je ne sais absolument pas à quel étape j'ai fait une erreur. Je ne dis pas que tu t 'es trompé. d'ailleurs je vois pas comment je pourrais te corriger un jour mdr

RE

78chris a écrit :

Dans l’historisation on garde le champ Name dans le requête Data.

Ce champ doit donc se retrouver dans TOUTES les copies faites après et donc dans Data_New

Comment comparer des noms de fichiers s'ils sont absents ?

J'ai bien précisé dans l'historisation (mes explications sont structurées en chapitres avec des titres en gras !)

Tu confonds vitesse et précipitation...

je vais recommencer du début je te tiens au courant

je précise juste j'ai fait mon fichier comme suit :

hm1 = data1 et data1 (2) + traitement

hm2 = data2 et data2 (1) + traitement(2)

etc donc je reprends hm1 et j 'y applique tes étapes. Merci en tous cas et je suis désolé je vais y aller plus doucement et faire attention.

Traitement n'a pas besoin d'être démultiplié : c'est comme une fonction Excel qui traite la colonne qu'on lui passe en paramètre

78chris a écrit :

Traitement n'est pas une requête mais une fonction...