Getagged: Add-In

Power Query lernen…

… aber wie damit beginnen?

Seit dem Jahr 2013 beschäftige ich mich mit Power Query – zu Beginn kam es als Add-In unter dem Namen Data Explorer auf den Markt.

Und schon gab es lange Gesichter. Nur für Excel 2010 und Excel 2013… und das noch nicht einmal für alle Versionen von Excel 2010. Aktuell laut Download-Seite von Microsoft für die Office-Versionen:

  • Microsoft Office 2010 Professional Plus mit Software Assurance
  • Microsoft Office 2013

Mit Excel 2016 bzw. Office 365 gehört es dann aber auch in den Excel-Standard über das Register Daten und muss nicht gesondert als Add-In aktiviert werden. Eine Ehre, die bereits Power Pivot in Excel 2013 zuteil wurde (leider auch wieder nicht in allen Versionen).

Power Query wird so regelmäßig erweitert und um neue Funktionen und Importmöglichkeiten ergänzt, dass ein heute erstelltes Skript schon für die nächste Version überarbeitet werden muss. Trotzdem: Es lohnt sich!

So viele Aufgabenstellungen im Bereich Datenimport, Datentransformation und -anreicherung können mit Power Query gelöst werden. Schnittstellen, die früher über Query, VBA oder manuellen Import gelöst wurden und viele manuelle Schritte bzw. SVERWEISe notwendig machten.

Die ersten Schritte in Power Query hatte ich mir selbst erarbeitet, bis ich dann im Mai 2014 Ken Puls (Excel MVP) beim Amsterdam Excel Summit begegnet bin.

AES2015.png

Hier ein Bild vom Amsterdam Excel Summit 2015. Ich bin umringt u.a. von Ken Puls, Jon Peltier, Bob Umlas, Henk Vlootman, Tony de Jonker, Roger Govier, Liam Bastick, Charles Williams, Jan-Karel Pieterse und vielen netten anderen Excel-Freunden.

Ken Puls aus Vancouver (Kanada) betreibt die Seite http://www.excelguru.ca und darunter auch seinen großartigen Blog http://www.excelguru.ca/blog/

Ken war dann auch im Folgejahr in Amsterdam und ich konnte noch mehr Input von ihm mitnehmen. Ich habe Möglichkeiten von Power Query entdecken dürfen, die ich mir niemals selbst hätte erarbeiten können. Mehr Infos zu parametergesteuerten Abfragen, zur Einbettung von Funktionen und tiefergehendes Hintergrundwissen. Ken beschreibt in seinem Blog raffinierte Lösungen und geht auf diverse Alltagsszenarien wie parametergesteuerte SQL-Abfragen ein.

Zudem hat Ken Puls zusammen mit Miquel Escobar das Buch M is for (Data) Monkey geschrieben. Dieser Titel hat mich in Techniken eingeführt, die zum Teil schon im Blog standen, zum Teil aber auch bislang völlig neu für mich waren. Eine unglaublich lehrreiche Lektüre, welche ich euch – auch als ebook – ans Herz legen mag. Zudem gibt es hier Zugang zu Online-Ressourcen, über die man auch sonst nicht verfügbare Datenquellen wie Azure anzapfen kann.

Am wichtigsten sind für mich die eigenen Erfahrungen, die ich in der Praxis bei mir oder beim Kunden sammeln kann. Ich lerne ungemein aus den Blogbeiträgen und der Lektüre, aber auch immer wieder beim Austausch an den Excel-Stammtischen.

Ich hoffe, das kommt hier nicht so als Freakshow rüber. Ja, tatsächlich…. wir treffen uns regelmäßig zu Excel-Stammtischen z.B. am 01.12.2016 in Münster, organisiert durch das ITB der FH Münster. Prof. Dr. Johannes Schwanitz und ich haben den Stammtisch dort ins Leben gerufen und alle können von den Erfahrungen und Problemen der anderen Teilnehmer profitieren. Der direkte Wissensaustausch funktioniert doch viel effizienter, als das alleinige googlen und ausprobieren.

Zuletzt… lernt nicht allein. Ihr bleibt allein schneller an Fragen hängen, könnt nicht von den Erfahrungen und Lösungen anderer profitieren und gebt somit viel schneller auf.

  1. Lernt nicht allein, sucht euch in der Firma einen Sparringspartner
  2. Tauscht euch mit anderen aus, egal ob in Foren oder live
  3. Schaut in nützliche Blogs und YouTube-Kanäle
  4. Experimentiert mit Power Query
  5. Arbeitet das Buch von Ken durch
  6. Besucht Seminare zu diesem Thema… seht die Teilnahmegebühr als Investition, nicht als Kosten.

