Über den Folder Import gab es in diesem Blog schon mehrere Beiträge (hier und hier). Ein abgewandelter Anwendungsfall ist der Folderimport auf "verstreut abgelegte" Datenfiles und/oder auf Files mit indviduellen Sheet- oder Tabellennamen. In diesem Fall werden in einer Excel Liste die (gleichartig aufgebauten) Importfiles definiert, die beim nächsten Importlauf eingelesen werden sollen. Weiters eignet sich diese Anwendung sehr gut, wenn die zu importierenden Files auf Basis von Formeln ermittelt werden sollen.

Technisch gesehen handelt es sich bei der hier vorgestellten Lösung um eine Query mit einer dynamischen Datenquelle, d.h. die Information zur Datenquelle wird importiert und als Source in die Importquery eingesetzt. In Analogie zum gut dokumentierten "Web Scraping" (hier und hier) wird hier ein Anwendungsfall zum "File Scraping" umgesetzt.

Leider machen derzeit dynamische Datenquellen aufgrund bekannter Limitierungen sowohl im Power BI Cloud Service als auch in Power Query für Excel noch Probleme, lediglich die Anwendung in Power BI Desktop funktioniert derzeit so unkompliziert wie wir uns das auch für die anderen beiden Technologien wünschen. Es gibt aber auch schon heute Lösungswege und Turnarounds, die im konkreten Anwendungsfall im Vorfeld evaluiert werden sollten.

Anforderungen

Die Liste der (aktuell) zu importierenden (gleichartig aufgebauten) Files wird in einer Excel-Fileliste definiert, dazu wird auch das variabel benannte Importsheet in der Fileliste als weiterer Parameter definiert. Der definierten Transformationen sollen in einer Schleife auf alle Files der Liste angewendet werden und die fertig transformierten Daten wie beim Folderimport in einer einzigen Outputtabelle ausgegeben werden (Append):

Die Spalte Import dient dazu, die konkret zu importierenden Files einfach steuern zu können, ohne Einträge aus der Liste entfernen zu müssen.

Die folgende Lösung wird in 7 Schritten umgesetzt:

• Schritt 1: Einzelfile importieren und Transformationen erstellen
• Schritt 2: Parametrisieren der Query
• Schritt 3: Erstellen der aufrufbaren M-Function
• Schritt 4: Excel Fileliste importieren
• Schritt 5: Aufrufen der M-Function und realisieren des Imports der Fileliste
• Schritt 6: Testen des Importdefinition
• Schritt 7: Umgang mit den bekannten Limitierungen

Vor der Umsetzung einer Lösung sollten die bekannten Limitierungen (= Schritt 7) für den konkreten Anwendungsfall evaluiert werden.

Schritt 1: Einzelfile importieren und Transformationen erstellen

Im ersten Schritt wird eine beliebige Datei der gleichartig aufgebauten Excel Files ganz normal mit dem Excel Konnektor importiert und die gewünschten Transformationen durchgeführt:

Schritt 2: Parametrisieren der Query

Als nächstes muß diese Query parametrisiert werden, damit diese später als Funktion in einer Schleife ausgeführt werden kann.

Da unsere Excel Fileliste zwei importrelevante Parameter enthält, werden zwei entsprechend benannte Textparameter angelegt. Der "Current Value" spielt für den späteren Import der Fileliste keine Rolle, dieser wird aber für die Preview der Transformation Query benötigt:

Jetzt wird in der Transformation Query der hard-codierte Dateipfad ...

... durch den dynamischen Parameter_Filepath ersetzt. An der Preview ändert sich nichts, da der Current Value auf das gleiche File verweist, für das auch der initiale Import erstellt wurde:

Der ebenfalls hard-codierte Sheetname ...

... wird analog durch den Parameter_Sheetname dynamisiert:

Schritt 3: Erstellen der aufrufbaren M-Function

Jetzt wird aus dem Kontextmenü der Transformation Query mit dem Befehl Create Function ... eine M-Function erstellt, damit diese später in einer Schleife x-fach aufgerufen werden kann:

Als Namen haben wir fn Transformation File vergeben. Die im M-Code der ursprünglichen Transformation Query verknüpften Parameter werden erkannt und können bzw. müssen beim Aufruf der M-Function als Parameter übergeben werden:

Sehr wichtig ist das Grundverständnis, daß die M-Function mit der Transformation Query - wie auch beim Folderimport - mittels Sync verbunden ist. D.h. wird die Transformation Query geändert, so wird der M-Code der Query automatisch in verknüpfte M-Function aktualisiert. Durch den Aufruf des Advanced Editor würde dieser Sync beendet, daher sollte dieser Schritt besser nicht durchgeführt werden:

Schritt 4: Excel Fileliste importieren

Die Excel Fileliste wird ganz normal über den Excel Konnektor eingelesen ...

... und die benötigten Transformationen definiert. Der Filter auf die Spalte Import dient zur Realisierung einer einfachen Importsteuerung:

Schritt 5: Aufrufen der M-Function und realisieren des Imports der Fileliste

In der Query für die Fileliste wird jetzt mit der Funktion Invoke Custom Function die M-Function aufgerufen:

