PowerQuerry - Compiler de nombreux fichier dépassant la limite de lignes
Bonjour,
Je cherche à compiler un grand nombre fichiers.
Le souci est que j'atteins rapidement le nombre de ligne max accepté par excel.
Je suis donc contraint de générer plusieurs fichier de compilation 1 à 1.
Est il possible de demander à Excel de continuer la compilation sur une autre feuille d'un même classeur, lorsqu'il atteint sa limite du nombre de ligne?
En vous remerciant par avance pour votre aide.
Cordialement,
Teka
Bonjour à tous !
Et.....
Il est possible d'utiliser la fonction Table.Split pour diviser la table initiale en n tables.
Que comptez-vous faire ensuite, dans Excel, de ces millions de lignes ?
Hey,
Dans un TCD tu n’es pas limité donc tant que tu n’affiches pas 1.4 millions de lignes c’est ok
@+
Bonjour,
Merci pour votre aide.
Je me sens bête, j'aurais du venir demander de l'aide plus tôt. J'ai déjà passé 8h à faire ce traitement de donnée manuellement...
Je dispose de mesures de température délivrées par une sonde depuis 2016 avec une fréquence d'acquisition de 3 minutes. Ce qui fait énormément de données à traiter. Sachant que je dispose d'un fichier .xls par jour, j'ai à compiler toutes les données sur un seul .xls.
Mon objectif est de par la suite moyenner les valeurs délirées par cette sonde pour obtenir une valeur toutes les 15 minutes.
Le format d'heure de l'acquisition est le suivant : 05/02/2020 00:06:44.0
Je pense donc extraire les valeurs d'heure et de minutes, puis faire une formule SI(Val.Minutes comprise entre 0+/- 7; 0 ; Si(Val.Minutes comprise entre 15+/- 7; 15;(Val.Minutes comprise entre 30+/- 7; 0;(Val.Minutes comprise entre 45+/- 7; 0;"").
Cela me permettra (de manière très basique je vous l'accorde) de moyenner les valeurs par pas de 15 minutes par la suite.
J'ai une 30ène de sondes à traiter comme cela.
Je vais m'intéresser à la fonction table.split.
Merci beaucoup.
Hey,
N’hésite pas à fournir un ou deux fichiers exemples histoire de voir ton traitement et voir si on peut aider.
@+
Bonjour,
À partir du moment où tu ne charges pas les données dans la feuille, tu peux t'affranchir de la limite du nombre de lignes.
Crée la requête en connexion uniquement, puis tu l'exploites dans power query, power pivot ou un simple TCD.
A+
Merci pour vos conseils.
J'ai à présent réussi à compiler mes fichier en 13 tables de 1000 000 de lignes.
Je cherche à présent à créer un tableur avec 13 onglets (1 table par onglet).
Sauriez-vous m'indiquer la manoeuvre s'il vous plait?
Hello,
Pourquoi faire ces 13 tables ? Quel est l’objectif maintenant que tu as ta requête fonctionnelle ?
@+
Bonjour,
Une fois mes 13 000 000 de lignes répartie dans 3000 fichiers .xls réunis sous 13 tables de 1 000 000 de lignes, je souhaite travailler sous Excel pour tracer des courbes, épurer les données erronées, faires des moyennes....
Je n'ai pas trouvé le moyen de le faire directement sous power query, et quoi qu'il en soit je préfèrerait retrouver un format .xls classique, car ces données vont être utilisées par pas mal de monde qui ne connaissent même pas l'existence de power query.
Je cherche donc à générer 13 feuilles Excel (ou fichiers) à partir de ces 13 tables.
Merci encore et bonne journée à tous.
Hello,
Dans ce cas là sur le résultat de ta requête tu vas appliquer cette étape supplémentaire :
=Table.Split( TaDernièreEtape , 1000000)Cela va te générer une liste avec 13 tables.
A partir de là, tu vas faire clic droit sur ta requête puis "Référence" 13 fois.
Et dans chaque requête que tu viens de créer qui auront toute la même source tu vas rajouter {0} sur la première, {1} sur la deuxième etc...
Du style :
=RequeteSource{0}Attention si il y a plus de 13 millions de lignes ce sera 14 tables à créer etc
@+
Bonjour à tous !
C'est pure folie que de vouloir intégrer dans Excel des feuilles à 1.000.000 lignes....
Surtout pour ensuite nettoyer les données, faire des moyennes, etc....
Bref.... se servir de Power Query à très mauvais escient.
Bonjour JFL,
J'ai bien conscience que power query est très puissant et me permettrait de faire ce que je souhaite directement.
Mais ne maitrisant pas les fonctions de power, alors que je sais déjà comment faire ça avec des formules .xls, je préfère repartir sur .xls afin de ne pas perdre du temps. J'ai déjà du mal à suivre les quelques instructions basiques que vous me fournissez gentiment (je cherche depuis 30 min comment faire ce que BAROUTE78 m'a indiqué "=RequeteSource{0}").
J'utilise power query simplement pour passer outre la limitation à 1 000 000 de ligne d'Excel.
Bonjour à tous !
C'est précisément maintenant qu'il vous faut commencer l'aventure Power Query.
Le résultat vaut l'investissement initial.
Osez !
Hello,
Une fois le Table.Split effectué sur ta requête tu fais clic droit (étape 1 ci-dessous) puis "Référence" (étape 2 ci-dessous). Cela te crée une nouvelle requête qui a comme source ta table source (étape 3 ci-dessous). Une fois que les 13 ou 14 copiés coller sont faits tu vas sur chaque requête et tu mets les {0}, {1} comme le montre l'étape 4
N'hésite pas si besoin
@+
Merci à vous tous.
Vous êtes sacrément gentils de me donner un coup de main.
Je viens de réussir à faire ce que je souhaitais.
Il est certain que je vais m'intéresser à cet outil à l'avenir, mais petit à petit car malheureusement on est souvent contraint par le temps...
Je vous souhaite à tous une belle journée et je vous remercie encore pour votre précieuse aide.
Bonjour
Comme JFL et BAROUTE78, je crains que tu perdes plus de temps à persister sur des formules Excel : rien que le temps de calcul sur 1000000 de lignes va faire beaucoup de temps mort. D'autant que ta pseudo formule ne semble par optimisée.
Le nombre de fois où j'ai regretté d'avoir repoussé une mise à jour de mes connaissances n'est pas nul mais lointain... car j'ai compris...
Bon, effectivement je pense que je vais devoir passer par Query pour faire mon traitement, car même sous excel classique je ne m'en sors pas.
Je me rends compte des faibles connaissance dont je dispose sous excel...
Je vous explique mon objectif si jamais vous savez comment m'aider (sous query ou excel, au final je ne sais le faire sur aucun des deux).
J'ai une liste conséquente de mesure de température (colonne B) avec leur date et heure d'acquisition (colonne A).
Chaque sonde possède une fréquence d'acquisition différente (l'une va enregistrer une valeur toute les 3 min, alors que l'autre toutes les 7 min).
Je cherche à recaler l'ensemble de mes sondes sur une même fréquence horaire : 1 valeur tous les quart d'heure.
J'imaginais pour cela réaliser une moyenne des températures quart d'heure par quart d'heure.
Il se peut également que certaines sondes n'aient pas de valeur acquise pendant une durée supérieure à 15 minutes, il faudrait donc alors pas qu'un décalage se crée à cause de cette absence de valeur. J'imaginait donc laisser une case vide si aucune valeur n'existe sur la durée de 15 minutes moyennée.
Je vous joins un .xls qui regroupe les valeurs de 2 sondes.
Ces valeurs sont déjà issues d'une compilation via Query.
J'ai volontairement laisser la deuxième sonde en version Brute pour que vous voyez à quoi ça ressemble avant que j'épure les lignes à ne pas considérer.
En gros, toutes les valeurs 1 indiquée sur la colonne C "Epuration" de la deuxième sonde sont des lignes à ne pas considérer.
J'ai donc simplement fait un filtre conditionnel pour les repérer et supprimer plus facilement avant traitement des données.
J'aurai également sans doute des valeurs délirantes parfois, lorsque la sonde perd son étalonnage. Je pense qu'il me faudra tout épurer manuellement.
En vous remerciant à nouveaux
Je continue d'explorer les fonctions de query, j'ai l'impression qu'il me faut utiliser List.Average pour mes moyennes.
Hey,
Possible de donner le résultat attendu d'après les deux onglets fournis ?
@+