PowerQuerry - Compiler de nombreux fichier dépassant la limite de lignes

Hello,

Tu veux vraiment avoir au quart d'heure ?

J'ai fait un truc qui fonctionne avec tes deux onglets mais la datemin c'est en 2016-09 et la date max c'est en 2017. Donc ça veut dire que pour avoir la moyenne sur chaque quart d'heure je dois générer tous les quarts d'heure sur plus d'une année ... Ca fait beaucoup de données pour beaucoup de valeur null....

Si c'est ce que tu veux je le posterai mais le temps d'actualisation est déjà hyper long avec deux fichiers....

@+

Edit : ca veut pas charger ça tourne

Bonjour,

Oui je dois le caler au quart d'heure car je dispose de beaucoup d'autres sondes déjà calées sur cette fréquence (il s'agit fichier d'exemple founit).

A terme, je vais ajouter au fichier d'exemple déjà mis en forme, les nouvelles sondes.

Mais inutile de traiter les milier de lignes que j'ai mises dans mes fichiers excel. Je cherche seulement la formule qui me permet de faire cette moyenne par quart d'heure pour chaque sonde. Donc n'hésite pas à ne conserver qu'une 100ène de lignes pour l'exemple.

Pour ce qui est des datas, j'en ai des millions à traiter derrière, mais je ferai tourner ça sur mon PC (qui j'espère tiendra le coup, sinon je ferai ça sur une machine scientifique que l'on peut occasionnellement exploiter pour ce type de traitement).

Au regard des millions de données que j'ai à traiter, il n'y a que très peu de valeurs nulles.

Dans l'exemple fournis, c'est le début des acquisition (mise en service en 2016). Il y avait donc des sondes pas encore en service, d'ou les "No Data" que tu vois.

En soit j'ai environ 30 sondes à traiter, et j'ai des acquisition de 2016 à 2024 => D'ou l'utilité de Query.

Merci encore. Je reprendrai demain car je sature pour aujourd'hui...

Je dois avoue que ça m'a rendu fou le temps d'actualisation, mais en diminuant le nombre de ligne des tableaux source

ça donne ça en rendu

Bonjour BAROUTE78,

Je vois que tu as réussi avant moi, comment t'y es tu pris?

C'est exactement le résultat que je vise.

J'ai vraiment du chemin à faire avant de me débrouiller comme toi sous excel...

Merci encore!

Hello,

Alors si je dois expliquer le cheminement brièvement

- Passage des données au format "Tableaux structurés" que je renomme avec "t_" au début pour faire un filtre dans la requête sur les tableaux commençant par "t_" ;

- Récupération sur tous tes tableaux des dates minimales et maximales pour borner ma plage de résultat ;

- Génération de la liste de jour avec les plages tous les quarts d'heure ;

- Je duplique cette plage d'horaire sur toutes les sondes que je trouve dans tes tableaux ;

- Et après pour chaque plage horaire je regarde les lignes qui ont des datetime entre la plage et la plage à +15 minutes pour bien les regrouper et ensuite je fais une moyenne sur ces valeurs par plage

N'hésite pas si besoin

@+

Merci pour le détail des étapes, mais même avec l'aide de tuto indiens pour trouver comment réalisé ce que tu décris, je ne trouve pas comment faire.

Voilà ou j'en suis à l'heure qu'il est :

J'ai créé 2 tables "t_1" et "t_2" qui contiennent chacune les valeurs d'une sonde :

image image

A noter que dans t_1 j'ai changé le format de la date en "Date", alors que je n'ai rien touché dans t_2

Ensuite, en créant une référence associée à chaque tableau, j'ai réussi à trouver comment sortir la date min et la date max de chaque table :

image image image image

Après j'ai généré une liste de date min à date max pour t_1.

J'ai dû convertir la date de t_1 au format date, sans quoi la fonction Number.Form ne fonctionne pas (j'imagine que ce n'est pas reconnu comme un nombre si je laisse la date au complet).

image

Il me faut à présent convertir cette liste en date, puis échantillonner tous les quart d'heure et associer les valeurs des sondes.

Je n'ai pas idée comment faire.

Je pense abandonner l'idée d'exploiter ces mesures car je n'ai pas le niveau suffisant ni le temps de me former pour réaliser ce travail sur autant de données.

J'ai l'impression qu'il n'est pas possible d'enregistrer le travail réalisé sous Query pour le partager avec quelqu'un d'autre, donc je n'ai pas le choix que de me débrouiller seul pour programmer toutes ces fonctions.

En tous cas merci pour l'aide, et de m'avoir fait découvrir cet outil.

Je pense demander une formation histoire de pouvoir m'en servir à l'avenir.

Bonjour

Il ne faut pas charger les tables individuellement mais le contenu du classeur et filtrer les tables à utiliser

On crée une requête vierge et on saisit dans la barre de formule de PowerQuery

= Excel.CurrentWorkbook()

Puis on filtre

Si tu regarde les étapes de la requête de BAROUTE78 (à droite dans l'interface) : on voit pour chaque étape le détail dans la barre de formule et en cliquant sur le petit rouage.

Les étapes AllTable et les 2 étapes après AddSonde ne sont pas faisable par une simple utilisation du ruban mais le le reste est classique

Bonjour,

Je n'arrive pas à trouver ou voir les formules pour faire chaque étape.

Faut il retourner dans Query en ouvrant le classeur de BAROUTE78?

Ou bien depuis excel, cliquer sur Données>Requêtes et connexions?

J'ai aussi tenté Données>Obtenir des données>Lancer l'éditeur Power Query, mais rien ne se passe.

Excusez moi encore pour mon si faible niveau en excel, ça parait pas, mais je passe des heures à chercher comment faire les choses que vous me dites.

J'arrive à trouver ces lignes :

image

Mais je n'identifie pas là dedans les étapes qui permettent de générer la colonne de date par 15min par exemple.
Alors je doute que je sois au bon endroit.

Merci encore pour l'aide.

EDIT : Est-ce cette ligne qui permet de compiler les 2 sondes par tranche de 15 min tout en faisant la moyenne des valeurs?

= Table.TransformColumnTypes(Tableau2__2_Table,{{"Date", type date}, {"Heure", type datetime}, {"QJB2A-QGB610MT.PM", type number}, {"QJB2A-QGB627MT.PM", type number}})

Bonjour

Tu peux appeler PQ par Alt + F12

L'interface est ainsi

image

On voit bien la liste des étapes à droite.
Si tu l'as désactivée : Affichage, Paramètres d'une requête

On voit aussi la barre de formules de PQ.

J'ai repéré une petite erreur à l'étape Jour (il manque le dernier jour): modifier ainsi dans la barre de formules

= Table.FromColumns({List.Dates(DateMin , Number.From(DateMax - DateMin) +1, Duration.From(1))},{"Date"})

Décidément je pense qu'il y a quelque chose qui ne va pas.

Quand j'ouvre le fichier et que je fais Alt + F12, ca mouline quelques seconde mais rien ne s'ouvre.

Alors que depuis une feuille Excel vierge, ca ouvre effectivement PQ.

Donc j'ouvre le fichier en faisant Obtenir des données>A partir d'un tableur.xls

Ca m'ouvre cette fenêtre :

image

Je coche toutes les tables et feuilles et je fais Transformer les données

PQ s'ouvre mais je n'ai pas le même titre que sur ton imprimé écran, ni les mêmes requêtes (je n'ai pas tout le détail que tu as et que je recherche).

image

Peut être manque t il un module à mon Excel pour faire ce que vous me dites?

RE

Non

Alt F12 ouvre l'interface PQ depuis n'importe quel classeur même vierge

Quand il y a déjà une requête, surtout pour un fichier téléchargé, tu as message pour valider l'ouverture au niveau du ruban
si tu ne le valides pas, cela ne s'ouvre pas mais un second message s'ouvre dans une boîte de dialogue.

Là ton image semble indiquer que tu n'as pas la requête dans le fichier mais juste le résultat : si tu n'ouvres pas le fichier de Baroute78 avec Excel mais depuis PowerQuery c'est normal : tu ne vois que le contenu d'Excel.

Il faut ouvrir le fichier que Baroute78 a transmis Lundi à 17:20 et depuis ce fichier utiliser Alt F12

Bonjour Chris,

Il faut ouvrir le fichier que Baroute78 a transmis Lundi à 17:20 et depuis ce fichier utiliser Alt F12

C'est exactement ce que je fais depuis le début, mais à part m'afficher le curseur de la souris qui réfléchi pendant 1 seconde, rien ne se passe. Et si je le refait ensuite, le curseur ne réagit même plus.

D'un autre tableur par contre, Alt + F12 fonctionne bien.

Ca doit venir de mon PC, je vais chercher.

Merci pour l'aide en tous cas.

EDIT : J'ai réussi. J'ai copié chaque onglet du fichier de Baroute78 dans un nouveau classeur. Et à présent le Alt + F12 fonctionne.

Je vais pouvoir étudier la méthode utilisée.

Merci grandement à vous deux.

Je viens de regarder chaque ligne de code, certaines étapes sont claire pour moi, d'autre beaucoup moins en raison de mon manque de connaissance sur la syntaxe des formules sur PQ.

Je comprends mieux la question de Baroute78 concernant la réelle nécessité de générer une ligne tous les quart d'heure, car ça fait beaucoup trop de ligne à gérer si l'on part de 2016 à aujourd'hui (315360 lignes).

Est- il possible de supprimer les lignes pour lesquelles aucune sonde ne dispose de valeur une fois la totalité des sondes compilées?

Merci encore

Hello,

J'ai revu ma manière de faire :

En faite j'ai créé une colonne Arrondi au quart d'heure basée sur ta colonne qui contient la date et heure/minute. Du coup sur l'entièreté de tes deux tableaux (27000 lignes) ba c'est instantané

@+

Bonjour

Vous avez combien de sondes ?

En dehors d'excel vous disposez d'un gestionnaire de base de données (en dehors d'Accès inclus dans Office) ?

J'ai lancé un test avec une source Access...

Edit : pas vu le dernier message de Baroute78. Bon ça tourne on verra...

Bonjour,

@78chris : Non je ne dispose pas d'autres gestionnaires de base de données.

J'ai environ 30 sondes.

Certaines sont une compilation de 2600 fichiers .xls, dont le cumul ne dépasse pas le million de lignes.

D'autres sont une compilation de 3000 fichiers .xls, dont le cumul atteint 13 million de lignes.

J'ai donc tout d'abord créé un dossier par sonde dans lesquels j'ai déposé les milliers de fichiers qui leurs sont propres.

Ensuite, grâce à vous, je sais comment compiler ces milliers de fichiers en feuilles Excel. Les sondes pour lesquelles le total de ligne ne dépasse pas le million sont stockées dans une seule feuille d'un tableau Excel.

Les sondes pour lesquelles le total de ligne dépasse le million sont stockées dans un tableau Excel comportant plusieurs feuilles (1000000 de ligne par feuille).

Ensuite je dois épurer chaque feuille pour retirer les infos qui ne sont pas des valeurs souhaitées (les en-tête des 2600 ou 3000 fichiers par exemple).

Une fois fait, je les convertie en tableau et je les renomme avec l "t_" devant pour pouvoir utiliser le programme de Baroute78.

@BAROUTE78 : Merci pour cette nouvelle proposition. Je n'arrive pas à expliquer pourquoi la première sonde est exclue lorsque l'on vient concaténer les colonnes "content" de chaque sonde.

Aussi, le résultat final de Test2 liste à présent les sondes dans une seule colonne. Ca ne génère plus une colonne par sonde avec la date et heure dans les deux premières colonnes du tableau de résultat.

J'imagine que c'est pour utiliser le mode tableau dynamique comme dans ton exemple?

Est il toutefois possible de ne pas passer par un tableau dynamique et de conserver la structure avec une colonne par sonde?

Merci encore.

Hey,

Et du coup on peut se baser directement sur le fichier excel de base...

Tu mets tous les fichiers Excel, les 2600 d'une sonde, les 3000 autres d'une autre sonde etc dans dossier puis sous dossier etc et après on dit à PowerQuery d'aller sur chaque fichier enlever les lignes non nécessaires et de tout compiler. Ca t'évite le formatage à la main...

@+

Edit :

Voici la modification en colonnes

RE

J'ai chargé dans Access les lignes que l'on avait dans les 2 listes Excel.

J'ai arrondi le temps dans Access.

Puis j'ai dupliqué les lignes en changeant le code sonde à chaque fois jusqu'à obtenir une table de plus de 13 millions de lignes (seulement 2443 temps mais 970 sondes)

Je charge dans PQ puis TCD (pas testé de charger direct en TCD d'autant qu'il faudrait définir une source dans Windows) : ça tourne sans souci et ce n'est pas très long.

EDIT : le pivotage sur ce volume marche aussi dans PQ mais nettement plus long que le TCD

Hello,

Merci encore pour votre précieuse aide.

J'avance enfin dans ma compréhension de PQ (enfin je pense!).

J'ai justement trouvé comment automatiser l'épuration des données via PQ.

Je cherche à présent à gérer directement dans PQ le fait d'aller chercher dans les différents dossier, les 2600 fichiers, pour ensuite les combiner, les épurer, les mettre en tableau et les renommer avec le "t_" , de manier à pouvoir appliquer le code de BAROUTE78.

J'ai pour cela quelques questions :

Voilà le code qui me permet d'aller chercher les milliers de fichier source de la sonde 610MT, que j'ai rangé dans le dossier "QGB610MT".

J'épure ensuite les lignes non souhaitées avec mes 3 étapes d'épuration.

Enfin, je divise en table de 1 000 000 de ligne au cas où le total dépasse ce volume de ligne (ce qui n'est pas le cas pour cette sonde, je me retrouve donc avec une seule table en fin de code "Final QGB610MT1").
Pour 3 sondes, j'ai créé 3 requetes comportant ce code (avec nom des sonde et dossier cible qui change) :

let
    Source = Folder.Files("C:\Users\leisabel\Desktop\Export - Copie\QGB610MT"),
    #"Fichiers masqués filtrés1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Appeler une fonction personnalisée1" = Table.AddColumn(#"Fichiers masqués filtrés1", "Transformer le fichier", each #"Transformer le fichier"([Content])),
    #"Colonnes renommées1" = Table.RenameColumns(#"Appeler une fonction personnalisée1", {"Name", "Source.Name"}),
    #"Autres colonnes supprimées1" = Table.SelectColumns(#"Colonnes renommées1", {"Source.Name", "Transformer le fichier"}),
    #"Colonne de tables développée1" = Table.ExpandTableColumn(#"Autres colonnes supprimées1", "Transformer le fichier", Table.ColumnNames(#"Transformer le fichier"(#"Exemple de fichier"))),
    #"Type modifié" = Table.TransformColumnTypes(#"Colonne de tables développée1",{{"Source.Name", type text}, {"Column1", type text}, {"Column2", type text}, {"Column3", type any}}),
    #"Colonnes supprimées" = Table.RemoveColumns(#"Type modifié",{"Source.Name", "Column3"}),
    #"Epuration" = Table.RemoveMatchingRows(#"Colonnes supprimées",{[Column1 = "Nombre de valeurs :"]},"Column1"),
    #"Epuration2" = Table.RemoveMatchingRows(#"Epuration",{[Column1 = "QJB2B-QGB610MT.PM"]},"Column1"),
    #"Epuration3" = Table.RemoveMatchingRows(Epuration2,{[Column2 = "Pt Created"]},"Column2"),
    #"Final QGB610MT"=Table.Split(#"Epuration3", 1000000),
    #"Final QGB610MT1" = #"Final QGB610MT"{0}
in
    #"Final QGB610MT1"
image

Question 1 (moindre importance, juste pour ma culture personnelle, car ça ne me prends pas beaucoup de temps de copier/coller les noms de sonde) : Est-il possible de récupérer le nom du dossier de la sonde QGB610MT de manière à générer une variable "Repère_Sonde", que je puisse appeler de manière générique dans le code, notamment pour nommer mes tables "Final QGB110MT" de la manière suivante "Final "Repère_Sonde"", ou encore [Column1 = "QJB2B-QGB610MT.PM"]

Question 2 (la plus importante) : Est il possible de générer la source d'une nouvelle requête, en compilant mes 3 précédentes requêtes, plutôt qu'en allant chercher 3 feuilles d'un tableur .xls?
L'idée est de créer cette nouvelle source composée des multiples requêtes que je vais générer pour chaque sonde, pour ensuite dérouler le code de BAROUTE78.

Question 3 : Dans le cas ou ma sonde (et donc ma requête) comporte plusieurs millions de lignes. est il préférable de diviser ma table en plusieurs tables, ou bien au contraire, cela est totalement inutile, puisqu'en poursuivant avec le code de BAROUTE78, le volume de ligne va réduire par tranche de 15 minutes, réduisant le nombre de ligne inférieur au million.
J'ai tendance à dire que c'est inutile et que je peux donc supprimer mes étapes suivantes :

    #"Final QGB610MT"=Table.Split(#"Epuration3", 1000000),
    #"Final QGB610MT1" = #"Final QGB610MT"{0}

Merci à vous deux.

Je commence à apprécier PQ même si je suis très mauvais et que l'analyse des formules et de leur fonction me prend encore beaucoup de temps.

EDIT :

Je me dis qu'il est certainement possible de faire tout ce travail en une seule requête (voir exemple ci-dessous).

Seule contrainte : Il semblerait que l'on ne puisse pas écraser le contenu d'une variable existante par un nouveau contenu.
Exemple : J'ai du numéroté toutes mes variables (Source, Fichiers masqués filtrés,....) sans quoi le code ne tourne pas.
Et je pense que le code de BAROUTE78 ne fonctionnerait plus, puisque les tables ne seraient plus distinguables sachant que j'ai supprimé la colonne comportant le nom de la sonde. Je me retrouverai à devoir compiler des tables composées chacune d'une colonne 'Column1" comportant les dates et heure, et une colonne 'Column2' comportant les valeurs, ce qui me donnerait un autre format de table que celui dont est capable de traiter le code de BAROUTE78.

let
    Source1 = Folder.Files("C:\Users\leisabel\Desktop\Export - Copie\QGB627MT\B"),
    #"Fichiers masqués filtrés1" = Table.SelectRows(Source1, each [Attributes]?[Hidden]? <> true),
    #"Appeler une fonction personnalisée1" = Table.AddColumn(#"Fichiers masqués filtrés1", "Transformer le fichier", each #"Transformer le fichier"([Content])),
    #"Colonnes renommées1" = Table.RenameColumns(#"Appeler une fonction personnalisée1", {"Name", "Source.Name"}),
    #"Autres colonnes supprimées1" = Table.SelectColumns(#"Colonnes renommées1", {"Source.Name", "Transformer le fichier"}),
    #"Colonne de tables développée1" = Table.ExpandTableColumn(#"Autres colonnes supprimées1", "Transformer le fichier", Table.ColumnNames(#"Transformer le fichier"(#"Exemple de fichier"))),
    #"Type modifié" = Table.TransformColumnTypes(#"Colonne de tables développée1",{{"Source.Name", type text}, {"Column1", type text}, {"Column2", type text}, {"Column3", type any}}),
    #"Colonnes supprimées" = Table.RemoveColumns(#"Type modifié",{"Source.Name", "Column3"}),
    #"Epuration" = Table.RemoveMatchingRows(#"Colonnes supprimées",{[Column1 = "Nombre de valeurs :"]},"Column1"),
    #"Epuration2" = Table.RemoveMatchingRows(#"Epuration",{[Column1 = "QJB2B-QGB627MT.PM"]},"Column1"),
    #"QGB627MT_B" = Table.RemoveMatchingRows(Epuration2,{[Column2 = "Pt Created"]},"Column2"),

        Source2 = Folder.Files("C:\Users\leisabel\Desktop\Export - Copie\QGB627MT\A"),
    #"Fichiers masqués filtrés2" = Table.SelectRows(Source2, each [Attributes]?[Hidden]? <> true),
    #"Appeler une fonction personnalisée2" = Table.AddColumn(#"Fichiers masqués filtrés2", "Transformer le fichier", each #"Transformer le fichier"([Content])),
    #"Colonnes renommées2" = Table.RenameColumns(#"Appeler une fonction personnalisée2", {"Name", "Source.Name"}),
    #"Autres colonnes supprimées2" = Table.SelectColumns(#"Colonnes renommées2", {"Source.Name", "Transformer le fichier"}),
    #"Colonne de tables développée2" = Table.ExpandTableColumn(#"Autres colonnes supprimées2", "Transformer le fichier", Table.ColumnNames(#"Transformer le fichier"(#"Exemple de fichier"))),
    #"Type modifié2" = Table.TransformColumnTypes(#"Colonne de tables développée2",{{"Source.Name", type text}, {"Column1", type text}, {"Column2", type text}, {"Column3", type any}}),
    #"Colonnes supprimées2" = Table.RemoveColumns(#"Type modifié2",{"Source.Name", "Column3"}),
    #"Epuration3" = Table.RemoveMatchingRows(#"Colonnes supprimées2",{[Column1 = "Nombre de valeurs :"]},"Column1"),
    #"Epuration4" = Table.RemoveMatchingRows(#"Epuration3",{[Column1 = "QJB2A-QGB627MT.PM"]},"Column1"),
    #"QGB627MT_A" = Table.RemoveMatchingRows(Epuration4,{[Column2 = "Pt Created"]},"Column2")
    #"Fusion_Tables" = ???????????
in
    #"Fusion_Tables"

Bonjour

Quand tu charges un dossier : il faut faire toutes les manips dans "Transformer l’exemple de fichier" ce qui crée automatiquement une fonction qui traitera automatiquement à l’identique chaque fichier du dossier. Ce que tu ne sembles pas avoir fait.

Peux-tu joindre 2 fichiers exemple bruts.

Le code de BAROUTE78 extrait la sonde du contenu mais si le nom du fichier ou bien le nom de l'onglet contient le code de la sonde on peut l'utiliser.

Il faut juste définir la règle : le nom suivi d'un _ ou - plus éventuellement un numéro d'ordre s'il y a plusieurs fichiers pour une même sonde.

Après il suffit d'indexer la liste complète puis on peut découper par 1000 000 grâce à l'index.

Rechercher des sujets similaires à "powerquerry compiler nombreux fichier depassant limite lignes"