Getagged: Power Query

Kalendertabelle – volle 10 Jahre

Warum war ein Monat so umsatzschwach? Wie viele Verkaufstage hat ein Monat? Oder wie sieht mein aktueller Umsatz im Vergleich zum passenden Vorjahreszeitraum aus?

Zahlen sagen nur wenig aus, wenn man sie ohne Kontext betrachtet. Zeitraum, Zeitpunkt, parallel laufende Ereignisse etc.
Zudem sieht der Kalender auch nicht für jeden gleich aus.

Wir betrachten meist das Kalenderjahr – und das schon aus unterschiedlichem Blickwinkel. Für die einen beginnt die Woche am Montag und hat fünf Arbeitstage – für die andere jedoch 6 Verkaufs- und Arbeitstage. Für die einen beginnt die erste Kalenderwoche automatisch am 1. Januar einen Jahres (z. B. in den USA) – für uns in der Woche, in der mehr neue Tage als Tage des alten Jahres sind (also mindestens vier Januartage und somit auch der Donnerstag). Für das eine Unternehmen ist das Wirtschaftsjahr identisch mit dem Kalenderjahr – wenn man sich aber die Geschäftsberichte großer Unternehmen anschaut, beginnt zum Beispiel bei thyssenkrupp das Geschäftsjahr am 1. Oktober eines Jahres und endet am 30. September des Folgejahres. Entsprechend werden auch die Monate anders gezählt. Theater widerum haben neben Kalender- und Geschäftsjahr noch ein zusätzliches Spieljahr … ach ja, Ferien pro Bundesland und abweichende Feiertage gibt es ja auch noch.

Kalendertabellen selbst können Großteils automatisiert errechnet werden. Andere Informationen wie z. B. der Reformationstag aus dem Jahr 2018 und z. B. die Ferien eines Bundeslandes wie Nordrhein-Westfalen müssen manuell ergänzt werden.

In einer neuen Seminarreihe gehe ich auf die Kalendererstellung und Datumsberechnung in Excel selbst, in Power Query mit der eigenen Programmiersprache M, sowie der Erstellung in PowerPivot mit DAX-Funktionen (Data Analysis Expressions) ein. Als i-Tüpfelchen könnte noch die Kalenderberechnung in VBA folgen. Oder schreibt man das mittlerweile iTüpfelchen?

Einige sind ja schon auf mein PDF-Skript gestoßen. Das wird nach und nach fortgeführt: Excel – Datums- und Zeitfunktionen

Auch hatte ich an anderer Stelle schon mal eine Kalendertabelle zur Verfügung gestellt und die Feiertage und Schulferien von NRW ergänzt. Diese Informationen und weitere Ergänzungen gibt es nun zum Download in folgender Excel-Datei (aktualisiert!): Kalendertabelle

Die Datei liegt im XLSX-Format vor und beinhaltet auch Informationen zur Ermittlung von Geschäfts- bzw. Fiskaljahren.

Für alle, die nichts herunterladen wollen oder dürfen, die Spalten enthalten:

  1.  Spalte:   Datum –> Liste manuell gelistet
  2.  Spalte:   Jahr =JAHR([@Datum])
  3.  Spalte:   Monat_kurz =MONAT([@Datum])
  4.  Spalte:   Monat =TEXT([@Datum];“MMMM“)
  5.  Spalte:   Tag Monat =TAG([@Datum])
  6.  Spalte:   Tag Woche =WOCHENTAG([@Datum];2)
  7.  Spalte:   Wochentag =TEXT([@Datum];“TTTT“)
  8.  Spalte:   Tage im Monat =TAG(MONATSENDE(DATUM([@Jahr];[@[Monat_kurz]];1);0))
  9.  Spalte:   KW =KALENDERWOCHE([@Datum];21)
  10.  Spalte:   Quartal =“Q“&AUFRUNDEN([@[Monat_kurz]]/3;0)
  11.  Spalte:   Jahr Quartal =[@Jahr]&“ „&[@Quartal]
  12.  Spalte:   Jahr Monat =TEXT([@Datum];“JJJJ-MM“)
  13.  Spalte:   Halbjahr =WENN([@[Monat_kurz]]<=6;“HJ 1″;“HJ 2″)
  14.  Spalte:   Jahr KW =JAHR([@Datum])-WENN(UND(MONAT([@Datum])=1;
    KALENDERWOCHE([@Datum];21)>51);1;0)+WENN(UND(MONAT([@Datum])=12;
    KALENDERWOCHE([@Datum];21)=1);1;0)&“-„&TEXT(KALENDERWOCHE([@Datum];21);“00“)
  15.  Spalte:   Feiern NRW –> manuell auf WAHR und FALSCH gesetzt
  16.  Spalte:   Feiertage NRW –> manuell auf WAHR und FALSCH gesetzt
  17.  Spalte:   Werktag =WENN([@[Tag Woche]]<=5;“Werktag“;“Wochenende“)
  18.  Spalte:   Verkaufstage Monat NRW =ZÄHLENWENNS([Tag Woche];“<=“&6;[Jahr];JAHR([Datum]);
    [Monat_kurz];MONAT([Datum]);[Feiertag NRW];FALSCH)
  19.  Spalte:   Werktage Monat NRW =ZÄHLENWENNS([Tag Woche];“<=“&5;[Jahr];
    JAHR([Datum]);[Monat_kurz];MONAT([Datum]);[Feiertag NRW];FALSCH)
  20.  Spalte:   FY =rngFYPräfix&WENN(rngStartFiskaljahr=1;[@Jahr];
    WENN([@[Monat_kurz]]<rngStartFiskaljahr;[@Jahr]-1&“/“&RECHTS([@Jahr];2);
    [@Jahr]&“/“&1*RECHTS([@Jahr];2)+1))
  21.  Spalte:   FY M =[@FY]&“ – „&TEXT(WENN([@[Monat_kurz]]<
    rngStartFiskaljahr;12-rngStartFiskaljahr+[@[Monat_kurz]];[@[Monat_kurz]]-rngStartFiskaljahr)+1;“00“)
  22.   Spalte:   FY Q =[@FY]&“ – Q“&AUFRUNDEN((WENN([@[Monat_kurz]]<
    rngStartFiskaljahr;12-rngStartFiskaljahr+[@[Monat_kurz]];
    [@[Monat_kurz]]-rngStartFiskaljahr)+1)/3;0)

