kurze Einführung in Excel Power Query


Youtube Channel Excel Power BI







Excel Power Query (ab Excel 2016 Get & Transform) ist ein Datenanalyse feature, mit welchem man Daten aus heterogenen Datenquellen ermitteln, bereinigen, kombinieren und über Filter einschränken kann.

Ein typisches Praxisbeispiel können Sie hier sehen.

Vereinfacht gesagt ersetzt Power Query das in die Jahre gekommene Microsoft Query.
Power Query erweitert Excel um Möglichkeiten der Datenanbindung und Transformation, eine
sinnvolle Vorbereitung für das Excel Data Modell (Power Pivot).

Darüber hinaus bietet es auch die Möglichkeit, nach öffentlichen Datenquellen wie zb Wikipedia zu suchen.

Power Query Formel Sprache (Language M) Referenz

Formelliste (Language M) und Beschreibung mit neuer Abfrage erstellen

Microsoft Einführung in Excel Power Query

Der Abfrage Editor ist Bestandteil von Excel Power Query.




Daten können transformiert

- Einträge bereinigen (TRIM, UPPERCASE usw.)
- Ersetzen (zb leere Zellen durch null)
- benutzerdefinierte Spalten (Berechnungen)
- Spalten splitten
- Unpivot (zb aus einem Datensatz mit 12 Monaten werden 12 Datensätze)
- Duplikate entfernen

Datenquellen können zusammenführt (kombiniert)

- merge := Tabellen werden über Relationen / keys miteinander verknüpft (join)
- append := gleichartige Tabellen aus verschiedenen Datenquellen werden addiert (union)

und so angepasst (data shaping) werden, dass sie Ihren Anforderungen für die (weitere) Datenanalyse entsprechen. Des Weiteren können die Daten für eine weitere Analyse und Modellierung (semantisches Modell) mithilfe von Tools wie Power Pivot und Power View vorbereitet werden.

Beispiel eines semantisches Modell (Arbeitsmappenabfragen Abhängigkeiten)



optional Übergabe an Power Pivot Modell möglich (Zweck: Kennzahlenbildung / measures)




siehe Blog Beitrag Einführung in Excel Power Pivot

Argumente für den Einsatz von Power Query







typische Beispiele aus der Praxis (ETL = Einlesen, Transformieren, Laden):


Einlesen

mehrere Tabellen in einer Excel Datei integrieren

strukturell gleiche Excel Dateien zusammenführen

Sharepoint, Bibliothek, Excel Datei anbinden

Webinhalte anbinden


Fehlerbehandlung

try ... otherwise


Transformieren

Anteil an Gesamtsumme berechnen

Arbeitstage anhand Start- und Enddatum berechnen

Arbeitstage in Kalendertage umrechnen (Modulo Operator)

Daten entpivotieren

Daten pivotieren mehrere Wertspalten

Datumstabelle mit Power Query erstellen

Datumstabelle (Wochentage) mit Power Query erstellen

Geschäftsjahr aus Datumswert ableiten

IF Funktion

 laufende Summe, ABC-Analyse

Leere Zellen in einer Spalte mit Vorgänger, Nachfolger Werten füllen

Mehrfache Fallunterscheidung

Synonyme erkennen und vereinheitlichen

Text aufteilen

Text Funktionen

Umgang mit wechselnden Spaltenbeschriftungen mit Power Query

Wasserfall Chart


Laden

Datenverbindung mit Power Query erstellen








Kommentare

Kommentar veröffentlichen

Beliebte Posts aus diesem Blog

Dropdown Liste mit Mehrfachauswahl

Bild
Standardmäßig kann in einem Dropdownfeld nur ein Eintrag ausgewählt werden (Daten -> Datentools -> Datenüberprüfung). Ein dynamisches Dropdownfeld kann folgendermaßen realisiert werden abhängige, dynamische Dropdownfelder können folgendermaßen in Excel  realisiert werden weiterführende Informationen siehe hier Um eine Mehrfachauswahl zu realisieren, kann der folgende VBA-Code im betreffenden Arbeitsblatt verwendet werden (rechte Maustaste auf Arbeitsblatt -> Code einfügen). --- SCHNIPP -- Private Sub Worksheet_Change(ByVal Target As Range) '** Mehrfachauswahl über DropDown-Liste (Gültigkeitsprüfung) '** Einfügen im Code-Container des betreffenden Arbeitsblattes '** Dimensionierung der Variablen Dim rngDV As Range Dim wert_old As String Dim wertnew As String '** Errorhandling On Error GoTo Errorhandling '** Mehrfachauswahl im definierten Bereich (Bsp. B4:B14) durchführen If Not Application.Intersect(Target, Range("B4:B14&
Mehr anzeigen

Vergleich mit 2 Bedingungen, INDEX(), VERGLEICH()

Wenn man 2 Tabellen mit jeweils 2 gleichen Spalten hat (zB Nachnahme, Vorname) und eine 3te Spalte (zB Telefon) von einer Tabelle in eine andere übernehmen will, stellt die Funktion SVERWEIS() keinen adäquaten Lösungsansatz mehr dar. Mit einer Kombination der Funktionen INDEX() und VERGLEICH() ist es dennoch möglich, diese Aufgabe mit Excel Formeln (Matrixformel) zu lösen. =INDEX(Tabelle1!$A$2:$C$5;VERGLEICH(A2&B2;Tabelle1!$A$2:$A$5&Tabelle1!$B$2:$B$5;0);3) wobei gilt: Tabelle 1 Spalte A zB Nachname Tabelle 1 Spalte B zB Vorname Tabelle 1 Spalte C zB Telefon (Wert welcher in Tabelle 2 bei inhaltlicher Gleichheit Spalten A, B übernommen werden soll) Formel in Tabelle 2 Spalte C aufbauen und mit STRG-SHIFT-RETURN (Matrixformel) abschließen und runterkopieren. Ein weiteres Beispiel zu Matrixformeln siehe folgenden Blog Beitrag seit Office 365 -> Microsoft 365 gibt es eine weitere, neue Alternative, Funktion XVERWEIS()
Mehr anzeigen

nützliche Text Funktionen in Power Query

Bild
Im Folgenden werden ein paar in der Praxis nützliche Text Funktionen in Power Query aufgelistet. Im Voraus soll darauf hingewiesen werden, dass es 2 wesentliche Unterschiede zwischen Excel und Power Query Formeln / Funktionen gibt: case sensitivity Excel Formel unterscheiden nicht zwischen Groß- und Kleinschreibung, Power Query Formeln indes schon. Wenn eine Power Query Signatur Text.Range vorgibt, dann wird TEXT.RANGE oder text.range nicht funktionieren (case sensitive). Basis 1 versus Basis 0 Excel Formeln / Funktionen beziehen sich immer auf die Basis 1, d.h. man fängt mit 1 an zu zählen. Auf der anderen Seite startet das Zählen in einer Power Query Funktion immer mit 0, nicht 1. Vergleich Excel Text mit Power Query Funktionen Text.Contains(Text,Suchstring) gibt TRUE zurück, wenn <Suchstring> in <Text> beinhaltet ist, andernfalls FALSE z.B. Text.Contains("Power Query","Query") Rückgabewert = TRUE Text.Remove([Column],{"
Mehr anzeigen