Power Query M Crashkurs für Entwickler: Web-APIs, Konnektoren

Power Query M aus Entwicklersicht

• M als funktionale Ausdruckssprache: Werte statt Anweisungen, keine Zuweisungen
• let … in als ein einziger Ausdruck: Schritte als Bezeichner, Abhängigkeiten statt Reihenfolge
• Verzögerte Auswertung: nur benötigte Schritte, Felder und Zeilen werden berechnet
• Streaming von Tabellen und Listen und mögliche Mehrfachauswertung einer Quelle
• Unveränderliche Werte und Pipelines: Parallelen zu LINQ, F# und SQL
• Einfache Beispiele: Ausdrücke in einer leeren Abfrage auswerten, ganz ohne Datenquelle

Typsystem, Operatoren und eigene Typen

• Das Typsystem: primitive und strukturierte Typen, any, null und nullable Typen
• Konstruktoren und Literale: #date, #datetime, #duration, #table, {…}, […]
• Operatoren und Type-Casting: as, is, Number.From, Text.From, Date.From
• Typfacetten wie Int64.Type und Currency.Type und ihr Einfluss auf das Datenmodell
• Custom Types: Tabellentypen type table [Id = Int64.Type, …] und Funktionstypen
• Value.ReplaceType für Spaltentypen und Funktionssignaturen

Grundfunktionen für Listen, Records und Tabellen

• Listenfunktionen: List.Select, List.Transform, List.Distinct, List.Zip
• Record-Funktionen: Record.Field, Record.AddField, Record.FieldNames, Record.Combine
• Table-Funktionen als Pipeline: Table.SelectRows, Table.AddColumn, Table.Group
• Helper-Funktionen: Value.Is, Value.Equals, Comparer, Splitter, Combiner
• Übung: dieselbe Aufgabe einmal mit Listen- und einmal mit Table-Funktionen lösen

Typische Anwendungsfälle in Code

• Zeilen einfügen, ändern, löschen: Table.InsertRows, Table.ReplaceRows, Table.RemoveMatchingRows
• Tabellen-Schema umbauen: Table.RenameColumns, Table.ReorderColumns, Table.TransformColumnTypes
• Schema dynamisch aus Table.Schema und Table.ColumnNames ableiten
• Set-Operationen: Table.Join, Table.NestedJoin mit JoinKind, Kreuzprodukt über Listen
• Lookups ohne Join: Zeilenzugriff per Schlüssel Tabelle{[Id = 4]} und Table.PositionOf
• Zeit: DateTime.LocalNow gegenüber DateTimeZone.UtcNow bei Aktualisierung im Dienst
• Text: Text.Split, Text.Combine, Text.PadStart, Text.Contains mit Comparer

Funktionale Techniken und Fehlerbehandlung

• Lambda-Funktionen, each mit _ und Funktionen als Argumente höherer Ordnung
• Schleifen ersetzen: List.Generate für Zustände, List.Accumulate als Fold
• Transformationsfunktionen: Table.TransformRows, Table.TransformColumns, Table.Transpose
• Exception Handling: try … catch, error mit eigenem Fehler-Record, Fehler weiterreichen
• Benutzerdefinierte Funktionen mit typisierten und optionalen Parametern
• Funktionen dokumentieren: Metadaten Documentation.Name und Documentation.Examples
• Environment-Konzept: Gültigkeitsbereiche, #shared und Expression.Evaluate mit eigener Umgebung
• Reflection: Value.Type, Type.TableSchema, Type.FunctionParameters, Value.Metadata

Parameter, Web-APIs und Paging

• Parameterabfragen in Excel und Power BI Desktop, Parameter als Funktionsargumente
• Web.Contents mit RelativePath, Query und Headers, damit der Dienst aktualisieren kann
• JSON-Antworten mit Json.Document lesen und verschachtelte Records erweitern
• Paging über Seitenzähler, Offset oder Next-Link mit List.Generate
• Ratenbegrenzung: Statuscodes mit ManualStatusHandling auswerten, Wartezeit mit Function.InvokeAfter
• API-Schlüssel und Token über Anmeldeinformationen statt im Code hinterlegen

Query Folding und Leistung

• Query Folding: welche Schritte als SQL an die Quelle gehen und welche lokal laufen
• Folding prüfen mit „Native Abfrage anzeigen“ und der Abfragediagnose in Power BI Desktop
• Table.Buffer und List.Buffer gegen Mehrfachauswertung – und wann sie Folding verhindern
• Value.NativeQuery mit Parametern und EnableFolding statt zusammengesetzter SQL-Zeichenketten

Custom Connectors mit dem Power Query SDK

• Power Query SDK in Visual Studio Code: Projekt anlegen, M-Code ausführen und testen
• Aufbau eines Konnektors: section, [DataSource.Kind], Publish-Record und Datenquellenart
• Authentifizierungsarten definieren: Key, UsernamePassword, OAuth
• Navigationstabelle für mehrere Endpunkte nach dem Muster der SDK-Beispiele
• .mez-Datei bauen, in Power BI Desktop laden und mit TestConnection über das lokale Datengateway im Dienst nutzen
• M-Code versionieren: Power BI-Projekte (.pbip) und Konnektorquellen in Git

Praxisprojekt: Pegelstände aus der offenen Web-API eines Hochwassermeldedienstes

• Messstellen und Zeitreihen per Web.Contents mit RelativePath und Abfrageparametern abrufen
• Seitenweise Antworten mit List.Generate vollständig laden, bei HTTP 429 verzögert wiederholen
• Messwerte typisieren, Lücken als Fehler-Records markieren, Zeitstempel in UTC vereinheitlichen
• Abruf als dokumentierte Funktion kapseln und zu einem kleinen Konnektor mit Navigationstabelle ausbauen