Träumt nicht von Excel!
Euer Andreas

 

 

 

Excel – Data Explorer Preview – Ordner auslesen

In meinem dritten Blog-Beitrag zum Add-In Data Explorer Preview für Excel 2010 / 2013 geht es um das Auslesen von Ordnern und Unterordnern.

Im Register Data Explorer kann man From Folder auswählen. Hier werden Verzeichnis- und Dateiinformationen eingelesen.

E367

Zunächst muss jedoch ein Verzeichnis (1) ausgewählt werden. Anschließend wird mit OK (2) bestätigt.

E367_2

Der Name der Abfrage wird vermutlich Query1 lauten (3). Über einen rechten Mausklick lässt sich der Name der Abfrage umbenennen. Hier möchte ich ein Bilderverzeichnis auslesen und benenne entsprechend um.

Anschließend möchte ich noch die Dateigrößen sehen. Diese verbergen sich in den Attributen (4). Zunächst wähle ich alle Einträge mit Select All Columns ab und aktivere dann das Kästchen bei Size. Mit OK (5) bestätigen.

E367_b

Die Ordnerstruktur kann ebenfalls über mehre Spalten verteilt werden. Über einen rechten Mausklick auf Folder Path und dort Split Column kann der Trennwert By Delimiter definiert werden.

E367_3

Statt des üblichen Kommas, Semikolons etc. wähle ich den Eintrag Custom und gebe den Schrägstrich nach links (Backslash) ein. Auch hier wird natürlich mit OK bestätigt.

E367_4

Anschließend muss ich noch erkennen, dass hinter jedem Bildnamen noch entweder .jpg oder .JPG steht. Über einen rechten Mausklick auf die Spalte Name ersetze ich die entsprechenden Werte.

E367_5

Bei Value To Find gebe ich erst .jpg ein und tausche gegen Nichts aus. Nach einem OK rufe ich Replace erneut auf und tausche auch .JPG gegen Nichts. Groß- und Kleinschreibung wird also unterschieden.

E367_6

Zum Schluss führe ich die Abfrage aus und erhalte eine Auflistung der Dateien im angegebenen Verzeichnis inkl. aller Unterverzeichnisse.

E367_7

Video bei YouTube:

Excel – Data Explorer Preview – XLSX einlesen

Im letzten Blog vom 12.05.2013 habe ich kurz das neue Add-In „Data Explorer“ Preview für Excel 2010 / 2013 beschrieben. Noch ist das Add-In nicht final, noch steht es nur in englischer Sprache zur Verfügung.

Eine Option im Register Data Explorer ist die Möglichkeit, Excel-Dateien einzulesen.

E365_1

Nachdem eine Datei ausgewählt wurde, können auf der linken Seite die Tabellen der Datei ausgewählt werden (1).

E365_2

Anschließend muss ich hier leider feststellen, dass die erste Zeile nicht als Überschriftenzeile erkannt wurde (2). Über einen rechten Mausklick in die obere Zeile kann die erste Zeile des Imports zur Überschriftenzeile erklärt werden (3) – Use first row as headers. Anschließend klicke ich auf Done (4).

Die Daten werden nun auf ein neues Tabellenblatt übertragen. Im Register Tabellentools taucht zusätzlich der Eintrag Query auf. Ich muss hier aber erkennen, dass die Datumswerte nicht als solche übernommen wurden (5).

E365_3

Zudem finde ich den Namen der Abfrage mit Query1 nichtssagend (6). Diesen möchte ich ändern. Nachträgliche Änderungen können jederzeit über einen Klick auf Filter & Shape (7) durchgeführt werden.

Mit einem rechten Mausklick auf den Namen der Abfrage (8) kann der Eintrag umbenannt werden. Ich vergebe hier zum Beispiel den sprechenden Namen qry_Team_1.

E365_4

Anders beim Datum. Dies wurde als Zahl interpretiert. Klicken Sie mit der rechten Maustaste auf den Feldnamen Date und dann auf Change Type und dann auf Date (9). Wählen Sie hier ein Format aus.

Das Ergebnis der gesamten Umformatierung können Sie in der oberen Zeile (10) ablesen. Bestätigen Sie anschließend wieder mit Done.

Sind mehrere Spalten markiert, können diese auch gruppiert werden. Als Ergebnis lassen sich die Summen festgelegter Spalten ziehen oder die Anzahl der Datensätze anzeigen.

Werden Daten in der Datenquelle verändert, kann über einen Refresh der Datenbestand aktualisiert werden.

Übungsdatei zum Download – ca. 6 MB:
E365_Lange_Liste

Video bei YouTube: