Nelles Power BI mit Excel

Das umfassende Handbuch
4. Auflage 2024
ISBN: 978-3-367-10195-5
Verlag: Rheinwerk
Format: EPUB
Kopierschutz: 0 - No protection

E-Book, Deutsch, 888 Seiten

Reihe: Rheinwerk Computing

Dank Power BI lassen sich mit Excel auch große Datenmengen analysieren und aufbereiten. Mit diesem Buch erhalten Sie alles, was Sie für den Arbeitsalltag benötigen: umfassende und verständliche Erklärungen anhand anschaulicher Praxisszenarien. Stephan Nelles zeigt Ihnen den Umgang mit Rohdaten aus unterschiedlichen Datenquellen und deren Bereinigung, Verknüpfung, Analyse und Visualisierung. Sie erfahren, wie Ihnen das Zusammenspiel von Excel mit Power Query, Power Pivot und Power BI Desktop effizient gelingt.

Aus dem Inhalt:

• Was ist Self-BI? Welche Tools leisten was?
• Power Query - Grundlagen der Bedienung
• Power Query statt Textfunktionen
• Abfragen zusammenführen/anhängen
• Power Query Formula Language: M für Fortgeschrittene
• Power Pivot - Grundlagen der Bedienung
• Bedingte Kalkulationen: CALCULATE()
• Berechnungen über mehrere Tabellen erstellen
• Kalendertabelle einbinden
• Sparklines und bedingte Formate benutzen
• Power BI Desktop - Grundlagen der Bedienung
• Grundlagen der Datenvisualisierung
• Standarddiagramme
• Interaktionen & Slicer
• Dashboards veröffentlichen
• Power BI Service - effizienteres Teamwork

Autoren/Hrsg.

Nelles, Stephan

Inhaltsverzeichnis

Vorwort ... 19  1.  Self-Service Business Intelligence -- die Tools und ihre Versionen ... 30  1.1 ... Drei Tools zur Optimierung des Reportings ... 31  1.2 ... Vom Add-in zur Menüintegration -- welche Excel-Version enthält was? ... 39  1.3 ... Power BI und der Power BI Service ... 42  2.  Power Query -- Daten aus unterschiedlichen Quellen importieren ... 46  2.1 ... Daten abrufen und transformieren -- Excel- und Power-BI-Desktop-Versionen ... 47  2.2 ... Mit Datenbanken verbinden ... 48  2.3 ... Zugriff mit Power BI Desktop auf Datenbankdateien ... 64  2.4 ... Import von CSV- und TXT-Dateien ... 66  2.5 ... Excel als Datenquelle für einen Power-Query-Import nutzen ... 72  2.6 ... Programmeinstellungen von Power Query anpassen ... 95  2.7 ... Mit Power Query erstellte Abfragen im Team nutzen ... 96  2.8 ... Mehrere Excel- oder CSV-Dateien aus einem Ordner konsolidieren ... 101  2.9 ... SharePoint-Listen mit Power Query abfragen und bereinigen ... 107  2.10 ... Zugriff mit Power Query auf Internetseiten ... 110  3.  Daten mit Power Query transformieren und kombinieren ... 115  3.1 ... Zugriff auf Textdateien ... 116  3.2 ... Spalten und Zeilen entfernen ... 117  3.3 ... Spalten teilen und neue Spalten erzeugen ... 128  3.4 ... Basisbereinigung und Zellbereiche füllen ... 136  3.5 ... Datumsformate anpassen und zusätzliche Datumsspalten erzeugen ... 140  3.6 ... Berechnete Spalten erstellen ... 155  4.  Tabellenstrukturen mit Power Query anpassen ... 176  4.1 ... Daten gruppieren ... 177  4.2 ... Mit strukturierten Spalten individuelle Datentypen erstellen ... 184  4.3 ... Transformation von gestapelten, kategorisierten und pivotierten Daten ... 190  5.  Erweiterte Funktionen mit Power Query ... 216  5.1 ... Eine kleine Reise durch M ... 217  6.  Power Pivot -- Grundlagen der Datenmodellierung ... 266  6.1 ... Tabellentypen eines Datenmodells ... 267  6.2 ... Transaktionstabellen ... 267  6.3 ... Dimensions- oder Suchtabellen ... 269  6.4 ... Typen von Beziehungen zwischen Tabellen ... 271  6.5 ... Excel-Jargon vs. Datenbank-Termini ... 273  6.6 ... Unterschiede zwischen logischen Beziehungen und Verweisfunktionen ... 279  6.7 ... Datenmodellierung in Excel oder in Power BI Desktop ... 279  6.8 ... Importieren weiterer Daten und logische Verknüpfung in Power Pivot ... 283  6.9 ... Überprüfung des Datenmodells mit einem impliziten Measure ... 289  6.10 ... Best Practice für die Berechnung von Kennzahlen -- das explizite Measure ... 293  6.11 ... Den Filterkontext einer Berechnung verstehen ... 300  6.12 ... Typische Fehler bei Berechnungen ... 306  6.13 ... Berechnete Spalten vs. Measures (berechnete Felder) ... 309  6.14 ... Fallbeispiele für die Verwendung von berechneten Spalten ... 327  6.15 ... Anpassungen an den Elementen eines Datenmodells vornehmen ... 339  7.  Absolute und prozentuale Abweichungen sowie Anteile mit DAX-Funktionen berechnen ... 353  7.1 ... Basisaggregierungen für eine Kundenanalyse erstellen ... 354  7.2 ... Die Datenqualität mithilfe von Basisaggregierungen überprüfen ... 358  7.3 ... Vergleich zweier Werte und Ratio-Berechnung ... 364  7.4 ... Bedingte Kalkulationen mit CALCULATE() erstellen ... 371  7.5 ... Anteile am Gesamtergebnis (Shares) mit ALL() berechnen ... 389  7.6 ... Die ALL-Varianten ALLEXCEPT() und ALLSELECTED() ... 394  8.  Variable Werte in DAX-Measures einbinden ... 401  8.1 ... Das Datenmodell und Basisaggregierungen der Budgetvarianten ... 402  8.2 ... Measures für die variable Auswahl des Budgets erstellen ... 404  8.3 ... Sortieren von Power-Pivot-Tabellen per Datenschnitt ... 413  9.  Zeitliche Analyse von Daten (Time Intelligence) ... 419  9.1 ... Anforderungen an eine Kalendertabelle ... 421  9.2 ... Year-over-Year- und Year-to-date-Berechnungen mit einem Standardkalender ... 431  9.3 ... Zeitliche Datenanalyse bei Verwendung von ISO-8601- und 445-Kalendern ... 440  9.4 ... Glättung von Zeitreihen auf Basis des gleitenden Mittelwertes ... 455  9.5 ... Manuell erfasste Schätzwerte in den Forecast einbinden ... 462  9.6 ... Eine saisonale Kurve in den Forecast einbinden ... 466

