Power Query est un nouvel outil de Microsoft pour extraire, transformer et charger des données. L'article d'aujourd'hui porte sur le traitement de tous les fichiers d'un dossier.
Power Query est intégré à Excel 2016 et est disponible en téléchargement gratuit dans certaines versions d'Excel 2010 et d'Excel 2013. L'outil est conçu pour extraire, transformer et charger des données dans Excel à partir de diverses sources. La meilleure partie: Power Query se souvient de vos étapes et les lira lorsque vous souhaitez actualiser les données. Au moment de la mise sous presse de ce livre, les fonctionnalités de Power Query dans Excel 2016 se trouvent sous l'onglet Données, dans le groupe Obtenir et transformer, sous Nouvelle requête. Il est difficile de prédire si Microsoft renomme rétroactivement Power Query en Get & Transform dans Excel 2010 et Excel 2013.
Ce complément gratuit est tellement incroyable qu'il pourrait y avoir un livre entier à ce sujet. Mais comme l'un de mes 40 meilleurs conseils, je veux couvrir quelque chose de très simple: apporter une liste de fichiers dans Excel, avec la date de création du fichier et peut-être sa taille. Ceci est utile pour créer une liste de classeurs budgétaires ou une liste de photos.
Dans Excel 2016, vous sélectionnez Données, Nouvelle requête, À partir d'un fichier, À partir d'un dossier. Dans les versions antérieures d'Excel, utilisez Power Query, à partir d'un fichier, à partir d'un dossier. Spécifiez le dossier:
Lors de la modification de la requête, cliquez avec le bouton droit sur les colonnes que vous ne souhaitez pas et choisissez Supprimer.
Pour obtenir la taille du fichier, cliquez sur cette icône dans la colonne Attributs:
Une liste d'attributs supplémentaires apparaît. Choisir la taille.
Une grande liste d'options de transformation est disponible.
Lorsque vous avez terminé de modifier la requête, cliquez sur Fermer et charger.
Les données se chargent dans Excel sous forme de tableau.
Plus tard, pour mettre à jour la table, sélectionnez Données, Actualiser tout. Excel se souvient de toutes les étapes et met à jour le tableau avec une liste actuelle des fichiers dans le dossier.
Pour une description complète de la fonctionnalité anciennement connue sous le nom de Power Query, consultez M is for (Data) Monkey de Ken Puls et Miguel Escobar.
M est pour (DATA) MONKEY »
Merci à Miguel Escobar, Rob Garcia, Mike Girvin, Ray Hauser et Colin Michael pour la nomination de Power Query.
Regarder la vidéo
- Les outils Power Query se trouvent sur l'onglet Données dans Excel 2016
- Complément gratuit pour 2010 et 2013
- Répertoriez tous les fichiers d'un dossier dans la grille Excel à l'aide de Power Query
- Choisissez Nouvelle requête, à partir du fichier, à partir du dossier
- Pas évident: développez le champ d'attribut pour obtenir la taille
- Si vos données sont dans des fichiers CSV, vous pouvez importer tous les fichiers à la fois dans une seule grille
- Promouvoir la ligne d'en-tête
- Supprimer les lignes d'en-tête restantes
- Remplacez "" par null
- Remplir pour la vue de contour
- Supprimer la colonne du total général
- Annuler le pivot des données
- Formule pour convertir les noms de mois en dates
- Liste complète des étapes - La plus grande annulation au monde
- Jour suivant - actualisez la requête pour refaire toutes les étapes
Transcription de la vidéo
- Power Query est intégré aux versions Windows d'Excel 2016. Recherchez l'onglet Données dans le groupe Obtenir et transformer. Si vous avez 2010 ou
- 2013 tant que vous exécutez Windows
- et pas Mac tout ce qui est ici dans Get & Transform
- vous pouvez télécharger gratuitement sur Microsoft. Recherchez simplement
- Téléchargez Power Query.
- Aujourd'hui, je suis intéressé par l'utilisation de Power Query pour obtenir une liste de fichiers. je
- souhaitez répertorier tous les fichiers d'un dossier.
- Peut-être ai-je besoin de voir quels fichiers sont
- fichiers volumineux ou j'ai besoin de trier ou j'ai besoin
- tu sais pour avoir une combinaison de toi
- connaître les fichiers budgétaires que nous avons envoyés
- puis un dossier différent lequel
- nous sommes revenus.
- Pour commencer, accédez à Données, Obtenir et transformer, À partir d'un fichier, À partir d'un dossier.
- Collez le chemin du dossier ou utilisez le bouton Parcourir.
- Cliquez sur OK et ils me montrent ceci
- Aperçu. Choisissez Modifier.
- Un couple de choses ici, vous voyez que nous avons
- le nom du fichier l'extension la date
- consulté, date de modification, date de création.
- Il n'est vraiment pas évident que ce symbole à côté de l'en-tête Attributs signifie Développer. Cliquez sur ce symbole et il y a plus de choses dans
- ici et si vous cliquez sur ce symbole, je
- peut entrer et obtenir des choses comme la taille du fichier
- ou s'il est en lecture seule et des choses comme
- donc dans ce cas je veux juste un fichier
- Taille. Choisissez la taille du fichier. Cliquez sur OK. Ils vous donnent un nouveau champ avec un nom de Attributes.Size.
- Je peux voir combien d'octets sont dans
- chaque fichier.
- Peut-être que je n'ai pas besoin de tout ici peut-être
- Je n'ai pas besoin de la date créée pour pouvoir
- faites un clic droit et dites que je veux
- supprimer cette colonne. Cette
- binaire dont je n'ai pas besoin qui supprimera
- cette colonne. Dans le ruban, cliquez sur Fermer et charger.
- Dans quelques secondes, vous aurez une vue triable de
- tout dans ce dossier si le dossier
- changements je peux venir ici et je peux
- actualisez la requête et elle reviendra
- et extraire ces données à droite c'est
- pour moi c'est un problème auquel nous avions l'habitude
- avoir tout le temps que nous enverrions 200
- fichiers de budget
- et tu récupères quelqu'un pas tous
- de retour, vous devez pouvoir comparer
- maintenant je peux essentiellement faire un vlookup
- entre les dossiers.
- C'est juste incroyable comment
- c'est cool mais regarde allons au-delà
- what I have in the book and show you how
- that's just the tip of the iceberg.
- I'm going to create another query. Data, New Query, From File, From Folder.
- I'll copy that folder path here.
- click edit.
- As of October 2016, this trick only works with CSV
- files, but in 2017 it was updated to work with single-sheet Excel files. I
- have a folder a whole bunch of files and
- I want to create one excel grid with all
- of the data from all of these files.
- It's not intuitive at all. Look next to the heading for the Binary column. There is an icon with two arrows pointing down at a horizontal line.
- Click that.
- BAM! it just pulled in every single record from
- every single file in that folder!
- Isn't
- that amazing I mean that was a VBA macro
- before and it takes months to learn VBA
- macros you can learn power query in ten
- minutes.
- We have to select this column and
- go to replace values say that we're
- going to
- replace nothing with the word null click
- okay
- That'll give us Nulls in place of empty cells.
- Those nulls allow us to use this amazing
- featured called Fill Down. Watch that
- column when I choose Fill Down. BAM it
- just pulled in all of that outline view
- and brought the value down.
- I don't need the Grand Total column.
- Right-click and remove.
- Now at this point you say oh yeah hey we could
- pull this in and it'd be awesome. But if
- we wanted to create a pivot table from
- this data having a repeating group going
- across Jan Feb Mar is not a good format
- for a pivot tables.
- Right now we have 47
- rows I need to have 47 times twelve rows
- and to do this in a regular Excel file
- it is horrendous using a Multiple
- Consolidation Range that I learned from
- Mike Alexander at Data Pig Technologies.
- But it is easy in Power Query. Check this out I'm going to choose the
- label columns along the left. These are the things that I don't
- want to change and then on the Transform tab, choose Unpivot Other Columns.
- We go from 47 rows to 564 rows
- that's an amazing step.
- Here you can see
- that these values are text. It is easy enough to
- change it to either currency or a whole
- number. Right click the heading and choose Rename and call it
- revenue
- How about these months? They're
- all text such as Jan, Feb, Mar. Here's an awesome way to fix
- that we go to add column add a custom
- column doesn't matter what the name you use.
- The calculation, in quotes, is " 1, 2016". Click OK.
- Now we have this new custom column I'm going to take the
- attribute column containing Month names and the new custom column. Select both columns
- and say I want to merge those columns
- with a space in between and call it date.
- Click OK. That looks enough
- like a real date that when I go to
- transform and change it to a date it
- converts it to a true Excel date.
- At this point these two temporary
- columns I can right click and remove.
- Now you could be saying to yourself:
- Wait, Bill! we could have done all of this in Excel and that's absolutely true we could have done all of it in Excel it would have been harder to get all the CSV files into one file it would have taken longer to fill in the blanks it definitely would have taken longer to do the unpivot operation but here's the thing look over on the right-hand side we haven't talked about Applied Steps at all. The Applied Steps panel is like the world's greatest undo if you need an audit trail if the auditors come and say well how did you get from all these CSV files to this file that we're building our financial statements on you can go back and show what it looked like at each step along the way. If you screwed something up back here you could change or edit that step. Next, on the Power Query Home tab, choose Close and Load. So here's our data set this is based on all of the files in this folder. Let's build a little pivot table from here insert pivot table existing worksheet right here and I'll put revenue in the values area products down the left hand side you see that we have six million in revenue. Well that's today's data now tomorrow tomorrow let's say that we get a couple of new customers a couple of new files come along and our IT department takes those and dumps them into our folder all I have to do is reopen this file select the query come over here and refresh and then come here analyze refresh the pivot table and we have the new data! Power Query is faster on day one maybe by a factor of 20-30%. On day two is faster by 99% it's an absolutely amazing product Power Query. It's in Excel 2016 but if you're in 2010 or 2013 for Windows you're more than welcome to go out and download it for free. The book that will teach you about Power Query is M is for (Data) Monkey" by Ken Puls and Miguel Escobar.
- Ce livre enseignera
- vous tout sur la requête d'alimentation
- interface c'est un livre incroyable le meilleur
- livre sur power query tout ce que j'ai appris
- J'ai appris de ce livre. J'ai pris un vol de
- Orlando à Dallas - J'ai lu tout le livre
- et ma connaissance de Power Query juste
- monté en flèche en deux heures, vous pouvez être jusqu'à
- accélérer et remplacer les choses que vous voudriez
- ont eu l'habitude d'en avoir fait avec VBA.
Télécharger un fichier
Téléchargez l'exemple de fichier ici: Podcast2037.xlsx
