Blog
Power Query Debrief: Erste Learnings aus Excel Esports Cases
Ich habe in letzter Zeit angefangen, einige Excel-Esports-Probleme nicht mit Formeln, sondern bewusst mit Power Query zu lösen. Power Query ist dabei selten die schnellste Waffe im Wettkampf, aber es ist für mich das Werkzeug, um Datenprobleme sauber zu zerlegen.
Meiner Meinung nach sind die Excel-Skills, die man für Excel Esports braucht, sehr viel wert. Es geht um Belastbarkeit unter Stress, Problemlösefähigkeiten und natürlich Excel. Manche Teilnehmer nutzen VBA, andere Formeln und wieder andere sogar Python. Auch Power Query bietet hier wertvolle Möglichkeiten.
Wenn ich aber an mein Arbeitsleben denke, sind viele Formellösungen nicht unbedingt direkt anwendbar. Selten muss ich eine Lösung per Formel möglichst schnell bauen. Meist geht es darum, Daten zu transformieren, um aus ihnen Informationen und dann Mehrwert zu generieren. Es geht nicht nur um Daten, sondern um Insights aus den Daten und die darauf basierenden Handlungsempfehlungen.
Damit man sich auf dieses Generieren von Mehrwert fokussieren kann, sollte die Aufbereitung der Daten einfach und nachhaltig gestaltet sein. Power Query ist genau dafür gemacht. Daher wollte ich meine Power-Query-Skills gezielt weiterentwickeln.
Ich arbeite schon lange mit Power Query, aber meistens benutze ich nur das Interface. Dahinter steckt jedoch die M-Sprache, mit der viel mehr geht als nur über die Benutzeroberfläche.
Ich habe bereits unterschiedliche Bücher gelesen, die über das User Interface hinausgehen, unter anderem The Definitive Guide to Power Query, Professional Power Query and M Language, Power Query: Beyond the User Interface, The Data Monkey Guide to the M Language und Master Your Data with Excel and Power BI. Lesen ist aber etwas anderes als machen.
Ich bin also theoretisch bereits mit der Sprache vertraut und kenne grundlegende Konzepte. Für mich war das wie ein Perspektivwechsel: das gleiche Problem in einer unbekannteren Welt, aber gleichzeitig eine Horizonterweiterung.
Wer mich kennt, weiß, dass ich am liebsten alles Single Cell löse. Das heißt: In Wettbewerben baue ich mir selten große Modelle auf, sondern packe die Logik in ein LET und kopiere die Lösung dann in jede Zeile. Der andere Ansatz wäre, ein Modell aufzubauen und per Datentabelle oder Makro den Input zu verändern, damit das Modell jeden Fall durchrechnet.
Das hier ist mein erster Debrief dazu. Kein vollständiger Guide, sondern eine Zwischenbilanz: Was hat mich überrascht, wo habe ich am Anfang falsch gedacht und welche Muster haben sich schon jetzt als besonders nützlich gezeigt?
Ich habe bisher noch nicht viele Fälle gemacht, teile meine Versuche aber auf YouTube. Dort habe ich mich bewusst für die englische Sprache entschieden, damit die Inhalte für mehr Leute zugänglich sind. Wenn Interesse an deutschen Videos besteht, meldet euch gern.
Warum ich Excel-Esports-Cases in Power Query löse
Excel Esports trainiert vor allem strukturiertes Denken unter Zeitdruck. Genau deshalb finde ich es spannend, dieselben Probleme mit verschiedenen Werkzeugen zu lösen. Mit Formeln trainiert man andere Muskeln als mit Power Query. Formeln zwingen einen oft in Richtung kompakter Logik pro Zelle oder Array. Power Query zwingt einen dagegen zu einem klaren Ablauf: Quelle verstehen, Daten bereinigen, umformen, anreichern, aggregieren und am Ende in ein brauchbares Ausgabeformat bringen.
Im normalen Arbeitsleben gibt es außerdem Transformationen, die über die GUI allein nicht sinnvoll oder gar nicht umsetzbar sind. Daher will ich mein Verständnis für M sowie den Umgang mit Tabellen, Records und Listen gezielt erweitern.
Learning 1: Scope verstehen
Power Query berechnet viele Dinge pro Zeile. Das ergibt Sinn: Wenn ich in einer Tabelle oder Liste eine neue Spalte hinzufüge, führe ich meistens eine Berechnung pro Zeile aus. Bei Excel-Esports-Problemen hat man oft viele unabhängige Inputs in einem Level. Für jeden Input muss also dieselbe Fragestellung gelöst werden.
Damit Power Query das sauber umsetzen kann, gibt es das each _-Konstrukt. Wenn ich einer Tabelle eine weitere Spalte hinzufüge und dann _ nutze, bekomme ich einen Record mit den Werten der gesamten Zeile. Im Endeffekt ist each nur die Kurzschreibweise für eine Funktion. Ich könnte also auch explizit mit (x) => ... arbeiten.
each bietet dabei noch weitere Möglichkeiten. Wenn ich nur auf eine Spalte zugreifen will, kann ich eckige Klammern nutzen, zum Beispiel [Column1]. Wenn ich stattdessen mit (x) => arbeite, muss ich das Objekt vor die Spaltenbezeichnung setzen, also x[Column1].
Spätestens bei geschachtelter Logik wird das wichtig. Mehrere each-Ausdrücke ineinander zu verschachteln funktioniert nicht beliebig, weil sonst nicht mehr klar ist, auf welchen Kontext sich ein Ausdruck bezieht. Deshalb ist Scope in Power Query ein zentrales Thema.
Codebeispiel: Level 6 von Mealprep 101 II
Hier ist der Code für meine Lösung zu Level 6. Man sieht darin gut, wie sich äußerer und innerer Kontext unterscheiden:
let
Quelle = Excel.CurrentWorkbook(){[Name="Level6"]}[Content],
#"Geänderter Typ" = Table.TransformColumnTypes(Quelle, {{"Column1", type text}, {"Column2", Int64.Type}, {"Column3", Int64.Type}, {"Column4", type text}}),
Benutzerdefiniert1 = Table.AddColumn(#"Geänderter Typ", "Solution", each
let
abs = [Column2] * Meals{[Meal = [Column1]]}[CaloriesAbsorbed],
act = Activity{[Activity = [Column4]]}[KalperMin],
fac = List.Last(Table.SelectRows(Timing, (x) => x[Waketime] <= [Column3])[Factor]),
calc = Number.RoundUp(abs / (act * fac))
in
calc,
Int64.Type),
#"Andere entfernte Spalten" = Table.SelectColumns(Benutzerdefiniert1, {"Solution"})
in
#"Andere entfernte Spalten"Man sieht hier zweimal let und in. Einmal für die gesamte Abfrage und einmal innerhalb von Table.AddColumn. Genau so breche ich komplexere Logik auf.
each sorgt hier dafür, dass die Berechnung für jede Zeile ausgeführt wird. Bei fac sieht man dann zusätzlich noch (x) => in Table.SelectRows. Dort steht vor Waketime explizit ein x, weil sich dieser Ausdruck auf die Tabelle Timing bezieht. [Column3] dagegen kommt aus dem äußeren each und bezieht sich auf die aktuelle Zeile der Eingabetabelle.
Dieses Konzept zu verstehen, öffnet bereits sehr viel. Es erlaubt auch komplexere Logik, weil ich innerhalb einer Berechnung nochmals pro Element oder pro Tabelle arbeiten kann, ohne den Überblick zu verlieren.
Learning 2: Datentypen sind keine Nebensache
Einer der größten Unterschiede zu klassischen Excel-Formeln: In Power Query merkt man sehr schnell, wenn Datentypen nicht sauber gesetzt sind. Ein Datum, das eigentlich Text ist. Eine Zahl mit Leerzeichen. Ein Wahr/Falsch-Wert, der als String importiert wurde. Solche Dinge werden nicht immer sofort sichtbar, machen die Logik aber unzuverlässig.
Auch komplexere Themen hängen daran. Man kann zum Beispiel nicht einfach eine Liste mit Zahlen mit einer einzelnen Zahl multiplizieren. In Excel kann ich =SEQUENZ(10)*5 schreiben. In Power Query geht das nicht direkt. Dafür muss man wieder mit each oder einer passenden Listenfunktion arbeiten.
Ähnlich ist es beim Zusammenbauen von Listen. Listen lassen sich mit dem &-Operator verketten. {1} & {2} wird zu {1, 2}. Aber wenn eines der Elemente keine Liste ist, funktioniert das nicht direkt. Wenn man darüber nachdenkt, ist das logisch, aber man muss sich dessen bewusst sein.
Für mich war das ein klares Learning: Datentypen sind kein Aufräumschritt am Ende.
Learning 3: Nicht unnötig kompliziert arbeiten
Meine Lösung für Level 7 von Mealprep 101 II war am Anfang deutlich komplexer als nötig:
let
Quelle = Excel.CurrentWorkbook(){[Name="Level7"]}[Content],
Benutzerdefiniert1 = Table.AddColumn(Quelle, "Solution", each
let
abs = [Column2] * Meals{[Meal = [Column1]]}[CaloriesAbsorbed],
act = Activity{[Activity = [Column4]]}[KalperMin],
minut = List.Generate(() => [Column3], (x) => x < [Column3] + 4000, (x) => x + 1),
fac = List.Transform(minut, (y) => act * List.Last(Table.SelectRows(Timing, (x) => x[Waketime] <= y)[Factor])),
calc = List.Accumulate(fac, [step = 0, cals = 0], (x, y) =>
if x is record then
let
start = [step = x[step] + 1, cals = x[cals] + y],
check = if start[cals] >= abs then start[step] else start
in
check
else
x
)
in
calc,
Int64.Type),
#"Andere entfernte Spalten" = Table.SelectColumns(Benutzerdefiniert1, {"Solution"})
in
#"Andere entfernte Spalten"Im Endeffekt berechne ich hier pro Zeile eine Liste mit allen Minuten, dann die Kalorienwerte und anschließend ein Running Total. Das Problem: Ich erzeuge immer 4000 Elemente, obwohl das in den meisten Fällen gar nicht gebraucht wird. Außerdem erstelle ich eine Liste, transformiere sie und laufe dann mit List.Accumulate noch einmal darüber. Der Code funktioniert, aber er ist langsam und unnötig kompliziert.
Spätere, deutlich bessere Lösung
Später bin ich auf diese Variante gekommen:
let
Quelle = Excel.CurrentWorkbook(){[Name="Level7"]}[Content],
Benutzerdefiniert1 = Table.AddColumn(Quelle, "Solution", each
let
abs = [Column2] * Meals{[Meal = [Column1]]}[CaloriesAbsorbed],
act = Activity{[Activity = [Column4]]}[KalperMin],
start = [Column3],
minut = List.Generate(
() => [c = 0, e = 0],
(x) => x[e] < abs,
(x) =>
[
c = x[c] + 1,
e = x[e] + act * List.Last(
Table.SelectRows(Timing, (y) => y[Waketime] <= x[c] + [Column3])[Factor]
)
]
),
out = List.Count(minut)
in
out,
Int64.Type),
#"Andere entfernte Spalten" = Table.SelectColumns(Benutzerdefiniert1, {"Solution"})
in
#"Andere entfernte Spalten"Anstatt mehrere Listen zu erzeugen, nutze ich hier List.Generate direkt für den eigentlichen Prozess. Das Abbruchkriterium ist simpel: Sobald die verbrauchten Kalorien größer oder gleich den absorbierten Kalorien sind, höre ich auf. Die Logik wird über einen Record aufgebaut: c ist mein Zähler, e meine akkumulierten Kalorien.
Für mich war das ein wichtiges Learning: Probleme nicht zu stark verkomplizieren und vor allem ein Gefühl dafür entwickeln, welche Funktionen für welche Art von Logik gut geeignet sind. In normalem Excel kenne ich viele Funktionen und habe meistens mehrere Ideen, wie ich ein Problem lösen kann. In Power Query möchte ich auch auf dieses Niveau kommen.
Was ich bisher noch lernen muss
Vieles ist noch Work in Progress. Ich merke zum Beispiel, dass ich in manchen Situationen noch zu lange in der Oberfläche bleibe, statt schneller in M zu denken. Auch Performance ist ein Thema, das ich noch systematischer verstehen will: Welche Schritte sind teuer, wann puffert man sinnvoll und wann ist ein Join besser als eine alternative Struktur?
Außerdem will ich mir gezielt mehr Standardmuster aufbauen. Also nicht nur wissen, welcher Button wo sitzt, sondern ein echtes Repertoire haben für Parsing, Ranking, Running Totals, Kalenderlogik, Pairing-Probleme oder regelbasierte Transformationen.
Mein vorläufiges Fazit
Power Query löst nicht jedes Excel-Esports-Problem besser als Formeln. Aber es zwingt zu einer anderen Art von Klarheit und ist meiner Meinung nach für den Arbeitsalltag oft wichtiger.
Mein bisher größtes Learning ist vielleicht dieses: Viele Datenprobleme werden leichter, sobald man aufhört, das bestehende Layout zu respektieren. Wenn die Struktur schlecht ist, darf man sie umbauen. Power Query ist genau dafür gemacht.
Das war der erste Debrief. Weitere werden vermutlich konkreter und technischer, sobald ich mehr Cases auf diese Weise durchgespielt habe.