10.  Rangfolgen und Top-N-Darstellungen ... 478  10.1 ... Bedingte Kalkulation mit SUMX() ... 479  10.2 ... Iteratoren in virtuellen Tabellen ... 481  10.3 ... Produkte des aktuellen Produktkatalogs mit COUNTX() zählen ... 483  10.4 ... Die Gesamtmarge und den prozentualen Anteil der Produktionskosten ermitteln ... 484  10.5 ... Fehlerhafte Ergebnisanzeigen vermeiden mit SUMX() ... 486  10.6 ... Rangfolgen mithilfe von RANKX() berechnen ... 489  10.7 ... Top-10-Darstellung im Power-Pivot-Report ... 494  10.8 ... Berechnung der Top-3-Werte in einer Power-Pivot-Tabelle ... 500  10.9 ... Den Kunden- und Umsatzanteil der letzten Bestellungen ermitteln ... 503

11.  Klassifizierungen und ABC-Analyse ... 507  11.1 ... Preisklassen mit einer berechneten Spalte bilden ... 509  11.2 ... Berechnung klassifizierter Produkte mit einem Measure ... 512  11.3 ... Kundenklassifizierung al italiano ... 513  11.4 ... ABC-Analyse auf Basis berechneter Spalten ... 521  11.5 ... ABC-Analyse mit berechnetem Feld ... 526

12.  Patterns für Korrelationen, rollierende Auswertungen, asynchrones Reporting und Allokation von Werten ... 529  12.1 ... Korrelationskoeffizienten mit DAX-Funktionen berechnen ... 529  12.2 ... Rollierende Summen und Mittelwerte berechnen ... 544  12.3 ... Die tabellarische Reportstruktur mithilfe von Cube-Funktionen aufbrechen ... 548  12.4 ... Granularität und asynchrone Reportingstruktur ... 554  12.5 ... Mit zwei Datumswerten in einer Tabelle arbeiten ... 566  12.6 ... Einen Betrag auf einen Gesamtzeitraum verteilen ... 570  12.7 ... Virtuelle Tabellen und gewichteter Durchschnittspreis ... 575

