Kategorie: Power Query
Power Query und Pivot-Tabelle mit Datenmodell
Ein weiterer Workshop! Doch dieses mal werden wir mit Hilfe von Power Query Daten ins Excel importieren und von dort aus mit dem Datenmodell weiterarbeiten. Mit Hilfe von DAX-Funktionen werden Measures gebildet, die dann in der Pivot-Auswertung zur Verfügung stehen.
Übungsdateien des Videos:
Zunächst wieder die reinen Verkaufsdaten in einer Liste auf einem Tabellenblatt:
Anschließend eine Datei mit einer Kalendertabelle auf einem Tabellenblatt und zwei Tabellen auf dem zweiten Tabellenblatt:
Die folgenden Steuerdaten werden zusätzlich benötigt. Hier greife ich per Webanfrage auf die Tabelle zu. Dafür muss der Link zu diesem Blog-Beitrag kopiert werden:
https://thehosblog.com/2022/03/04/power-query-und-pivot-tabelle-mit-datenmodell/
| Land | Mehrwertsteuer |
| Deutschland | 19% |
| Niederlande | 21% |
| Österreich | 20% |
| Schweiz | 7,7% |
Die über Power Query abgefragten Tabellen werden im Datenmodell oder schon vorab in Power Query miteinander verknüpft. Informationen, die später in Pivot nicht verfügbar sein sollen, werden vorab im Datenmodell ausgeblendet. So benötigt man nicht den Umsatz der ursprünglichen Tabelle, sondern das mit Hilfe der DAX-Funktion berechnete Measure. Auch die Filialen tauchen tausendfach in den Sales-Daten auf. Hier benötige ich lediglich das in der Filialtabelle enthaltene Feld für spätere Filterzwecke.
Mit den ins Datenmodell importierten Daten können nun nach der Erstellung von DAX-Funktionen Auswertungen gefahren werden.
Das Tabellenblatt kann nun kopiert und in Kopie weiter modifiziert werden.
Hier geht es zum YouTube-Video… folgt – wird neu erstellt. Danke an den Hinweis zum Fehler im Video an Olaf!
Videolink: folgt
Pivot-Tabellen und Power Query – Neue Reihe
Du möchtest aus Deinen Daten eine für Deine Zwecke aussagekräftige Auswertung erzeugen? Du musst Daten aus diversen Datenquellen zusammenfügen und die Informationen verdichten?
Dann sind Power Query und Pivot-Tabellen genau die richtigen Werkzeuge für Dich!
Einen kurzen Blick in die neue Reihe findest Du im folgenden Intro-Video:
Videolink: https://youtu.be/sE94pP1KODQ
Doch bevor ich auf die modernen Pivot-Tabellen mit Datenmodell eingehe, möchte ich mit Dir eine klassische Pivot-Tabelle aufbauen. Im folgenden Bild erkennst Du das Endergebnis des Videos.
Und so sahen die Ausgangsdaten aus Datei 1 aus.
Die Technik wird auch noch für Online- und Mac-Nutzer benötigt. Für einige genutzte Funktionen benötigst Du Excel 2019 oder neuer (z. B. die Funktion TEXTVERKETTEN). Die anderen Techniken funktionieren aber schon bei älteren Excel-Versionen.
Die Übungs- und Ergebnisdateien für das folgende YouTube-Video kannst Du hier herunterladen:
Ausgangsdatei für das erste Trainingsvideo:
Ergebnisdatei:
An diesen Pivot-Tabellen – die tolle und mächtige Ergebnisse liefern – gibt es aber einige Schwachstellen, welche ich dann in späteren Videos mit Hilfe der Pivot-Tabellen mit Datenmodell und den DAX-Funktionen (data analysis expressions) beheben werde.
Wenn Du mich auf dieser Reise durch Power Query und Pivot begleiten magst, würde ich Dich bitten, die Videos zu kommentieren, zu bewerten und den Kanal zu abonnieren. Für mich der einzige Ansporn, weiter kostenlosen Content bei YouTube hochzuladen.
Videolink: https://youtu.be/MTcCcrQ3rM0
Ich freue mich auf eine spannende Reise
Andreas
P.S.: Die Reihe steht Dir kostenlos zur Verfügung, Du kannst die Videos auch gerne ins Unternehmen verlinken. Die Übungsdateien dürfen in unveränderter Form unter Nennung der Quelle und des Autors kommerziell genutzt werden.
Pivot – Datenmodell mit Zeitintelligenz per DAX-Funktionen
Ohne die Verwendung von Pivot-Tabellen macht die Auswertung vielfältiger Informationen einfach keinen Spaß, ist extrem aufwändig und wird per Formeln höchst rechenintensiv und undynamisch. Seit Office 97 stehen uns Pivot-Tabellen direkt in Excel zur Verfügung. Ein tolles Tool… ich habe da sogar vor meiner Selbstständigkeit meine Diplom-Arbeit mit dem Thema “Deskriptive Statistik mit Microsoft Excel” zu geschrieben. Als Student und Tutor im Bereich Angewandte Sozialforschung war ich von Anfang an vom Potential und den Möglichkeiten der Pivot-Tabellen überzeugt. Vor allem, da es über Excel eine so große Verbreitung genoss.
Anfangs wurden die Pivot-Tabellen erst sehr sparsam genutzt. Sie waren aber auch sehr gut im Menü Daten verborgen. Pivot-Auswertungen erfreuen sich gerade in den letzten Jahren immer größerer Beliebtheit und Verwendung. Aber leider sind sie immer noch nicht bei der Auswertung von Daten in jedem Standardrepertoire eines Excel-Nutzers vorhanden. Und sie haben bis einschließlich Excel 2010 noch ein paar eindeutige Schwächen.
Und dann wurde auf einmal ALLES anders!
Microsoft veröffentlichte für Excel 2010 das Add-In PowerPivot, welches vorher unter dem Codenamen Genesis intern von der SQL-Crew entwickelt und dem Excel-Team vorgestellt wurde. Plötzlich konnten Unmengen von Daten aus externen Datenquellen angezapft und ausgewertet werden. Alte Mängel durch Nutzung des anfälligen Pivot Caches wurden behoben. Und durch eine für Excel neue Speichertechnik und von SQL (MDX) zu Excel migrierte zusätzliche DAX-Formeln (data analysis expressions) erweitern sich seitdem die Auswertungsmöglichkeiten.
Meine ersten YouTube-Videos zu PowerPivot aus Februar 2011 zeigen noch meine ersten Gehversuche. Schnell war klar, dass man auch besser und dynamischer an neue Daten und Datenquellen kommen muss. 2012 wurde dann Data Explorer als Add-In nachgereicht und kurze Zeit später in Power Query umbenannt. Allerdings war das Add-In PowerPivot noch nicht so stabil und führte häufiger zu Abstürzen. Mit Excel 2013 wurde dann endlich PowerPivot fest in Offfice 2013 integriert und damit auch die neue Speichermöglichkeit und Verarbeitung großer Datenmengen. Aus dem PowerPivot-Datenmodell wurde das Excel-Datenmodell. Und auch Power Query ist seit Excel 2016 fester Bestandteil von Excel. Und ja, es gab auch Power View und GeoFlow umbenannt in Power Map bzw. jetzt 3D-Karten.
Meine bei YouTube sehr häufig aufgerufenen und auch von Universitäten genutzten Pivot-Lehrvideos beziehen sich oft noch auf den alten Pivot Cache-Versionen ohne Verwendung des Datenmodells.
Eine neue Videoreihe zu Pivot wird sich auf die neuen Möglichkeiten des Datenmodells unter Verwendung von Power Query stützen. Das so erworbene Wissen lässt sich dann aber auch eine Stufe weiter in Power BI gut verwenden.
Eine neue Demodatei steht im XLSX-Format zur Verfügung:
Um die Beispiele optimal nachzuvollziehen wäre die Verwendung von Excel 2016 oder neue optimal. Aber auch Excel 2010 mit aktivierten Add-Ins PowerPivot und Power Query wäre nutzbar.
Für dieses kostenlos zur Verfügung gestellte Projekt brauche ich eure Unterstützung!
Wenn euch die Tutorials gefallen,
- empfehlt den Kanal bitte weiter!
- abonniert den Kanal!
- lasst einen Daumen nach oben da!
- und schreibt bitte gelegentlich einen Kommentar!
Interaktivität auf dem Kanal ist sehr wichtig, gibt mir Feedback, wie und ob die Videos bei euch ankommen und verbessert zudem das Ranking der Titel.
Und wenn euch einige Tipps einen echten Vorteil für den oder im Job gebracht haben und ihr auch Potential für euer Unternehmen seht, dann
- packt doch einige der Links zu den Videos auf eure firmeninterne Intranetseite! Eine reine Verlinkung auf meine YouTube-Videos, vielleicht noch mit einem Kommentar von euch ist immer erlaubt.
- gebt einen Link zum Kanal an eure Personalabteilung weiter!
- überlegt bitte, wieviel Zeit ihr durch ein wenig Fortbildung und Erweiterung eures Excel-Wissens gewinnen könnt! Als Berater und Trainer bin ich im ganzen deutschsprachigen Raum unterwegs… aktuell online, bald auch hoffenltlich wieder physisch.
Und danke nochmal für den regen und sehr aktiven Austausch auf unseren Excel-Stammtischen, welche auch aktuell online durchgeführt werden. Ich und andere können hier eine Menge voneinander lernen.
Vielen Dank auch an diejenigen, die oben die Schalter 700+ Excel-Stick und Kanal unterstützen gefunden haben. Das Betreiben des Kanals und die Produktion der Titel kosten mich nicht nur Zeit, sondern auch einige Taler. Merci *verbeug*
Im Juni gibt es dann das 10jährige Bestehen des YouTube-Kanals. Hoffentlich mit kleiner Grillparty 😉
Bleibt gesund!
Viel Erfolg und
bis bald
Andreas
Bulgarian Excel Days 2019 in Sofia
Was für ein Feuerwerk!!! Und wie schade, das wir hier in Deutschland ein solch krass gutes Event nicht stemmen können. Oder?
Zwischen dem 20. und 23. November 2019 haben sich in Sofia über 400 Personen zu den Bulgaria Excel Days 2019 zusammengefunden. Neben diversen eintägigen Masterclasses in PowerPivot, Power Query und Financial Controlling gab es noch eine Großveranstaltung in der einem in kurzen Vorträgen die Informationen nur so um die Ohren geflogen sind. Unglaublich wertvoll, spannend vorgetragen und für unsere Verhältnisse dann auch noch günstig.
Mit dabei waren unter anderem
- die Gastgeberin und Excel MVP Boriana Petrova
- aus Australien MVP Liam Bastick – absolut fit in Financial Modelling
- aus den Niederlanden MVP Tony der Jonker, ebenfalls Financial Modelling und Organisator der Excel Experience Days und des Amsterdam Excel Summit
- aus dem schönen Wien Excel MVP und YouTube-Kollegin Leila Gharani
- aus Kanada beehrte und MVP Ken Puls mit krass-guten Themen zu Power Query
- aus Slowenien der brilliante Excel MVP Gašper Kamenšek. Gašper organisiert die aktuell stattfindende Excel Olympics Conference 2019 in Ljubljana
- Excel MVP Frederic Le Guen aus Frankreich mit ein paar wirklich kleveren Tricks
- einige Mitglieder des für Excel verantwortlichen Microsoft Teams
- und so viele mehr…
Glaubt mir bitte: die waren alle wirklich sehr gut und haben neben einer unterhaltsamen und lehrreichen Performance auch viel Lehrmaterialien zur Nachbereitung mitgeliefert.
Ken Puls und Gašper Kamenšek haben mir in den Masterclasses den Horizont erweitert.
Von dem, was ich dort alles gesehen und gelernt habe, kann und werde ich euch nicht alles über den kostenlosen YouTube-Kanal oder die Stammtische zur Verfügung stellen können. Meine Schulungs- und Beratungskunden profitieren natürlich davon!
Das was ich dort gesehen habe, kann ich kaum mit einem Buch oder Videokurs lernen. Die Vorträge liefern mir aber einen Ansatzpunkt, wo ich mit der Fortbildung in Power Query, PowerPivot und Co. weitermachen kann und sollte. Und es gibt natürlich super Kurse, wie z.B. Power Query von Ken Puls und PowerPivot von Rob Collie und Matt Allington.
Wenn Ihr mit Excel arbeitet und von Excel massiv für euren Job profitiert, warum nehmt ihr nicht an solchen internationalen Konferenzen teil? Ihr profitiert nicht nur vom Wissen der Besten der Besten der Besten, ihr habt auch die Möglichkeit des direkten Austauschs und könnt euch als kleine Entschädigung für den Reiseaufwand tolle Städte anschauen. Sofia hat mich echt umgehauen! Also die Stadt!
Ein bisschen Spaß durfte nicht fehlen… Tony, Frederic, ich und Leila.
Einen ähnlich positiven Bericht werde ich aus Ljubljana abliefern dürfen. Ich habe heute einen DAX PowerPivot-Kurs bei MVP Matt Allington absolviert, genieße hier die Gastfreundschaft von Gašper Kamenšek und bin mal wieder erstaunt… ich bin der einzige Deutschsprachige hier…
Sonnige Grüße
Andreas
P.S.: Schaut euch doch mal um… nächstes Jahr gibt es den Amsterdam Excel Summit 2020, den Global Excel Summit 2020 in London https://globalexcelsummit.com , natürlich wieder die Bulgaria Excel Days und die Excel Olympics Conference in Ljubljana.
P.P.S.: Wer es lieber “deutsch” mag, dem empfehle ich mit gutem Gewissen die Excel-Kompetenztage in Fulda und die bestimmt auch wieder stattfindenden Munich Excel Days.
P.P.S.: Hey! Und natürlich bin ich auch noch für euch da! Denkt an die Excel-Stammtische!
Spreadsheet Day 2019 & Excel-Aufgabe
Hey!
Nächste Woche ist der Spreadsheet-Day am 17. Oktober 2019! Und dann geht es auch schon mit den Excel-Tagen 2019 in München weiter https://www.munich-office-group.de/
Doch bis dahin gibt es noch eine kleine Aufgabe von mir für meine angehenden at Excel Experts. Heute auf dem Excel-Stammtisch in Essen können wir die Aufgabe auch gerne praktisch durchgehen… https://www.meetup.com/de-DE/Excel-Stammtisch-NRW/
Hier das Szenario:
Für ein Projekt wurden drei Firmen beauftragt.
Die drei Firmen werden für 200-220 Tage beauftragt, haben klare moderate Tagessätze und sind für bestimmte Teilprojekte vorgesehen. Das erste Unternehmen hier z.B. für die Teilprojfekte 1 bis 5.
Die Projektvorgangscodes aus der mittleren Spalte werden dann auch bei den Buchungen der einzelnen Mitarbeiter der Firmen eingetragen.
In der letzten Spalte ist noch zu sehen, wie viele Personentage für den Projektvorgang in Summe vorgesehen sind.
Die Projektplanung und die Buchung der Firmen umfasst somit komplett jeweils 640 Personentage.
Die folgenden 15 Mitarbeiter aus den drei Unternehmen werden dann auch tatsächlich für drei Monate eingesetzt. Jede Firma stellt 5 Mitarbeiter.
Nun kommen die tatsächlichen Buchungen für die drei Monate. Jeder Monat wird auf einem Tabellenblatt ausgewiesen.
Die Daten stehen platt im Datenblatt – keine Tabelle. Erst nur das Kürzel, dann das Datum pro Person und entsprechend ein Vorgang. Auf halbe Tage habe ich hier mal verzichtet.
Egal wie, sollen die folgenden Fragen bzw. Aufgaben geklärt werden:
- Passen die Buchungen der Mitarbeiter aus den drei Monaten zu den Beauftragungen. Wurden die Buchungen für die Firmen gut geplant?
- Passen die Plan-Zahlen der Projektvorgänge zu den Buchungen? Wurden die Projektvorgänge richtig eingeschätzt?
- Wie hoch fallen die monatlichen Rechnungen der drei Unternehmen aus?
- Stellen Sie tabellarisch in einer Kreuztabelle pro Unternehmen dar, wie viele Tage die einzelnen Mitarbeiter pro Monat gebucht haben.
- Gab es Fehlbuchungen auf Projekte, die einem Unternehmen nicht zugewiesen waren?
- Liegen vereinzelt Buchungen auf Freitagen? Dies war nicht gestattet!
- … fallen Dir noch Sachen auf?
Die XLSX-Dateien mit den Daten der Buchungen: E1026 Buchungsdaten
Und die ersten drei Tabellen: E1026 Buchungsliste Aufgabe Excel Experts
Ich bin auf euer Feedback gespannt.
Viel Spaß beim Basteln und 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.
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.
- Lernt nicht allein, sucht euch in der Firma einen Sparringspartner
- Tauscht euch mit anderen aus, egal ob in Foren oder live
- Schaut in nützliche Blogs und YouTube-Kanäle
- Experimentiert mit Power Query
- Arbeitet das Buch von Ken durch
- Besucht Seminare zu diesem Thema… seht die Teilnahmegebühr als Investition, nicht als Kosten.
Träumt nicht von Excel!
Euer Andreas
Du muss angemeldet sein, um einen Kommentar zu veröffentlichen.