Power Query M - Komplettkurs für Anwender - Beratung, Coaching und Schulung

Einführung: wo M läuft und wie Code entsteht

• M in Power Query für Excel, Power BI Desktop, Dataflows, Analysis Services und Dataverse
• Self-Service BI und Managed BI: Excel-Abfragen, Power BI-Modelle, zentrale Dataflows
• Vom Klick zum Code: wie jeder angewendete Schritt als M-Ausdruck entsteht
• Werkzeuge: erweiterter Editor, Bearbeitungsleiste, IntelliSense und Kommentare
• Code lesbar halten: sprechende Schrittnamen, Einrückung, Formatierer für M
• Power Query SDK in Visual Studio Code für größere M-Projekte (Überblick)

Das Typsystem von Power Query M

• Primitive Typen: number, text, logical, date, datetime, datetimezone, duration, null
• Strukturierte Werte: Listen {…}, Records […] und Tabellen mit #table
• Typwerte und Funktionswerte: type table [...], type function und Value.Type
• Nullable-Typen, any und der Unterschied zwischen zugewiesenem und geprüftem Typ
• Operatoren: Arithmetik, Vergleiche, & zum Verketten und Zusammenführen, ?? für Ersatzwerte
• Reflection: Typen zur Laufzeit lesen mit Type.TableSchema und Type.FunctionParameters
• Metadaten mit meta anhängen und mit Value.Metadata auslesen
• Syntax: let … in, Bezeichner mit #"…", Groß-/Kleinschreibung, Kommentare

Abfragen in M aufbauen

• Datenquellen per Code: Csv.Document, Excel.Workbook, Sql.Database
• Ordnerimport mit Folder.Files und Dateiinhalten aus der Spalte Content
• Überschriften und Spaltentypen: Table.PromoteHeaders, Table.TransformColumnTypes mit Kultur
• Index- und Schlüsselspalten: Table.AddIndexColumn, Table.AddKey, Table.Distinct
• Spalten teilen und verbinden: Table.SplitColumn, Table.CombineColumns
• Übung: generierten Code einer Oberflächenabfrage lesen und von Hand vereinfachen

Grundlagen: Listen, Records und Tabellen

• Werte erzeugen: Literale, List.Numbers, List.Dates, Record.FromList
• Tabellen aus Listen und Records: Table.FromRows, Table.FromRecords, Table.FromColumns
• Zugriff: Position {0}, Feld [Spalte], optionaler Zugriff mit ?
• Operationen: List.Transform, Record.TransformFields, Table.TransformColumns
• Metadaten-Record praktisch: Beschreibungen an Abfragen und Funktionen hängen
• Konvertierungen: Table.ToRecords, Table.Column, Record.ToTable, Table.FromList

Funktionen für Datum, Text und Zahlen

• Datum, Zeit und Dauer: Date.AddMonths, #duration, Duration.TotalHours
• Zeitzonen: DateTimeZone.SwitchZone, DateTimeZone.ToLocal und UTC im Power BI-Dienst
• Datumstabelle in M erzeugen: List.Dates plus Spalten für Jahr, Quartal, Kalenderwoche
• Text: Text.Split, Text.BetweenDelimiters, Text.Select, Text.Proper
• Zahlen: Number.Round mit RoundingMode, Number.IntegerDivide, Number.Mod
• Konvertieren und formatieren: Number.ToText, Date.ToText mit Formatzeichenfolge und Kultur
• Comparer: Comparer.OrdinalIgnoreCase für Vergleiche ohne Groß-/Kleinschreibung
• Splitter und Combiner: Splitter.SplitTextByDelimiter, Combiner.CombineTextByDelimiter

Tabellen umformen mit Record- und Table-Funktionen

• Spalten hinzufügen, kopieren, umbenennen: Table.AddColumn, Table.DuplicateColumn, Table.RenameColumns
• Spalten robust auswählen: Table.SelectColumns mit MissingField.Ignore
• Joins: Table.NestedJoin mit JoinKind, Self-Join und Anti-Join für Abgleiche
• Anfügen mit Table.Combine und der Unterschied zu Table.Join
• Unscharfer Abgleich von Schreibvarianten mit Table.FuzzyNestedJoin
• Gruppieren: Table.Group mit mehreren Aggregationen und GroupKind.Local
• Pivotieren und entpivotieren: Table.Pivot, Table.UnpivotOtherColumns, Table.Transpose
• Sortieren mit Table.Sort, mehreren Schlüsseln und eigener Sortierreihenfolge

Eigene Funktionen in Power Query M

• Syntax: (x as number, optional y as text) as number => …
• Aufrufmuster: Funktion als eigene Abfrage, Aufruf je Zeile über eine benutzerdefinierte Spalte
• Lambda-Ausdrücke und die Kurzform each mit _ als Parameter
• Best Practices: Parameter typisieren, Funktionen mit Documentation.*-Metadaten beschreiben
• Schleifen ohne Schleife: List.Generate für Zustände, List.Accumulate zum Aufsummieren
• Verschachtelte Funktionen und Rekursion mit dem Bereichsoperator @
• Umgebung und Gültigkeitsbereich: let-Bereiche, #shared, Abfragen als Bezeichner