13.  Power BI Desktop -- vom Datenmodell zum interaktiven Onlinereport ... 579  13.1 ... Die Benutzeroberfläche von Power BI Desktop ... 581  13.2 ... Measures -- auch in Power BI Desktop das Maß aller Dinge ... 588  13.3 ... Visualisierungen auf Basis eines Datenmodells erstellen ... 602  13.4 ... Das Prinzip der Interaktion ... 613  13.5 ... Das Prinzip der Hierarchien ... 618  13.6 ... Das Prinzip der künstlichen Intelligenz ... 621

14.  Gestaltungsregeln für Reports und Dashboards ... 626  14.1 ... Regeln der Wahrnehmung ... 626  14.2 ... Das Erscheinungsbild von Reports durch Vorlagen steuern ... 630  14.3 ... Aufbau von Reportseiten ... 633  14.4 ... Darstellung der sechs grundlegenden Datenrelationen ... 636

15.  Power-BI-Reports -- Die zeitliche Analyse von Daten ... 639  15.1 ... Zeitliche Analyse von Unternehmensdaten ... 640  15.2 ... Die jährliche Wachstumsrate im Bericht darstellen ... 677

16.  Dynamische Steuerung -- Parameterfelder, Datenschnitte, Kalkulationsgruppen und Eingabefelder ... 680  16.1 ... Variablen in Power-BI-Desktop-Berichten ... 680  16.2 ... Die Datenauswahl durch Parameterfelder flexibler gestalten ... 685  16.3 ... Eingabe von veränderlichen Werten über Parameter ... 691  16.4 ... Mit Kalkulationsgruppen die Anzahl der Measures reduzieren ... 694  16.5 ... Mehrere Spalten mit einem Datenschnitt filtern ... 699

17.  Optimierung von Datenmodellen und DAX-Berechnungen ... 705  17.1 ... Das Datenmodell in Power BI Desktop optimieren ... 705  17.2 ... DAX-Abfragen prüfen und optimieren ... 709  17.3 ... Die Performance von Visualisierungen mit DAX-Abfragen analysieren ... 718

18.  Power-BI-Reports -- die Analyse von Performance-Kennzahlen ... 721  18.1 ... Analyse der Performance ... 721

19.  Power-BI-Reports -- die geografische und statistische Analyse ... 763  19.1 ... Geografische Auswertungen in Power BI Desktop ... 763  19.2 ... Statistische Auswertungen in Power BI Desktop ... 776

20.  Erweiterte Datenanalyse und KI in Power BI ... 798  20.1 ... Was geht, KI? ... 798  20.2 ... Erweiterte Datenanalysefunktionen ... 804  20.3 ... Daten mit Q&A erkunden ... 821

21.  Zielgruppenorientiertes Reporting -- Quickinfos, Drillthroughs, Bookmarks und interne Seitennavigation ... 828  21.1 ... Zusätzliche Informationen über Quickinfos in eine Visualisierung einbinden ... 829  21.2 ... Mit Drillthrough von Übersichts- zu Detailseiten wechseln ... 830  21.3 ... Lesezeichen für eine Präsentation erstellen ... 834  21.4 ... In einem Bericht eine Navigation erstellen ... 840  21.5 ... Quickmeasures erstellen ... 848

22.  Reports teilen, aktualisieren und Zugriffsrechte auf Daten organisieren ... 852  22.1 ... Die Datenhierarchie auf Power BI Service ... 852  22.2 ... Die Lizenztypen von Power BI ... 853  22.3 ... Daten freigeben und Mitglieder zu Arbeitsbereichen hinzufügen ... 856  22.4 ... Die Sicherheitsarchitektur von Power BI ... 857  22.5 ... Datasets aktualisieren ... 863  22.6 ... Zusätzliche Funktionen der Onlinereports von Power BI ... 868  22.7 ... Endgeräte für die Nutzung von Berichten und Dashboards ... 872  Über den Autor ... 875  Index ... 877

Leseproben

Vorwort

Die Digitalisierung im Controlling ist in vollem Gange. Niemand wird dem ernsthaft widersprechen. Und dass diese Transformation auch notwendig ist, ist ebenso wenig umstritten. Gil Press präsentierte 2016 im Forbes Magazine Zahlen, die zum Nachdenken anregen mussten. Etwa 80 % ihrer Arbeitszeit sind Data Scientists mit der manuellen Sammlung, Bereinigung und Organisation von Daten für die spätere Auswertung beschäftigt.[ 1 ] Zwar liegen keine präzisen Daten für die Verteilung von Tätigkeiten im Controlling vor, doch meine persönlichen Erfahrungen aus zahlreichen Projekten in Unternehmen erlauben die Schätzung, dass auch für dieses Tätigkeitsprofil die händische Aufbereitung von Datenmaterial einen exorbitant hohen Anteil an der Arbeitszeit einnimmt. Zeit, die in der Folge natürlich für die eigentlichen Kernaufgaben der Controllerinnen und Controller fehlen: Ergebnisse bewerten, das Management beraten.