Der zentrale Schritt ist jetzt die Verknüpfung der beiden importrelevanten Spalten der Fileliste mit den beiden Parametern der M-Function. Auf diese Weise werden die Inhalte der Fileliste als Parameter (= Variablen) in die Transformation pro File übergeben:

Das Ergebnis des Funktionsaufrufs ist eine Structured Column, wie sie aus anderen Kontexten in Power Query vermutlich schon bekannt ist. Als optionaler Schritt kann jetzt mit dem Befehl Reference eine neue (verknüpfte) Query erstellt werden, damit die Wartung der Fileliste getrennt von der Output Query ermöglicht wird:

Die Structured Column enthält in den einzelnen Feldern bereits das Ergebnis der vollständigen Transformation, also die jeweilige Ergebnistabelle des Files der jeweiligen Zeile der Fileliste. Mit dem Befehl Expand werden die Ergebnistabellen aus der Structured Column extrahiert:

Wie bei jedem Funktionsaufruf in Power Query werden die Datentypen, die ja bereits in der Transformation Query definiert wurden, nicht übernommen. Daher müssen die Datentypen der Spalten nochmals in der Output Query definiert:

Zum Abschluß wird mit dem Befehl Enable Load die Datenausgabe der beiden vorgelagerten Queries Transformation File und Fileliste deaktiviert, da nur die Output Query Importdata im Datenmodell benötigt wird:

Der Import wird jetzt entsprechend der definierten Excel-Fileliste ausgeführt:

Schritt 6: Testen des Importdefinition

Zum Test wird die Aktivierung der zu importierenden Files geändert ...

... und der Import in Power BI Desktop ausgeführt:

Schritt 7: Umgang mit bekannten Limitierungen

Aktuell gibt es leider für diese an sich sehr effiziente Art der Importsteuerung folgende (mir bekannte) Limitierungen.

7.1 Keine "normalen" Parameter möglich

Sämtliche in die Transformation Query verknüpften Parameter werden zu "dynamischen Variablen" in der verknüpften M-Function. Daher kann bspw. der erste Teil des Importpfads in obiger Anwendung nicht mehr mittels eines "normalen" Parameters wartbar gemacht werden, da dieser automatisch zu einem dynamischen Eingabeparameter für die M-Function wird und damit seine Funktion verliert.

Lösungsweg:

Weitere Parameter können in die Excel Fileliste als sogenannter Parameter Table integriert werden und auf diese Weise zentral gewartet werden (Anleitung hier).

7.2 Refresh dynamischer Datenquellen im PBI Cloud Service (derzeit) nicht möglich

Leider können aktuell Datasets mit einer dynamischen Datenquelle im PBI Cloud Service nicht aktualisiert werden:

Laut Online Hilfe ist dieser Sachverhalt bereits in PBI Desktop im Dialog Data Source Settings anhand der folgenden Meldung ersichtlich:

Lösungsweg:

Eine Lösung gibt es entsprechend der folgenden Quellen derzeit nur bei der Verwendung des Web.Contents() Konnektors. Der oben beschriebene Excel Import kann daher möglicherweise durch Transfer der Importdateien auf OneDrive / SharePoint Online / MS Teams aktualisierbar sein. Diesen Versuch habe ich im Rahmen dieses Blogbeitrags nicht unternommen, Feedback ist willkommen!

https://learn.microsoft.com/en-us/power-bi/connect-data/refresh-data#refresh-and-dynamic-data-sources

https://blog.crossjoin.co.uk/2016/08/23/web-contents-m-functions-and-dataset-refresh-errors-in-power-bi

7.3 Formula.Firewall Fehler bei Power Query in Excel

Wird die hier beschriebene Lösung nicht in Power BI Desktop sondern in Power Query für Excel umgesetzt, dann funktionieren die Queries unmittelbar nach der Umsetzung einwandfrei. Sobald aber ein anderer User versucht, die Queries zu aktualisieren, tritt folgender Fehler auf:

Es scheint dann so, also ob der Privacy Level (Public, Organiziational, Private) der Datenquelle definiert werden müsste. Die Definition des Privacy Levels bspw. mit "Organizational" oder "Private" führt aber zu keiner Lösung.

Lösungsweg:

Einen Versuch wert ist das Abschalten der Sicherheitsstufen in den Abfrageoptionen. Das ist einerseits nicht empfohlen und nur dann möglich, wenn ausschließlich Daten aus dem eigenen Firmennetzwerk importiert werden. Andererseits "hält" das Setting häufig nicht bei der Verwendung durch andere User, die Option wird dann immer wieder auf die erste Option zurückgesetzt:

Weitere Lösungsansätze sind hier zu finden:

https://answers.microsoft.com/en-us/msoffice/forum/all/power-query-get-transform-dynamic-folder-or-file/4fa172b1-900d-44f5-8bb4-0950e9c71d62

https://community.powerbi.com/t5/Desktop/Formula-Firewall-Query-references-other-queries-so-it-may-not/td-p/18619

https://excelguru.ca/power-query-errors-please-rebuild-this-data-combination/

https://www.thepoweruser.com/2019/03/12/data-privacy-and-the-formula-firewall/

Quellen:

https://www.thebiccountant.com/2018/03/22/web-scraping-2-scrape-multiple-pages-power-bi-power-query/

https://radacad.com/custom-functions-made-easy-in-power-bi-desktop