12. Aug 2019
Lesedauer 9 Min.
Power Query: Das bessere Query-Tool
Datenzugriff und -verarbeitung mit Power Query
Das leistungsstarke Werkzeug wurde geschaffen, um Daten abzurufen und zu transformieren. Im Zusammenspiel mit Visual Studio wird es noch mächtiger.
Autor
Mit Office 2010 hat Microsoft für Excel mehrere Werkzeuge veröffentlicht, die einen erweiterten Datenbankzugriff und Möglichkeiten eines Reportings eröffnen: Power Query, Power Pivot, Power Map und Power View. Man musste sie in der damaligen Excel-Version aus dem Internet herunterladen und in Excel installieren. Ab der Version 2013 ist Power Query fester Bestandteil von Excel. In der Version 2016 und 2019 residiert dieses Programm in Excel unter Daten abrufen und transformieren im Register Daten.Parallel zu Excel wurde dieses Werkzeug in Power BI integriert, das kostenlos heruntergeladen werden kann [1]. Nicht nur, dass Microsoft damit ein mächtiges Werkzeug für Excel und Power BI geschaffen hat; auch im Zusammenspiel mit Visual Studio wird es zu einer cleveren Hilfe für den Zugriff auf Daten und für deren Verarbeitung.
Abrufen: Verbindung zu externen Quellen
Excel und Power BI können mit Daten abrufen und transformieren – wir sprechen in diesem Artikel von Power Query, dem Namen, unter dem es in Excel 2013 installiert wurde – auf sehr unterschiedliche Datenquellen zugreifen. Dabei sind einige sehr bemerkenswert. Doch der Reihe nach.In Excel können Sie einen Datenbereich als Tabelle formatieren oder ihn in eine Tabelle umwandeln. In der Literatur und in Online-Videos bezeichnen viele diese Liste als „intelligente Tabelle“ oder „formatierte Tabelle“ (Bild 1). Diese kann ebenso in Power Query eingebunden werden wie ein Tabellenblatt einer gespeicherten Excel-Arbeitsmappe, eine Text- oder CSV-Datei, eine XML- oder eine JSON-Datei. Aber auch einen Ordner (mit allen darin befindlichen Excel-Tabellen oder Dateien anderer Formate) kann man als Grundlage für einen Datenzugriff verwenden, ebenso wie eine Internetseite, die Tabellen enthält. Möglich ist der Zugriff auf eine SharePoint-Liste oder eine Azure-Datenbank. Interessant ist sicher auch die immense Zahl weiterer Datenbanken, die Power Query anzapfen kann – von Access bis Oracle und SQL Server, wie man auf Bild 2a und Bild 2b erkennen kann.
Eine intelligente/formatierte Tabelle– erkennbar an den Tabellentools(Bild 1)
Autor
… und ebenso umfangreichist die Liste der Datenbanken, die Power Query anzapfen kann(Bild 2b)
Autor
Die Liste an Dateiformaten, auf die Power Queryzugreifen kann, ist lang …(Bild 2a)
Autor
Mit einer Einfach- oder Mehrfachselektion können diese Tabellen, Listen oder Abfragen importiert werden. Wird eine Tabelle einer dieser Datenquellen geladen und bearbeitet, so wird sie im Power-Query-Editor geöffnet (Bild 3).
Der Power-Query-Editor(Bild 3)
Autor
Schließen & Laden in
Im Power-Query-Editor kann die Liste – nachdem sie transformiert wurde – mit der Schaltfläche Schließen & Laden in zurück nach Excel geschrieben werden. Dabei stehen Ihnen zwei Varianten zur Verfügung: Entweder Sie schreiben die Daten als Tabelle, PivotTable oder PivotChart in ein vorhandenes oder neues Tabellenblatt. Oder Sie erstellen eine Verbindung. Dann wird diese Verbindung (und alle anderen vorhandenen Verbindungen) im Aufgabenbereich Abfragen und Verbindungen angezeigt, wie Sie in Bild 4 sehen.
Die Datenwerden nach Excel zurückgeschrieben – als Tabelle oder als Verbindung(Bild 4)
Autor
Der Editor
Zurück zum Editor: Auf dessen linker Seite befindet sich der Aufgabenbereich, der die Abfrage beziehungsweise die Abfragen anzeigt. Das Fenster Abfrageeinstellungen auf der rechten Seite listet alle Schritte auf, die getätigt werden. Der aktuelle Schritt wird in der Bearbeitungsleiste gezeigt.Das Tabellenfenster zeigt das Ergebnis der aktuellen Abfrage an – allerdings nicht vollständig, sondern nur die ersten 100 Zeilen. Scrollt man an den unteren Rand, werden weitere 100 Zeilen eingeblendet. Das bedeutet: Man kann nicht zum unteren Rand der Liste springen. Andererseits beschleunigt dieses Query-Folding den Zugriff auf die Daten, da hierfür nur die ersten Datensätze der Datenquelle gelesen und angezeigt werden müssen (Bild 5).
Mehrere Abfragenim Editor(Bild 5)
Autor
Anpassen der Daten: Transformation
Im Power-Query-Editor können Sie all die Aktionen durchführen, für die auch Excel Assistenten, Funktionen und Befehle zur Verfügung stellt:- Zeilen und Spalten löschen und einfügen,
- sortieren und filtern,
- gruppieren,
- Werte ersetzen,
- Spalten trennen und zusammenfügen,
- ausfüllen nach unten.
Die Datentypenkönnen auch über das Kontextmenü festgelegt werden(Bild 6)
Autor
Die angewendeten Schritte
Jeder Schritt, der durchgeführt wurde, wird im Aufgabenbereich Abfrageeinstellungen mitprotokolliert. Dort können Sie zu einem weiter zurückliegenden Schritt navigieren. Fehlerhafte Anweisungen lassen sich löschen. Mehr noch: Sie können sogar zwischen zwei weiter zurückliegenden Schritten einen neuen Schritt einfügen. In der Bearbeitungsleiste ist jeder einzelne Schritt einsehbar. Über den erweiterten Editor, den Sie in der Gruppe Abfrage in der Registerkarte Start finden, können Sie sämtliche Schritte einsehen und ändern (Bild 7).
Ein Ändern von vorhandenen Befehlenoder nachträgliches Einfügen von Befehlen in die bestehende Abfolge ist möglich(Bild 7)
Autor
Die Sprache M
Die angewendeten Schritte werden zeilenweise mitprotokolliert. Die dahinterliegende Sprache heißt M.Jeder Befehl endet mit einem Komma. Der gesamte Code ist in zwei let … in-Blöcken eingeschlossen. Im let-Teil stehen die einzelnen Zeilen, die von einem Namen eingeleitet werden (als Quasivariable, der der Wert einer Funktion übergeben wird). Die Zeilen werden linear von oben nach unten abgearbeitet – Sprünge sind natürlich möglich. Im in-Teil befindet sich das Ergebnis der Transformation. Der von Power Query erzeugte Code kann modifiziert werden. In Bild 8 sehen Sie ein Codebeispiel.
Der erweiterte Editormit einem Codebeispiel(Bild 8)
Autor
Es stehen die klassischen Funktionen zur Verfügung:
- Textfunktionen, darunter zum Beispiel Text.Length, Text.StartsWith, Text.Middle, Text.PositionOf …
- Datumsfunktionen, darunter zum Beispiel Date.Day, Date.DayOfWeek, Date.QuarterOfYear, DateTime.LocalNow …
- Zahlenfunktionen, darunter zum Beispiel Number.Exp, Number.Sin, Number.Factorial …
- Verzweigungen wie if
Listing 1: Ein Codebeispiel für eine Abfrage auf eine Datei
let <br/> Quelle = Excel.Workbook(File.Contents(<span class="hljs-string">"D:\Eigene Dateien\SAP01.xlsx"</span>), null, <span class="hljs-literal">true</span>), <br/> Tabelle1_Sheet = Quelle{[Item=<span class="hljs-string">"Tabelle1"</span>,Kind=<span class="hljs-string">"Sheet"</span>]}[Data], <br/> #<span class="hljs-string">"Höher gestufte Header"</span> = Table.PromoteHeaders(Tabelle1_Sheet, [PromoteAllScalars=<span class="hljs-literal">true</span>]), <br/> #<span class="hljs-string">"Geänderter Typ"</span> = Table.TransformColumnTypes(#<span class="hljs-string">"Höher gestufte Header"</span>,{{<span class="hljs-string">"Status:"</span>, <span class="hljs-keyword">type</span> <span class="hljs-type">text}, </span>{<span class="hljs-string">"Column2"</span>, <br/> <span class="hljs-keyword">type</span> <span class="hljs-type">any}, </span>{<span class="hljs-string">"Column3"</span>, <span class="hljs-keyword">type</span> <span class="hljs-type">any}, </span>{<span class="hljs-string">"Column4"</span>, <span class="hljs-keyword">type</span> <span class="hljs-type">any}, </span>{<span class="hljs-string">"offen"</span>, <span class="hljs-keyword">type</span> <span class="hljs-type">text}, </span>{<span class="hljs-string">"Column6"</span>, <span class="hljs-keyword">type</span> <span class="hljs-type">any}, </span><br/><span class="hljs-type"> </span>{<span class="hljs-string">"Column7"</span>, <span class="hljs-keyword">type</span> <span class="hljs-type">text}, </span>{<span class="hljs-string">"Column8"</span>, Int64.<span class="hljs-keyword">Type</span>}, {<span class="hljs-string">"Column9"</span>, <span class="hljs-keyword">type</span> <span class="hljs-type">text}, </span>{<span class="hljs-string">"vorerfasst"</span>, <span class="hljs-keyword">type</span> <span class="hljs-type">text}, </span><br/><span class="hljs-type"> </span>{<span class="hljs-string">"Column11"</span>, <span class="hljs-keyword">type</span> <span class="hljs-type">any}, </span>{<span class="hljs-string">"Column12"</span>, <span class="hljs-keyword">type</span> <span class="hljs-type">any}, </span>{<span class="hljs-string">"ausgegl."</span>, <span class="hljs-keyword">type</span> <span class="hljs-type">text}, </span>{<span class="hljs-string">"Column14"</span>, <span class="hljs-keyword">type</span> <span class="hljs-type">text}, </span>{<span class="hljs-string">"Column15"</span>, <br/> <span class="hljs-keyword">type</span> <span class="hljs-type">any}, </span>{<span class="hljs-string">"Column16"</span>, <span class="hljs-keyword">type</span> <span class="hljs-type">text}, </span>{<span class="hljs-string">"Column17"</span>, <span class="hljs-keyword">type</span> <span class="hljs-type">any}, </span>{<span class="hljs-string">"Column18"</span>, <span class="hljs-keyword">type</span> <span class="hljs-type">text}, </span>{<span class="hljs-string">"Column19"</span>, <span class="hljs-keyword">type</span> <span class="hljs-type">text}}), </span><br/><span class="hljs-type"> </span>#<span class="hljs-string">"Andere entfernte Spalten"</span> = Table.SelectColumns(#<span class="hljs-string">"Geänderter Typ"</span>,{<span class="hljs-string">"Column4"</span>, <span class="hljs-string">"Column11"</span>, <span class="hljs-string">"Column12"</span>, <br/> <span class="hljs-string">"Column15"</span>, <span class="hljs-string">"Column16"</span>, <span class="hljs-string">"Column19"</span>}), <br/> #<span class="hljs-string">"Entfernte oberste Zeilen"</span> = Table.Skip(#<span class="hljs-string">"Andere entfernte Spalten"</span>,<span class="hljs-number">9</span>), <br/> #<span class="hljs-string">"Höher gestufte Header1"</span> = Table.PromoteHeaders(#<span class="hljs-string">"Entfernte oberste Zeilen"</span>, [PromoteAllScalars=<span class="hljs-literal">true</span>]), <br/> #<span class="hljs-string">"Geänderter Typ1"</span> = Table.TransformColumnTypes(#<span class="hljs-string">"Höher gestufte Header1"</span>,{{<span class="hljs-string">"Zuordnung"</span>, <span class="hljs-keyword">type</span> <span class="hljs-type">any}, </span><br/><span class="hljs-type"> </span>{<span class="hljs-string">"Belegdatum"</span>, <span class="hljs-keyword">type</span> <span class="hljs-type">date}, </span>{<span class="hljs-string">"Buch.dat."</span>, <span class="hljs-keyword">type</span> <span class="hljs-type">date}, </span>{<span class="hljs-string">"Betrag in BW"</span>, <span class="hljs-keyword">type</span> <span class="hljs-type">any}, </span>{<span class="hljs-string">"Währg"</span>, <span class="hljs-keyword">type</span> <span class="hljs-type">text}, </span><br/><span class="hljs-type"> </span>{<span class="hljs-string">"Text"</span>, <span class="hljs-keyword">type</span> <span class="hljs-type">text}}), </span><br/><span class="hljs-type"> </span>#<span class="hljs-string">"Entfernte oberste Zeilen1"</span> = Table.Skip(#<span class="hljs-string">"Geänderter Typ1"</span>,<span class="hljs-number">1</span>), <br/> #<span class="hljs-string">"Geänderter Typ2"</span> = Table.TransformColumnTypes(#<span class="hljs-string">"Entfernte oberste Zeilen1"</span>,{{<span class="hljs-string">"Betrag in BW"</span>, <span class="hljs-keyword">type</span> <span class="hljs-type">text}}), </span><br/><span class="hljs-type"> </span>#<span class="hljs-string">"Hinzugefügte benutzerdefinierte Spalte"</span> = Table.AddColumn(#<span class="hljs-string">"Geänderter Typ2"</span>, <span class="hljs-string">"Betrag korr"</span>, each <span class="hljs-keyword">if</span> Text.<br/> EndsWith([#<span class="hljs-string">"Betrag in BW"</span>],<span class="hljs-string">"-"</span>) <span class="hljs-keyword">then</span> <br/> <span class="hljs-string">"-"</span> &amp; Text.Replace([#<span class="hljs-string">"Betrag in BW"</span>], <span class="hljs-string">"-"</span>, <span class="hljs-string">""</span>) <br/> <span class="hljs-keyword">else</span> <br/> [#<span class="hljs-string">"Betrag in BW"</span>]), <br/> #<span class="hljs-string">"Geänderter Typ3"</span> = Table.TransformColumnTypes(#<span class="hljs-string">"Hinzugefügte benutzerdefinierte Spalte"</span>,{{<span class="hljs-string">"Betrag korr"</span>, <br/> <span class="hljs-keyword">type</span> <span class="hljs-type">number}}), </span><br/><span class="hljs-type"> </span>#<span class="hljs-string">"Entfernte Spalten"</span> = Table.RemoveColumns(#<span class="hljs-string">"Geänderter Typ3"</span>,{<span class="hljs-string">"Betrag in BW"</span>}), <br/> #<span class="hljs-string">"Geänderter Typ4"</span> = Table.TransformColumnTypes(#<span class="hljs-string">"Entfernte Spalten"</span>,{{<span class="hljs-string">"Zuordnung"</span>, <span class="hljs-keyword">type</span> <span class="hljs-type">text}}), </span><br/><span class="hljs-type"> </span>#<span class="hljs-string">"Ersetzter Wert"</span> = Table.ReplaceValue(#<span class="hljs-string">"Geänderter Typ4"</span>,<span class="hljs-string">"E"</span>,<span class="hljs-string">""</span>,Replacer.ReplaceText,{<span class="hljs-string">"Zuordnung"</span>}) <br/><span class="hljs-keyword">in</span> <br/> #<span class="hljs-string">"Ersetzter Wert"</span>
Hierzu einige Erläuterungen: Die Datei D:\Eigene Dateien\SAP01.xlsx wird geöffnet, einige Spalten und Zeilen werden gelöscht, die erste Zeile wird zur Überschriftenzeile, der Datentyp einer Spalte wird zu Text geändert, das falsche Minus an Ende der negativen Zahlen wird nach vorn geholt und der Buchstabe „E“ wird gelöscht (siehe Bild 9a und Bild 9b).
Die zu importierende Datei(links) …(Bild 9a)… und das Ergebnisder abgearbeiteten Abfrage(Bild 9b)
Autor
Diese Zeilen sind leicht verständlich, ebenso wie auch selbst geschriebene if-Funktionen der folgenden Form:
if Text.EndsWith([#"Betrag in BW"],"-") then
"-" & Text.Replace([#"Betrag in BW"], "-", "")
else
[#"Betrag in BW"]),
Entsprechend hätte man statt Table.ReplaceValue(#”Geänderter Typ4”,”E”,””,Replacer.ReplaceText,{”Zuordnung”}) (lösche den Anfangsbuchstaben „E“) schreiben können:
#"Hinzugefügte benutzerdefinierte Spalte" = Table.
AddColumn(#"Geänderter Typ1", "ZuordnungOhneE", each
if Text.StartsWith([Zuordnung], "E") then
Text.Middle([Zuordnung], 1)
else
[Zuordnung]),
#"Geänderter Typ2" = Table.TransformColumnTypes(
#"Hinzugefügte benutzerdefinierte Spalte",
{{"ZuordnungOhneE", Int64.Type}}),
Erläuterung: Wenn die ID der Spalte Zuordnung mit einem „E“ beginnt, wird der Text nach diesem Buchstaben herausgelöst. Anschließend wird die Spalte in einen ganzzahligen Wert konvertiert. Wie Sie sehen, beginnt die Zählung bei 0.
Power-Query-Abfragen mit Visual Studio anstoßen, generieren und modifizieren
Da die Skriptsprache M ihre Befehle als Text zur Verfügung stellt, können diese Befehle mit einer Programmiersprache erzeugt oder modifiziert werden. In gleicher Weise kann eine Aktualisierung durchgeführt werden.Ein Beispiel soll das veranschaulichen. Stellen Sie sich folgendes Szenario vor: Sie erhalten jede Woche einen Download als Excel-Datei mit den aktuellen Preisen Ihrer Produkte. Diese Preise liegen in verschiedenen Währungen vor. Sie sollen in eine Leitwährung – beispielsweise Euro – umgerechnet werden. Hierzu sind die Tageswechselkurse nötig. Diese sollen aus dem Internet geholt und in die aktuelle Excel-Mappe eingefügt werden, und danach zum Beispiel mit einem SVERWEIS verknüpft werden.Power Query kann auf das Internet zugreifen und Tabellen einer Seite verarbeiten. Unter [2] befinden sich in mehreren Tabellen die aktuellen Währungskurse. Diese sechs Tabellen werden zusammengefasst, bereinigt, aufbereitet und mit Euro als Leitwährung (Euro zu Euro: Kurs 1) erweitert. Diese Liste wird in die aktuelle Datei in eine neue Tabelle geschrieben. Dort kann sie jederzeit aktualisiert werden, wenn Internetzugriff besteht. Den Code in M sehen Sie in Listing 2.Listing 2: Greife auf eine Internetseite zu, fasse Tabellen von dort zusammen und schreibe die Daten nach Excel
let <br/> Quelle = Table.Combine({#"Table 0", #"Table 1", #"Table 2", #"Table 3", #"Table 4", #"Table 5"}), <br/> #"Entfernte Spalten" = Table.RemoveColumns(Quelle,{"Kürzel", "Veränderung", "Zeit", "Heute", ""}), <br/> #"Ersetzter Wert" = Table.ReplaceValue(#"Entfernte Spalten","EUR/","",Replacer.ReplaceText,{"Kurs"}), <br/> #"Spalte nach Position teilen" = Table.SplitColumn(#"Ersetzter Wert", "Kurs", <br/> Splitter.SplitTextByPositions({0, 3}, false), {"Kurs.1", "Kurs.2"}), <br/> #"Geänderter Typ" = Table.TransformColumnTypes(#"Spalte nach Position teilen",{{"Kurs.1", type text}, <br/> {"Kurs.2", type text}}), <br/> #"Entfernte Spalten1" = Table.RemoveColumns(#"Geänderter Typ",{"Kurs.2"}), <br/> #"Umbenannte Spalten" = Table.RenameColumns(#"Entfernte Spalten1",{{"Kurs.1", "Währung"}, {"in %", "Kurs"}}), <br/> #"Euro Einfügen"= Table.InsertRows(#"Umbenannte Spalten", 0, { [Land = "EURO-Länder", Währung = "EUR", <br/> Kurs = 1] }) <br/>in <br/> #"Euro Einfügen"
Da mehrere Tabellen zusammengefügt werden, muss eine Abfrage auf jede dieser Tabellen erstellt werden. Das Grundgerüst in VB.NET sieht folgendermaßen aus:
strZugriffAufTabelle = "let" & Chr(13) & "" & Chr(10) &
" Quelle = Web.Page(Web.Contents(""https://www.
consorsbank.de/ev/Wertpapierhandel/Kurse-Maerkte/
Waehrungen""))," & Chr(13) & "" & Chr(10) & " Data
INDEX = Quelle{INDEX}[Data]," & Chr(13) & "" & Chr(10)
& " #""Geänderter Typ"" = Table.TransformColumn
Types(DataINDEX,{{""Land"", type text}, {""Kürzel"",
type text}, {""Kurs"", type text}, {""in %"", type
number}, {""Veränderung"", Percentage.Type}, {""Zeit"",
type time}, {""" & "Heute"", type text}, {"""", type
text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) &
"" & Chr(10) & " #""Geänderter Typ"""
Die Objektvariable xlDatei greift auf die aktuelle Datei zu:
xlDatei = Globals.ThisAddIn.Application.ActiveWorkbook
Mit einer Schleife wird mithilfe der Variablen strZugriffAufTabelle jeweils eine Abfrage für jede Tabelle der Internetseite erzeugt:
For i = 0 To 5
xlDatei.Queries.Add(Name:="tbl_Kurstabelle"
& i, Formula:=Replace(strZugriffAufTabelle,
"INDEX", i))
Next
Die neu erzeugten Abfragen tragen nun die Namen tbl_Kurstabelle0, tbl_Kurstabelle1, tbl_Kurstabelle2 …Der eigentliche M-Code wird an die Variable strM_Code übergeben:
strM_Code = "let" & vbCr & "Quelle = Table.
Combine({#""tbl_Kurstabelle0"", #""tbl_
Kurstabelle1"", #""tbl_Kurstabelle2"",
[...]
Hier wird mit den neuen Namen tbl_Kurstabelle0, tbl_Kurstabelle1 … gearbeitet.Damit wird eine neue Abfrage erzeugt:
xlAbfrage = xlDatei.Queries.Add(Name:=strAbfrageName,
Formula:=strM_Code, Descripton:=strAbfrageBeschreibung)
Eine Verbindung wird hergestellt:
xlDatei.Connections.Add2(Name:="Query - " & xlAbfrage.
Name, Description:="Connection To the ‚" & xlAbfrage.
Name & "‘ query in the workbook.", ConnectionString:=
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=
$Workbook$;Location=" & xlAbfrage.Name, CommandText:=
"""" & xlAbfrage.Name & """", lCmdtype:=6, CreateModel
Connection:=True, ImportRelationships:=False)
Das Ganze kann nun als Tabelle in ein Arbeitsblatt geschrieben werden:
xlListObjekt = xlBlatt.ListObjects.Add(SourceType:=4,
Source:=xlDatei.Connections("Query - " &
xlAbfrage.Name), Destination:=xlBlatt.Range("$A$1"))
xlTabellenObjekt = xlListObjekt.TableObject
With xlTabellenObjekt
.RowNumbers = False
.PreserveFormatting = True
.PreserveColumnInfo = False
.AdjustColumnWidth = True
.RefreshStyle = 1
.ListObject.DisplayName =
Replace(xlAbfrage.Name, " ", "_") &
"_ListObject"
.Refresh()
End With
Das Ergebnis sehen Sie in Bild 10.
Das Ergebnisdes Internetzugriffs(Bild 10)
Autor
Randbemerkung: Da Power Query Case-sensitiv ist, werden die USA zwischen die Türkei und Ungarn einsortiert: Also gilt: Türkei < USA < Ungarn. Eine Lösung: Man ersetzt „USA“ durch „Usa“, sortiert dann und ersetzt anschließend „Usa“ in „USA“. Oder man ändert in M (im erweiterten Editor) den Sortierbefehl in folgender Weise ab:
#"Sortierte Zeilen" = Table.Sort(#"Umbenannte Spalten",
{{each Text.Upper([Land]), Order.Ascending}})
Dabei steht Umbenannte Spalten für den vorhergehenden Befehl und Land für die zu sortierende Spalte. Die Funktion Text.Upper ändert die Schreibweise der Länderbezeichnungen in Großbuchstaben, und Table.Sort sortiert diese. Anders als in Excel gibt es übrigens in Power Pivot leider keinen Schalter (oder Parameter), um die Sortierung nicht Case-sensitiv durchzuführen.
Bestimmte Tabellen aktualisieren
Ein weiteres Beispiel. Stellen Sie sich folgendes Szenario vor: Sie haben eine Excel-Tabelle, die mit mehreren Dutzend Abfragen aus einer Datenbank (oder mehreren Datenbanken) Daten herausholt, die dann in einem Dashboard aufbereitet werden. Es kann sich hierbei um eine Oracle-Datenbank, einen SQL Server oder auch nur um Access handeln. Da nun einige der Tabellen mehrere Millionen Datensätze haben, würde es lange dauern, alle Abfragen mit einem RefreshAll zu aktualisieren. Umgekehrt wäre es auch zeitraubend und fehleranfällig, „per Hand“ die betroffenen Tabellen zu aktualisieren. Abhilfe schafft ein kleines Skript, das überprüft, welche Abfrage von welcher Verbindung verwendet wird, um gezielt diese zu aktualisieren. In Listing 3 sehen Sie die Aktualisierungsroutine, die überprüft, ob eine Abfrage ihre Daten aus der Datenbank Nordwind abruft. Bild 11 zeigt das Dashboard, Bild 12 die durchgeführte Aktualisierung.Listing 3: Aktualisiere alle Verbindungen, die einen Zugriff auf die Datenbank „Nordwind“ vornehmen
Sub AktualisiereNordwind() <br/> Dim i As Integer <br/> Dim j As Integer <br/> Dim intZähler As Integer = 0 <br/><br/> Dim xlDatei As Excel.Workbook <br/> xlDatei = <br/> Globals.ThisAddIn.Application.ActiveWorkbook <br/><br/><br/> For i = 1 To xlDatei.Queries.Count <br/> If xlDatei.Queries(i).Formula Like "*Nordwind*" <br/> Then <br/> For j = 1 To xlDatei.Connections.Count <br/> If xlDatei.Connections(j).Name Like "*" &amp; <br/> xlDatei.Queries(i).Name Then <br/> xlDatei.Connections(j).Refresh() <br/> intZähler = intZähler + 1 <br/> End If <br/> Next j <br/> End If <br/> Next i <br/><br/> MsgBox(intZähler &amp; " Abfragen wurden aktualisiert.") <br/><br/>End Sub 
Die Abfragen, die auf die Datenbank „Nordwind“ aufsetzen, werden aktualisiert(Bild 12)
Autor
Ein Dashboardmit verschiedenen Tabellen(Bild 11)
Autor
Visual Studio als Editor für M
Leider unterstützt der erweiterte Editor weder in Excel noch in Power BI IntelliSense. Zwar soll es in nächster Zeit implementiert werden – aber bis dahin muss man leider den M-Code ohne jede Hilfe selbst erstellen. Ein hierfür praktisches Tool finden Sie jedoch unter [3]. Wenn Sie dieses Add-in herunterladen und installieren, steht Ihnen in Visual Studio eine neue Projektvorlage Power Query zur Verfügung (siehe Bild 13). Wenn Sie die Datei PQ file verwenden, erhalten Sie die üblichen Hilfen: IntelliSense, Tooltipps, farbige Codeformatierungen und Fehlerkennzeichnungen. Sie sehen in Bild 14 ein Codebeispiel, das die Arbeitsweise verdeutlicht.
Die Vorlage PQ filefür die bequeme Codeeingabe der Sprache M(Bild 13)
Autor
IntelliSensehilft bei der Codeeingabe(Bild 14)
Autor
Zusammenfassung
Das mächtige Werkzeug Power Query ermöglicht Zugriff auf Datenbanken, auf Dateien, die sich in einem Ordner befinden, auf das Internet und auf Excel-Tabellen beziehungsweise Text-, XML- oder JSON-Dateien. Diese Daten können aufbereitet und nach Excel zurückgegeben oder in Power BI verarbeitet werden. Die einzelnen Schritte werden mitprotokolliert und können gelöscht, geändert oder ergänzt werden.Die zugrunde liegende Sprache heißt M. Da man mit Visual Studio auf Excel zugreifen beziehungsweise Add-ins für Excel erstellen kann, ist es möglich, solche M-Code-Texte neu zu generieren, vorhandene zu modifizieren oder auch Abfragen auf bestimmte Inhalte zu überprüfen. Ebenso hat man über Visual Studio Zugriff auf die Abfragen (Queries) beziehungsweise auf die Datenverbindungen (Connections), die ebenfalls manipuliert, gelöscht oder erzeugt werden können. Und schließlich stellt Microsoft ein Power Query SDK zur Verfügung, das eine bequeme Erstellung von M-Code ermöglicht.Fussnoten
- Power BI, http://www.dotnetpro.de/SL1908PowerQuery1
- Beispieltabellen der aktuellen Währungskurse, http://www.dotnetpro.de/SL1908PowerQuery2
- Power Query SDK, http://www.dotnetpro.de/SL1908PowerQuery3