Fehlerbehandlung

• Fehler auf Schrittebene und Fehler in einzelnen Zellen unterscheiden
• try … otherwise und try … catch mit Zugriff auf den Fehler-Record
• Eigene Fehler auslösen mit error und Error.Record
• Fehlerzeilen finden und ersetzen: Table.SelectRowsWithErrors, Table.ReplaceErrorValues
• Datenschutzebenen und Fehler der Formel-Firewall (Formula.Firewall) verstehen und auflösen

Query Folding und Leistung

• Query Folding: welche Schritte an die Quelle zurückgegeben werden und welche Quellen es können
• Folding prüfen: „Native Abfrage anzeigen“ und Folding-Indikatoren in Power Query Online
• Schritte, die Folding abbrechen, und eine Schrittreihenfolge, die es erhält
• Value.NativeQuery mit EnableFolding = true für eigene SQL-Abfragen
• Table.Buffer und List.Buffer: wann Puffern hilft und wann es bremst
• Abfragediagnose in Power BI Desktop zum Messen von Ladezeiten

Muster aus der Praxis

• Konvertierungsmuster: Liste zu Record, Tabelle zu Liste mit Table.ToList und Table.ToRows
• Verschachtelte Daten erweitern: Table.ExpandTableColumn, Table.ExpandRecordColumn
• JSON-Antworten mit Json.Document in flache Tabellen überführen
• Parent-Child-Hierarchien rekursiv durchlaufen und Pfade wie „Werk/Halle/Linie“ bilden
• Übung: dynamische Spaltenlisten mit Table.ColumnNames statt fest codierter Namen
• Übung: mehrere ähnliche Abfragen zu einer parametrisierten Funktion zusammenführen

M in Excel, Power BI und Dataflows

• Eigene Funktionsbibliotheken: Funktionen als Abfragen, Code aus Textdateien mit Expression.Evaluate
• Vorlagen: Excel-Arbeitsmappenvorlagen, Power BI-Vorlagen (.pbit), Power Query-Vorlagen (.pqt)
• M-Code versionieren: Power BI-Projekte (.pbip) mit TMDL-Dateien in Git
• Verbindungen, Anmeldedaten und Gateways im Power BI-Dienst gemeinsam nutzen
• Parameter in Excel und Power BI, inkrementelle Aktualisierung mit RangeStart und RangeEnd
• M im semantischen Modell: Laden deaktivieren, Abfragegruppen, Hilfsabfragen
• Dataflows Gen2 in Microsoft Fabric: Abfragen zentral pflegen und Datenziele festlegen
• Eigene Konnektoren (Custom Connectors) im Überblick – Vertiefung im Crashkurs für Entwickler

Copilot und Power Query M in Dataflow Gen2

• Transformationsschritte aus einer Beschreibung in natürlicher Sprache erzeugen lassen
• Bestehenden Power Query M-Code von Copilot erklären lassen – nützlich bei geerbten Abfragen
• Erzeugte Schritte im erweiterten Editor gegenlesen: Typen, Spaltennamen, Folding
• Voraussetzungen: Fabric-Kapazität ab F2 bzw. P1, Mandanteneinstellung; außerhalb von EU/USA zusätzlich regionsübergreifende Verarbeitung

Praxisprojekt: Stücklisten eines Maschinenbauers auflösen

• Stücklisten-Exporte aus dem ERP als CSV und Excel per Ordnerimport einlesen
• Mehrstufige Baugruppen rekursiv auflösen und Mengen je Ebene multiplizieren
• Fehlerhafte Zeilen protokollieren statt verwerfen, Prüfabfrage für unbekannte Teilenummern
• Auflösung als dokumentierte Funktion bereitstellen und in Excel und Power BI wiederverwenden

Lernziele

• Sie lesen generierten M-Code Schritt für Schritt und vereinfachen ihn im erweiterten Editor
• Sie erzeugen und konvertieren Listen, Records und Tabellen und setzen Typen und Metadaten bewusst ein
• Sie schreiben eigene, typisierte und dokumentierte Funktionen, auch rekursiv und mit List.Generate
• Sie fangen Fehler mit try … catch ab und machen Fehlerzeilen sichtbar
• Sie prüfen Query Folding und ordnen Schritte so an, dass Abfragen an der Quelle ausgeführt werden
• Sie stellen M-Funktionen und Vorlagen in Excel, Power BI und Dataflows zur Wiederverwendung bereit

Zielgruppe

• Fortgeschrittene Anwender:innen von Excel und Power BI, die Power Query bereits über die Oberfläche nutzen
• Controller:innen, Analyst:innen und Berichtsersteller:innen mit wiederkehrenden Datenaufbereitungen
• Key User und BI-Verantwortliche, die Abfragen anderer warten und vereinheitlichen