Power Query ist ein neues Tool von Microsoft zum Extrahieren, Transformieren und Laden von Daten. Der heutige Artikel befasst sich mit der Verarbeitung aller Dateien in einem Ordner.
Power Query ist in Excel 2016 integriert und steht in bestimmten Versionen von Excel 2010 und Excel 2013 als kostenloser Download zur Verfügung. Das Tool dient zum Extrahieren, Transformieren und Laden von Daten aus verschiedenen Quellen in Excel. Das Beste daran: Power Query merkt sich Ihre Schritte und gibt sie wieder, wenn Sie die Daten aktualisieren möchten. Zum Zeitpunkt der Drucklegung dieses Buches befinden sich die Power Query-Funktionen in Excel 2016 auf der Registerkarte Daten in der Gruppe Get & Transform unter New Query. Es ist schwer vorherzusagen, ob Microsoft Power Query rückwirkend in Get 2010 in Excel 2010 und Excel 2013 umbenennen wird.
Dieses kostenlose Add-In ist so erstaunlich, dass es ein ganzes Buch darüber geben könnte. Aber als einer meiner Top-40-Tipps möchte ich etwas sehr Einfaches behandeln: eine Liste von Dateien in Excel zusammen mit dem Erstellungsdatum der Datei und möglicherweise der Größe. Dies ist nützlich, um eine Liste mit Budget-Arbeitsmappen oder eine Liste mit Fotos zu erstellen.
In Excel 2016 wählen Sie Daten, Neue Abfrage, Aus Datei, Aus Ordner. Verwenden Sie in früheren Excel-Versionen Power Query, From File, From Folder. Geben Sie den Ordner an:
Klicken Sie beim Bearbeiten der Abfrage mit der rechten Maustaste auf nicht gewünschte Spalten und wählen Sie Entfernen.
Klicken Sie auf dieses Symbol in der Spalte Attribute, um die Dateigröße abzurufen:
Eine Liste zusätzlicher Attribute wird angezeigt. Größe auswählen.
Eine große Liste von Transformationsoptionen ist verfügbar.
Wenn Sie mit der Bearbeitung der Abfrage fertig sind, klicken Sie auf Schließen und Laden.
Die Daten werden als Tabelle in Excel geladen.
Um die Tabelle später zu aktualisieren, wählen Sie Daten, Alle aktualisieren. Excel merkt sich alle Schritte und aktualisiert die Tabelle mit einer aktuellen Liste der Dateien im Ordner.
Eine vollständige Beschreibung der Funktion, die früher als Power Query bekannt war, finden Sie unter M is for (Data) Monkey von Ken Puls und Miguel Escobar.
M steht für (DATEN) AFFE »
Vielen Dank an Miguel Escobar, Rob Garcia, Mike Girvin, Ray Hauser und Colin Michael für die Nominierung von Power Query.
Schau Video
- Die Power Query-Tools befinden sich in Excel 2016 auf der Registerkarte Daten
- Kostenloses Add-In für 2010 und 2013
- Listen Sie alle Dateien aus einem Ordner mithilfe von Power Query im Excel-Raster auf
- Wählen Sie Neue Abfrage, Aus Datei, Aus Ordner
- Nicht offensichtlich: Erweitern Sie das Attributfeld, um die Größe zu erhalten
- Wenn sich Ihre Daten in CSV-Dateien befinden, können Sie alle Dateien gleichzeitig in ein einziges Raster importieren
- Bewerben Sie die Überschriftenzeile
- Löschen Sie die verbleibenden Kopfzeilen
- Ersetzen Sie "" durch null
- Füllen Sie für die Umrissansicht aus
- Löschen Sie die Gesamtsummenspalte
- Heben Sie die Daten auf
- Formel zum Konvertieren von Monatsnamen in Datumsangaben
- Vollständige Liste der Schritte - weltweit größtes Rückgängigmachen
- Nächster Tag - Aktualisieren Sie die Abfrage, um alle Schritte erneut auszuführen
Abschrift des Videos
- Power Query ist in Windows-Versionen von Excel 2016 integriert. Überprüfen Sie die Registerkarte Daten in der Gruppe Get & Transform. Wenn Sie 2010 oder haben
- 2013, solange Sie Windows ausführen
- und nicht Mac alles, was hier in Get & Transform ist
- Sie können kostenlos von Microsoft herunterladen. Suchen Sie einfach nach
- Laden Sie Power Query herunter.
- Heute bin ich daran interessiert, Power Query zu verwenden, um eine Dateiliste zu erhalten. ich
- möchte alle Dateien in einem Ordner auflisten.
- Vielleicht muss ich sehen, welche Dateien die sind
- große Dateien oder ich muss sortieren oder ich brauche
- Sie wissen, eine Kombination von Ihnen zu bekommen
- kennen die Budgetdateien, die wir verschickt haben
- und dann einen anderen Ordner welche
- wir kamen kamen zurück.
- Gehen Sie zunächst zu Daten, Abrufen und Transformieren, Aus Datei, Aus Ordner.
- Fügen Sie den Ordnerpfad ein oder verwenden Sie die Schaltfläche Durchsuchen.
- Klicken Sie auf OK und sie zeigen mir dies
- Vorschau. Wählen Sie Bearbeiten.
- Ein paar Dinge hier sehen Sie, die wir haben
- der Dateiname die Erweiterung das Datum
- Zugriff, Änderungsdatum, Erstellungsdatum.
- Es ist wirklich nicht offensichtlich, dass dieses Symbol neben der Überschrift "Attribute" "Erweitern" bedeutet. Klicken Sie auf dieses Symbol und es sind weitere Inhalte vorhanden
- hier und wenn Sie auf dieses Symbol klicken, dann ich
- kann hineingehen und Dinge wie Dateigröße bekommen
- oder wenn es schreibgeschützt ist und Dinge wie
- das so in diesem Fall möchte ich nur Datei
- Größe. Wählen Sie Dateigröße. OK klicken. Sie geben Ihnen ein neues Feld mit dem Namen Attributes.Size.
- Ich kann sehen, wie viele Bytes drin sind
- jede Datei.
- Vielleicht brauche ich hier nicht alles
- Ich brauche das Erstellungsdatum nicht, damit ich kann
- Rechtsklick und sag, dass ich will
- Entfernen Sie diese Spalte. Dies
- binär brauche ich nicht das wird entfernen
- diese Spalte. Klicken Sie in der Multifunktionsleiste auf Schließen und Laden.
- In wenigen Sekunden haben Sie eine sortierbare Ansicht von
- alles in diesem Ordner, wenn der Ordner
- Änderungen kann ich hier reinkommen und ich kann
- Aktualisieren Sie die Abfrage und sie wird zurückgehen
- raus und zieh die Daten rein das ist
- Für mich ist das ein Problem, das wir früher hatten
- haben die ganze Zeit würden wir 200 verschicken
- Budget-Dateien
- und du bekommst jemanden zurück, nicht alle
- zurück muss man so vergleichen können
- Jetzt kann ich im Wesentlichen eine Suche durchführen
- zwischen Ordnern.
- Es ist einfach unglaublich wie
- cool ist es aber schau lass uns darüber hinaus gehen
- 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.
- Dieses Buch wird lehren
- Sie alles über die Power-Abfrage
- Schnittstelle Es ist ein erstaunliches Buch das Beste
- Buch über Machtabfrage alles, was ich gelernt habe
- Ich habe aus diesem Buch gelernt. Ich stieg in einen Flug von
- Orlando nach Dallas - ich habe das ganze Buch gelesen
- und mein Wissen über Power Query nur
- In zwei Stunden sind Sie auf dem Laufenden
- beschleunigen und ersetzen Sie Dinge, die Sie würden
- Ich hatte früher mit VBA gearbeitet.
Download-Datei
Laden Sie die Beispieldatei hier herunter: Podcast2037.xlsx