Utz Schäffer und Jürgen Weber wiesen 2016 darauf hin, dass Aufgabenprofil, Toolbox und Mindset der Mitarbeitenden im Controlling aufgrund der digitalen Transformation im Zeitraffer einer Revision unterzogen würden.[ 2 ] Im Aufgabenprofil müssten sich zwangsläufig Skills zur Standardisierung und Automatisierung von Prozessen der Datenanalyse ausbreiten (Prozessoptimierung). Im Mindset müssten sich verstärkt Qualifikationen für die Funktion des Controllers und der Controllerin als Beratende des Managements weiterentwickeln. Und die Toolbox sollte nach Möglichkeit unterschiedliche Softwarewerkzeuge, aber auch Verfahren und Metriken für die Analyse von Big Data umfassen (Predictive Analytics, KI etc.).

Was bringt KI für das Reporting mit Power BI?

Bei all den Notwendigkeiten und Innovationen fragen sich im Jahr 2024 viele Mitarbeitende im Controlling, aber auch deren Manager und Managerinnen im Unternehmen, wie diese abstrakten Begriffe und Konzepte in der täglichen Praxis beispielsweise eines mittelständischen Unternehmens wohl Form annehmen werden. Und wie sich die Arbeit und die Arbeitsplätze verändern werden. Es ist selbstverständlich nicht so, dass die Entwicklung einer Software wie Power BI und die Betrachtung ihrer Änderungen über die letzten Jahre hinweg eine erschöpfende Antwort auf diese Fragen der Unternehmensentwicklung geben können. Doch im viel kleineren Rahmen, nämlich der Datenanalyse und dem Reporting, erscheint Power BI fast wie ein Brennglas für die Modifikation eines Unternehmensbereichs unter den Vorzeichen der schrittweisen Implementierung von KI. Wer durch dieses Brennglas schaut, sieht 2024 erstaunliche Dinge. Und es sind Dinge, liebe Leserin, lieber Leser, die Sie schon morgen selbst an Ihrem Arbeitsplatz umsetzen könnten.

So einfach geht das: Im Power BI Service öffnen Sie den OneLake-Datahub, wählen ein semantisches Modell (sprich ein Dataset) aus und klicken auf den Button Bericht automatisch erstellen. Nach insgesamt 4 Mausklicks und etwa 20 Sekunden sehen Sie eine fertige Berichtsseite (siehe Abbildung 1).

Abbildung 1     KPI-Karten, Balkendiagramme, Narrativ-Visual – Fabric stellt dies in Eigenregie für Sie zusammen.

Denn Fabric ist die Microsoft-Plattform für Echtzeitanalysen. Sie integriert neben dem zentralisierten Speicherort OneLake-Datahub auch Microsofts KI-Dienste für die Datenanalyse. Weil Speicherort, KI-Dienst und Reporting-Tool so nahtlos integriert sind, entsteht Ihr Geschäftsbericht als eine Art Schnappschuss in wenigen Sekunden. Sicherlich beleuchtet er nicht alle für Sie interessanten Aspekte des bestehenden Datenmaterials. Aber als Ausgangspunkt ist dieser Bericht schon sehr gut und eine enorme Hilfe (siehe Abschnitt 20.1.2).

Natürlich geht’s auch eine Nummer kleiner. So können Sie im Q&A-Trainingscenter einige Fragen zur Auswertung des vor Kurzem erstellten Power-BI-Salesreports vorformulieren. Das hilft Ihrem Team und auch der erweiterten Zielgruppe später, die Datenbasis intuitiv zu erkunden. Nutzende werden dadurch auch ermuntert, eigene Fragen an die Datenbasis zu richten und dadurch Analysen abzurufen, an die Sie als Autorin oder Autor des Reports vielleicht gar nicht gedacht haben. Oder für deren Einbindung Sie schlichtweg nicht mehr die notwendige Zeit gefunden haben (siehe Abschnitt 20.3).

Kann KI auch Ihren eigenen Umgang mit Berichten vereinfachen, die nicht Sie, sondern Mitarbeitende erstellt haben? Ja, das geht. Zum Beispiel dann, wenn Sie sich die Textzusammenfassung eines Reports geben lassen, den Ihre Kollegin letzte Woche fertiggestellt hat, kurz bevor sie in Urlaub ging. Leider hat es zeitlich nicht mehr geklappt, sich die wichtigsten Ergebnisse gemeinsam anzusehen und zu besprechen. Fragen Sie den Copilot im Power BI Service, und Sie erhalten die gewünschte textliche Zusammenfassung in übersichtlicher Form in wenigen Sekunden (siehe Abbildung 2). Auch ein komplexes DAX-Measure, das Sie nicht so recht verstehen, lassen Sie sich zukünftig auf diesem Wege erklären.

