Aufrufe: 10104

Der nachfolgende Beitrag dreht sich um die Erstellung von neuen Spalten mit dynamischen Spaltennamen in Power Query.

Aufgabenstellung

Für die Budgetierung sind die Vorjahreswerte pro Produkt bekannt. Für das nächste Jahr ist der aktuelle Umsatz um 1% zu erhöhen. Die Spalte soll den Namen des Budgetjahres enthalten. Die Lösung ist so aufzubauen, dass sie Jahr für Jahr wiederverwendet werden kann.

Willst du Power BI lernen?
Auf unseren neuen Seite Power BI Coach haben wir einen Onlinekurs zusammengestellt.

Lösung

Lasst uns die Aufgabenstellung in einzelne Teilschritte aufteilen.

1. Schritt – dynamische Spaltennamen:

Spalten in einer Tabelle werden meistens mittels “Spalte hinzufügen” und einem fix definierten Namen erstellt.  Beispielsweise

= Table.AddColumn(#”Geänderter Typ”, “2017”, each [2016]*1.01).

Die Namen der auf diese Art in Power Query erstellten Spalten sind jedoch statisch. In Excel hingegen kann dies einfach erreicht werden:

= A1 + 1, wobei z.B. A1 = 2016

In Power Query respektive Power BI funktioniert dies aber nicht und führt zu einer Fehlermeldung:

= Table.AddColumn(#"Geänderter Typ", [2016]+1, each [2016]*1.01)

Für Power Query ist der Wert in der Spalte [2016] nicht eindeutig, da dieser in jeder Zeile ändern kann. Der Workaround ist aber ziemlich rasch zu bewerkstelligen. Dazu benötigen wir lediglich einen eindeutigen Wert.

Workaround

Was wir benötigen, ist der Wert des letzten Jahres, was in unserer Tabelle dem Wert 2016 entspricht. Folgender Befehl extrahiert alle Spaltennamen in eine neue Liste.

=Table.ColumnNames(Source)

Als nächste wird die Liste in eine Tabelle umgewandelt:

= Table.FromList(ColumnNames, Splitter.SplitByNothing(), null, null, ExtraValues.Error)

Wandelt als nächstes das Format in Ganzzahl um, da wir das letzte Jahr extrahieren wollen.

= Table.TransformColumnTypes(#"In Tabelle konvertiert",{{"Column1", Int64.Type}})

Die ergibt folgende Tabelle:

Der erste Wert der Spalte war “Produkt”. Die Umwandlung in eine Ganzzahl ergibt natürlich einen Fehler. Diesen werden wir los mit
= Table.RemoveRowsWithErrors(#"Geänderter Typ", {"Column1"})

Schlussendlich wird das Ganze wieder in eine Liste umgewandelt und aus dieser wird der grösste Wert (=2016) ermittelt.

= #"Entfernte Fehler"[Column1]
und
= List.Max(Column1)

Da ich gerne kompakte Schritte habe, habe ich all die vorgenannten Schritte in einen einzigen zusammengefasst:

Last_Year = List.Max(
Table.RemoveRowsWithErrors(
Table.TransformColumnTypes(
Table.FromList(
Table.ColumnNames(Source), Splitter.SplitByNothing(), null, null, ExtraValues.Error)
,{{"Column1", Int64.Type}}))[Column1]),

Das Resultat können wir als Ausgangswert für den dynamischen Spaltennamen verwenden. Den Wert 2016 erhöhen wir um 1 und wandeln das Ganze in Text um, da Power BI respektive Power Query als Spaltenüberschrift einen Textwert verlangt.

= Table.AddColumn(ChType,
Number.ToText(Last_Year+1),
each [2016]*1.01)

Ka-Ching!

2. Schritt – Werte aus einer dynamisch gewählten Spalte beziehen:

Als nächstes benötigen wir noch den Wert aus dem letzten Jahr als Ausgangswert für die Berechnung. Auch dieser Bezug muss dynamisch sein.

Dazu müssen wir nur noch den Teil “each [2016]*1.01)” ändern.
Da bei Erstellung des Befehls nicht klar, welche Spalte jeweils angesprochen wird, nehmen wir Record.Field zur Hilfe.
Record.Field gibt den Wert eines bestimmten Feldes zurück. Die Syntax lautet:

Record.Field(record as record, field as text) as any

Da der Wert für “Record” wie beschrieben dynamisch sein muss, ersetzen wir den Wert mit (_) respektive abgekürzt _.

= Table.AddColumn(ChType,
Number.ToText(Last_Year+1),
each Record.Field(_,
Number.ToText(Last_Year))*1.01, Int64.Type)

Und schon haben wir den Wert für das nächste Jahr dynamisch errechnet und können die einzelnen Schritte jederzeit auch für das nächste Jahr verwenden.

Nachstehend nochmals der gesamte Code:

Ich hoffe, dass Euch der Beitrag gefallen hat. Bitte hinterlasst Eure Anregungen und Kritiken wie immer gerne im Kommentar.

Mehr über Power BI und insbesondere Power Query findet Ihr im Power Query Online Buch.

Happy Querying!