Auf dem zweiten Tabellenblatt gibt es zwei Zellen benannte Zellen. Die mit rngStartFiskaljahr benannte Zelle muss auf Werte zwischen 1 und 12 gesetzt werden. Die benannte Zelle rngFYPräfix beinhaltet z. B. „GJ “ für Geschäftsjahr oder „FY “ für fiscal year.

Alle Angaben und Berechnungen sind ohne Gewähr. Also bitte noch einmal prüfen. Vielleicht mag sich auch jemand die Mühe machen und die Feiertage und Ferien für andere Bundesländer eintragen – dies allein war schon irre aufwendig.

So!!! Nun haben wir eine Basis, auf die ich in neuen Videos eingehen werde.
Freue mich auf Feedback bei YouTube und auch Danke an alle, die den Kanal supporten.

Bis bald
Andreas

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 – Power Query – Dateien importieren – csv und txt

Mit Power Query können die Dateien eines Ordners ausgelesen werden. Dabei können in der Abfrage aber auch die Inhalte der Dateien ausgelesen und zu einer Liste zusammengefasst werden.

Bei der Auswahl Ordner / Folder werden die Dateien eines anschließend ausgewählten Verzeichnisses aufgelistet.

Über Extension kann der Dateityp gefiltert werden.

537a

Anschließend auf das Symbol bei Content klicken, um statt der Dateiinformationen die Dateiinhalte auszulesen.

537b

Sollten irrtümlich noch Überschriften mit importiert werden, können diese herausgefiltert werden.

537c

Hier sind die Übungsdateien:
http://1drv.ms/1jRavBS

Hier geht es zum Video:

Videolink: http://youtu.be/auSLYl-BfYQ
Kurzlink dieses Beitrags: http://wp.me/p3egMz-uL

Excel – Power Query – Monatsanfang und Monatsende

Die Funktionen Date.StartOfMonth und Date.EndOfMonth ermitteln passend zu einer Datumsspalte den jeweiligen Monatsanfang und das Monatsende.

Wichtig bei diesen Funktionen ist die korrekte Groß-/Kleinschreibung.
Befinden sich in der Spalte namens „Datum“ z.B. Verkaufsdaten, kann mit Date.EndOfMonth([Datum]) der jeweilige Monatsende errechnet werden.

Natürlich stehen mir Monatsanfang und Monatsende auch über eine vernünftige Kalendertabelle in PowerPivot zur Verfügung. Aber wer weiß schon, wofür diese Spalten benötigt werden können?

Hier gibt es die Übungsdatei:
Excel Power Query 2-1

Hier geht es zum Video:

Videolink: http://youtu.be/nXVa5O7Y9oI

Excel – PowerPivot – Daten aus dem Web per Power Query

Das kostenlose Add-In Power Query (früher Data Explorer) für Excel 2013/2010 ermöglicht die komfortable Anpassung der zu importierenden Daten, Filterung und auch Abfragen per Internet auf dort verfügbare Listen.

448a

Über Power Query – Online Search kann auf Inhalte von Tabellen im Netz zugegriffen werden.

448b

Anschließend einfach einen Suchbegriff eingeben und anschließend mit der Maus über die Trefferliste fahren, um sich die Treffer links anzeigen zu lassen. Erst mit einem Klick wird die Tabelle ins Tabellenblatt geladen.

448c

Der Import der Daten kann noch über Filter & Shape gesteuert werden. So können Spalten abgewählt, Datentypen verändert oder Zeichenketten ausgetauscht werden. Siehe hierzu die Videoreihe zu Data Explorer.

Mit Load to data model wird die Tabelle in das PowerPivot Data Model überführt. Mit einem Ein-/Aus-Schalter lässt sich auch vermeiden, dass die Tabelle vorab in ein Tabellenblatt eingelesen wird.

448d

Hier ist das Video: