Power Query, Parameter Tabelle, fnGetParameter

Anbei ein Beispiel, wie man über einen Parameter die Anbindung einer Access Datenbank
parametrisieren kann. Dies ist immer dann sinnvoll, wenn die Dateien von einem System auf ein anderes übertragen werden sollen, ohne dass die relevante Code Zeile in der Abfrage manuell angepasst werden soll.

Wenn sowohl die Excel Datei mit Power Query Abfragen als auch die Access Datenbank in ein
und demselben Ordner liegen funktioniert die Anbindung mittels folgender Methode:

(Dieses Beispiel kann analog auch für andere Datenquellentypen angepasst werden.)


1 Neue Abfrage erstellen, folgenden Code einfügen (vorher alles entfernen)

---SCHNIPP ---

(ParameterName as text) =>
let
ParamSource = Excel.CurrentWorkbook(){[Name="Parameter"]}[Content],
ParamRow = Table.SelectRows(ParamSource, each ([Parameter] = ParameterName)),
Value=
if Table.IsEmpty(ParamRow)=true
then null
else Record.Field(ParamRow{0},"Value")
in
Value

---SCHNAPP---

2 Funktion in fnGetParameter umbenennen

3 Tabelle Parameter erstellen, 2 Spalten [Parameter], [Value]


Formel Spalte [Value] := 

Ordnerpfad
=LINKS(ZELLE("dateiname";B6);FINDEN("[";ZELLE("dateiname";B6);1)-1)

Dateipfad bis zum Dateinamen ohne [eckige Klammern]
=WECHSELN(LINKS(ZELLE("Dateiname";$A$1);-1+FINDEN("]";ZELLE("Dateiname";$A$1)));"[";"")

Tabelle über 
Entwurf -> Tabellenname in
Parameter
umbenennen

Tabelle als Abfrage in Power Query abbilden (Reiter Daten -> aus Tabelle)

Exkurs
alternativ kann eine Tabelle direkt über das #table Objekt in einer Arbeitsmappenabfrage
definiert / erstellt werden

Beispiel:
= #table({"Parameter","Value"},{{"Parameter1","Value1"},{"Parameter2","Value2"}})


4 Neue Abfrage erstellen und Code anpassen


Datenquellen (Typen)

Datenquelle Access Datenbank

--- SCHNIPP ---

let
SolutionPath = fnGetParameter("File Path Access"),    
Quelle = Access.Database(File.Contents(SolutionPath & "Name_AccessDB_einsetzen.accdb"), [CreateNavigationProperties=true]),
    _Tabelle1 = Quelle{[Schema="",Item="Tabelle1"]}[Data]
in
    _Tabelle1

--- SCHNAPP ---

Datenquelle Excel Datei

Um zB Excel Dateien anzubinden kann ein weiterer Parameter eingetragen werden


siehe auch Exkurs: Kind Eigenschaft (3 Objekttypen := Sheet, Table, DefinedName)

--- SCHNIPP

let
    SolutionPath = fnGetParameter("File Path Excel"),    
    Quelle = Excel.Workbook(File.Contents(SolutionPath & "Name_Exceldatei_einsetzen.xlsx"), null, true),
    Navigation = Quelle{[Item="Tabelle1",Kind="Sheet"]}[Data],
    #"Höher gestufte Header" = Table.PromoteHeaders(Navigation, [PromoteAllScalars=true])
in
    #"Höher gestufte Header"

--- SCHNAPP

Datenquelle Text / CSV Datei

Exkurs: Parameter Csv.Document Language M Funktion

Eine Text Datei kann folgendermaßen angebunden werden
(im gewählten Beispiel Tabstop getrennte Felder, 4 Quell Spalten)

--- SCHNIPP

let
    SolutionPath = fnGetParameter("File Path Text"),
    Quelle = Csv.Document(File.Contents(SolutionPath & "Name_Text_Datei_einsetzen.txt"),[Delimiter="#(tab)", Columns=4, Encoding=1252, QuoteStyle=QuoteStyle.None])
in
    Quelle

--- SCHNAPP

Datenquelle Text Datei

 falls es notwendig ist, einen Import als Text-Datei zu erzwingen (eine Spalte, keine Aufteilung auf Spalten anhand Trennzeichen), kann alternativ folgende benutzerdefinierte Funktion verwendet werden
(in neue Arbeitsmappenabfrage kopieren, Name [fxRawFileContents])

 --- SCHNIPP

/* Hole Rohdaten Zeile für Zeile, ohne dass Power Query diese interpretiert */
/*Ergebnis ist eine Spalte ohne Aufteilung der Daten auf Spalten */
let
fxRawFileContents = (fullpath as text) as table =>
let
Value = Table.FromList(Lines.FromBinary(File.Contents(fullpath)),Splitter.SplitByNothing())
in
Value
in
fxRawFileContents

--- SCHNAPP

für Details siehe folgenden Blog Beitrag
Datenquelle Sharepoint

Anbindung Excel Datei bereitgestellt in Sharepoint Bibliothek (über URL) siehe hier


#Table Objekt

 wie man alternativ eine gekapselte (Parameter) Tabelle (#table) erstellen kann siehe hier

weiterführende Informationen bei Ken Puls
https://www.excelguru.ca/blog/2014/11/26/building-a-parameter-table-for-power-query/

Lern Video




Kommentare

  1. bisdsaf19. September 2019 um 07:18

    Hallo Böns,

    gibt es diese Möglichkeit auch bei einem ganzen Ordner (mit CSV oder excel-Dateien).

    Bei meinem Kollegen liegt das zum Teil auf anderen Ressourcen und anderen gemappten Laufwerken, so dass auch hier eine Anpassung über Paramter notwendig wäre.

    AntwortenLöschen

Kommentar veröffentlichen

Beliebte Posts aus diesem Blog

Dropdown Liste mit Mehrfachauswahl

Bild
Standardmäßig kann in einem Dropdownfeld nur ein Eintrag ausgewählt werden (Daten -> Datentools -> Datenüberprüfung). Ein dynamisches Dropdownfeld kann folgendermaßen realisiert werden abhängige, dynamische Dropdownfelder können folgendermaßen in Excel  realisiert werden weiterführende Informationen siehe hier Um eine Mehrfachauswahl zu realisieren, kann der folgende VBA-Code im betreffenden Arbeitsblatt verwendet werden (rechte Maustaste auf Arbeitsblatt -> Code einfügen). --- SCHNIPP -- Private Sub Worksheet_Change(ByVal Target As Range) '** Mehrfachauswahl über DropDown-Liste (Gültigkeitsprüfung) '** Einfügen im Code-Container des betreffenden Arbeitsblattes '** Dimensionierung der Variablen Dim rngDV As Range Dim wert_old As String Dim wertnew As String '** Errorhandling On Error GoTo Errorhandling '** Mehrfachauswahl im definierten Bereich (Bsp. B4:B14) durchführen If Not Application.Intersect(Target, Range("B4:B14&
Mehr anzeigen

Vergleich mit 2 Bedingungen, INDEX(), VERGLEICH()

Wenn man 2 Tabellen mit jeweils 2 gleichen Spalten hat (zB Nachnahme, Vorname) und eine 3te Spalte (zB Telefon) von einer Tabelle in eine andere übernehmen will, stellt die Funktion SVERWEIS() keinen adäquaten Lösungsansatz mehr dar. Mit einer Kombination der Funktionen INDEX() und VERGLEICH() ist es dennoch möglich, diese Aufgabe mit Excel Formeln (Matrixformel) zu lösen. =INDEX(Tabelle1!$A$2:$C$5;VERGLEICH(A2&B2;Tabelle1!$A$2:$A$5&Tabelle1!$B$2:$B$5;0);3) wobei gilt: Tabelle 1 Spalte A zB Nachname Tabelle 1 Spalte B zB Vorname Tabelle 1 Spalte C zB Telefon (Wert welcher in Tabelle 2 bei inhaltlicher Gleichheit Spalten A, B übernommen werden soll) Formel in Tabelle 2 Spalte C aufbauen und mit STRG-SHIFT-RETURN (Matrixformel) abschließen und runterkopieren. Ein weiteres Beispiel zu Matrixformeln siehe folgenden Blog Beitrag seit Office 365 -> Microsoft 365 gibt es eine weitere, neue Alternative, Funktion XVERWEIS()
Mehr anzeigen

nützliche Text Funktionen in Power Query

Bild
Im Folgenden werden ein paar in der Praxis nützliche Text Funktionen in Power Query aufgelistet. Im Voraus soll darauf hingewiesen werden, dass es 2 wesentliche Unterschiede zwischen Excel und Power Query Formeln / Funktionen gibt: case sensitivity Excel Formel unterscheiden nicht zwischen Groß- und Kleinschreibung, Power Query Formeln indes schon. Wenn eine Power Query Signatur Text.Range vorgibt, dann wird TEXT.RANGE oder text.range nicht funktionieren (case sensitive). Basis 1 versus Basis 0 Excel Formeln / Funktionen beziehen sich immer auf die Basis 1, d.h. man fängt mit 1 an zu zählen. Auf der anderen Seite startet das Zählen in einer Power Query Funktion immer mit 0, nicht 1. Vergleich Excel Text mit Power Query Funktionen Text.Contains(Text,Suchstring) gibt TRUE zurück, wenn <Suchstring> in <Text> beinhaltet ist, andernfalls FALSE z.B. Text.Contains("Power Query","Query") Rückgabewert = TRUE Text.Remove([Column],{"
Mehr anzeigen