Abbildung 2     Der Copilot liefert Ihnen eine Textzusammenfassung einer Berichtsseite, die Ihre Kollegin letzten Monat erstellt hat.

Für viele KI-Funktionen werden Sie das richtige Power-BI-Konto benötigen. Denn je avancierter die KI-Tools sind, desto höher und undurchlässiger ist die Bezahlschranke. Doch auf der anderen Seite unterstützt KI Sie selbst lokal, also in Power BI Desktop, mit nützlichen Assistenzsystemen. Zum Beispiel dann, wenn Sie im Kontextmenü eines Visuals die Option Analysieren wählen und gleich eine ganze Reihe von Erkenntnissen als Vorschlag erhalten (siehe Abbildung 3). Stoßen Sie auf eine Erkenntnis, die Ihnen besonders nützlich erscheint, so übernehmen Sie diese mit einem Mausklick in Ihren Bericht (siehe Abschnitt 20.2.3).

Abbildung 3     Analysevorschlag zu einem Bericht in Power BI Desktop

Also kurz gesagt: Niemand kann verlässliche Aussagen darüber treffen, welchen umfassenden Einfluss KI auf unser berufliches oder gar auf unser gesellschaftliches Leben haben wird. Werden wir vier Tage in der Woche nicht arbeiten müssen, weil der Copilot das Steuer immer häufiger übernimmt? Oder sind wir am Arbeitsplatz sogar vollständig ersetzbar? Werden wir alle zu Beraterinnen bzw. Beratern mit höchster Fachkompetenz und auf ungeahnt hohem Empathie-Level? Werden wir total verblöden, weil wir nur wissen, auf welchen Knopf wir drücken müssen, ohne auch nur noch ansatzweise zu verstehen, was dann im hermetisch abgeriegelten und garantiert keimfreien Data Center passiert? Alle diese Szenarien wurden schon unzählige Male seit Beginn der Industrialisierung diskutiert oder beschworen. Dabei ist das Großartige an der Vorstellung von Zukunft meines Erachtens, dass sie einzig der Menschheit vorbehalten ist. Wir haben diese Gabe exklusiv und sie schenkt uns alle Möglichkeiten, das, was kommt, mitzugestalten.

Welchen Einfluss KI auf die Arbeit im Controlling haben wird, darauf gibt es hingegen viele (Mikro)-Antworten. Das vorliegende Buch soll einige davon in knappen Beispielen formulieren. Ich hoffe, dies ist mir gelungen.

Was ist neu in dieser Ausgabe?

Neben dem Thema KI in Kapitel 20 gibt es selbstverständlich weitere Neuerungen in Power BI, die in dieser aktuellen Auflage des Buches berücksichtigt wurden:

• Neue sinnvolle Assistenzfunktionen für die Strukturierung Ihres Datenmodells und die Optimierung der Performance von Visuals und Reports sind nun in Power BI verfügbar. Die neue DAX-Abfrageansicht hilft Ihnen dabei, Schritt für Schritt die Arbeitsweise von DAX-Measures zu verstehen und diese dann zu verbessern. Wie das genau geht, beschreibe ich in Kapitel 17 ausführlich.

• Für die Konfiguration von Visuals hat Microsoft eine völlig neue Schnittstelle entwickelt. Sie trägt die Bezeichnung Interaktionen auf dem Objekt (Vorschaufeatures). In Abschnitt 13.3.2 können Sie sich mit dieser Neuerung vertraut machen und entscheiden, ob Sie diese Funktion zukünftig der konventionellen Arbeitsweise vorziehen werden.

• Ebenfalls eine grundlegende Neuerung und ein Vorschaufeature sind die visuellen Berechnungen, die es zum ersten Mal erlauben, ein Measure zu schreiben, das ausschließlich im dafür gewählten Visual und nicht im gesamten Bericht gültig ist. In Abschnitt 13.2.3 erfahren Sie mehr darüber.

• Es gibt auch Themen und Funktionen, die in Power BI schon seit Längerem eine Rolle spielten bzw. verfügbar waren, die aber in dieser Auflage neu gebündelt werden mussten. Dazu gehört der Umgang mit Datumstabellen für die zeitliche Auswertung von semantischen Modellen (siehe...