Inhalte
Wenn Sie bereits begonnen haben, die Tools des kostenlosen Power Query-Add-Ins in Microsoft Excel zu verwenden, werden Sie sehr bald auf ein hochspezialisiertes, aber sehr häufiges und ärgerliches Problem stoßen, das mit dem ständigen Unterbrechen von Links zu Quelldaten verbunden ist. Der Kern des Problems besteht darin, dass, wenn Sie in Ihrer Abfrage auf externe Dateien oder Ordner verweisen, Power Query den absoluten Pfad zu ihnen im Abfragetext hartcodiert. Auf Ihrem Computer funktioniert alles einwandfrei, aber wenn Sie sich entscheiden, eine Datei mit einer Anfrage an Ihre Kollegen zu senden, werden sie enttäuscht sein, weil. Sie haben einen anderen Pfad zu den Quelldaten auf ihrem Computer, und unsere Abfrage funktioniert nicht.
Was tun in einer solchen Situation? Sehen wir uns diesen Fall anhand des folgenden Beispiels genauer an.
Formulierung des Problems
Angenommen, wir haben in dem Ordner E:Berichte über die Verkäufe liegt die Datei Top 100 Produkte.xls, die ein Upload aus unserer Unternehmensdatenbank oder unserem ERP-System (1C, SAP usw.) ist. Diese Datei enthält Informationen zu den beliebtesten Warenartikeln und sieht im Inneren so aus:
Wahrscheinlich ist auf Anhieb klar, dass es in Excel in dieser Form fast unmöglich ist, damit zu arbeiten: Leere Zeilen durch eine mit Daten, verbundene Zellen, zusätzliche Spalten, eine mehrstufige Überschrift usw. stören.
Daher erstellen wir neben dieser Datei im selben Ordner eine weitere neue Datei Handler.xlsx, in der wir eine Power Query-Abfrage erstellen, die hässliche Daten aus der Quell-Upload-Datei lädt Top 100 Produkte.xls, und ordnen Sie sie:
Eine Anfrage an eine externe Datei stellen
Öffnen der Datei Handler.xlsx, wählen Sie auf der Registerkarte aus Datum Befehl Daten abrufen – aus Datei – aus Excel-Arbeitsmappe (Daten – Daten abrufen – Aus Datei – Aus Excel), geben Sie dann den Speicherort der Quelldatei und das benötigte Blatt an. Die ausgewählten Daten werden in den Power Query-Editor geladen:
Lassen Sie uns sie wieder normalisieren:
- Leerzeilen löschen mit Home — Zeilen löschen — Leere Zeilen löschen (Start — Zeilen entfernen — Leere Zeilen entfernen).
- Löschen Sie unnötige oberste 4 Zeilen durch Start — Zeilen löschen — Obere Zeilen löschen (Start — Zeilen entfernen — Obere Zeilen entfernen).
- Heben Sie die erste Zeile mit der Schaltfläche zum Tabellenkopf an Verwenden Sie die erste Zeile als Überschrift Tab Startseite (Home — Erste Zeile als Kopfzeile verwenden).
- Trennen Sie den fünfstelligen Artikel vom Produktnamen in der zweiten Spalte mit dem Befehl geteilte Spalte Tab Transformation (Transformieren – Spalte teilen).
- Löschen Sie unnötige Spalten und benennen Sie die Überschriften der verbleibenden zur besseren Sichtbarkeit um.
Als Ergebnis sollten wir das folgende, viel angenehmere Bild erhalten:
Es bleibt, diese geadelte Tabelle wieder auf das Blatt in unserer Datei hochzuladen Handler.xlsx das Team schließen und herunterladen (Startseite — Schließen&Laden) Tab Startseite:
Suchen des Pfads zu einer Datei in einer Anfrage
Sehen wir uns nun an, wie unsere Abfrage „unter der Haube“ aussieht, in der in Power Query integrierten internen Sprache mit dem prägnanten Namen „M“. Gehen Sie dazu zurück zu unserer Abfrage, indem Sie im rechten Bereich darauf doppelklicken Anfragen und Verbindungen und auf der Registerkarte Bewertung wählen Erweiterter Editor (Ansicht – Erweiterter Editor):
In dem sich öffnenden Fenster zeigt die zweite Zeile sofort einen hartcodierten Pfad zu unserer ursprünglichen Upload-Datei. Wenn wir diese Textzeichenfolge durch einen Parameter, eine Variable oder einen Link zu einer Excel-Tabellenzelle ersetzen können, in der dieser Pfad vorgeschrieben ist, können wir ihn später problemlos ändern.
Fügen Sie eine intelligente Tabelle mit einem Dateipfad hinzu
Lassen Sie uns Power Query vorerst schließen und zu unserer Datei zurückkehren Handler.xlsx. Lassen Sie uns ein neues leeres Blatt hinzufügen und eine kleine „intelligente“ Tabelle darauf erstellen, in deren einzige Zelle der vollständige Pfad zu unserer Quelldatendatei geschrieben wird:
Um eine intelligente Tabelle aus einem regulären Bereich zu erstellen, können Sie die Tastenkombination verwenden Ctrl+T oder Taste Als Tabelle formatieren Tab Startseite (Home — Als Tabelle formatieren). Die Spaltenüberschrift (Zelle A1) kann absolut beliebig sein. Beachten Sie auch, dass ich der Tabelle aus Gründen der Übersichtlichkeit einen Namen gegeben habe Parameter Tab Bauherr (Design).
Einen Pfad aus dem Explorer zu kopieren oder gar manuell einzugeben ist natürlich nicht besonders schwierig, aber am besten minimiert man den menschlichen Faktor und ermittelt den Pfad möglichst automatisch. Dies kann mit der Standard-Excel-Arbeitsblattfunktion implementiert werden CELL (ZELLE), die eine Reihe nützlicher Informationen über die als Argument angegebene Zelle ausgeben kann – einschließlich des Pfads zur aktuellen Datei:
Wenn wir davon ausgehen, dass die Quelldatendatei immer im selben Ordner wie unser Prozessor liegt, dann lässt sich der benötigte Pfad nach folgender Formel bilden:
=LEFT(CELL("Dateiname");FIND("[";CELL("Dateiname"))-1)&"Top 100 products.xls"
oder in englischer Version:
=LEFT(CELL(«Dateiname»);FIND(«[«;CELL(«Dateiname»))-1)&»Топ-100 товаров.xls»
… wo ist die Funktion LEVSIMV (LINKS) nimmt ein Stück Text vom vollständigen Link bis zur öffnenden eckigen Klammer (dh den Pfad zum aktuellen Ordner), und dann wird der Name und die Erweiterung unserer Quelldatendatei daran geklebt.
Parametrieren Sie den Pfad in der Abfrage
Die letzte und wichtigste Berührung bleibt – den Pfad zur Quelldatei in die Anfrage zu schreiben Top 100 Produkte.xls, die sich auf Zelle A2 unserer erstellten „intelligenten“ Tabelle bezieht Parameter.
Gehen wir dazu zurück zur Power Query-Abfrage und öffnen Sie sie erneut Erweiterter Editor Tab Bewertung (Ansicht – Erweiterter Editor). Anstelle eines Text-String-Pfads in Anführungszeichen „E:VerkaufsberichteTop 100 Produkte.xlsx“ Lassen Sie uns die folgende Struktur einführen:
Excel.CurrentWorkbook(){[Name=”Einstellungen”]}[Inhalt]0 {}[Pfad zu Quelldaten]
Mal sehen, woraus es besteht:
- Excel.CurrentWorkbook() ist eine Funktion der M-Sprache für den Zugriff auf den Inhalt der aktuellen Datei
- {[Name=”Einstellungen”]}[Inhalt] – Dies ist ein Verfeinerungsparameter für die vorherige Funktion, der angibt, dass wir den Inhalt der „intelligenten“ Tabelle abrufen möchten Parameter
- [Pfad zu Quelldaten] ist der Name der Spalte in der Tabelle Parameterworauf wir verweisen
- 0 {} ist die Zeilennummer in der Tabelle Parametervon denen wir Daten nehmen wollen. Die Kappe zählt nicht und die Nummerierung beginnt bei Null, nicht bei Eins.
Das ist eigentlich alles.
Es bleibt zu klicken Endziel und überprüfen Sie, wie unsere Anfrage funktioniert. Wenn Sie nun den gesamten Ordner mit beiden darin enthaltenen Dateien an einen anderen PC senden, bleibt die Anfrage funktionsfähig und ermittelt den Pfad zu den Daten automatisch.
- Was ist Power Query und warum wird es beim Arbeiten in Microsoft Excel benötigt?
- So importieren Sie ein Floating-Text-Snippet in Power Query
- Umgestalten einer XNUMXD-Kreuztabelle zu einer flachen Tabelle mit Power Query