Power Query, Spaltennamen von Quelldaten in benutzerfreundliche Spaltennamen umbenennen, List.Zip





Phase 1 - Vorbereitung Quell - Ziel Felder Struktur (Spaltennamen)


1.1 Datenquelle über Excel Power Query anbinden



zB SAP Export Daten, Text Datei, Tab getrennt

angewendete Schritte := [Quelle]

= Csv.Document(File.Contents("D:\Projekte\SAP\Daten\SAP_Export.txt"),[Delimiter=" ", Columns=6, Encoding=1252, QuoteStyle=QuoteStyle.None])


1.2 Liste (Listobjekt) mit Spalten der Datenquellenstruktur erstellen



angewendete Schritte := [Liste_Quellfelder]

= Table.ColumnNames(Quelle)


Exkurs: Anzahl Spalten der Quelldaten ermitteln

 = List.Count(Table.ColumnNames(Quelle))


1.3 in Tabelle konvertieren, Spalte in [Quell_Feld] umbenennen






Language M Code

--- SCHNIPP ---

let
    Quelle = Csv.Document(File.Contents("D:\Projekte\SAP\Daten\SAP_Export.txt"),[Delimiter=" ", Columns=6, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    Liste_Quell_Felder = Table.ColumnNames(Quelle),
    #"In Tabelle konvertiert" = Table.FromList(Liste_Quell_Felder, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Umbenannte Spalten" = Table.RenameColumns(#"In Tabelle konvertiert",{{"Column1", "Quell_Feld"}})
in
    #"Umbenannte Spalten"

--- SCHNAPP ---

1.4 Ergebnis in Excel Tabelle laden und um Feld [Ziel_Feld] (=benutzerfreundliche Spaltennamen) ergänzen



1.5 Liste mit [Quell_Feld], [Ziel_Feld] als Arbeitsmappenabfrage abbilden


(im Beispiel Abfragename [tbl_Tabellenspalten_umbenennen])




Phase 2 - Mapping Quell -> Zielfeld 


2.1 neue Abfrage erstellen und Bearbeitungsschritte einfügen (angewendete Schritte)




zB SAP Export Daten, Text Datei, Tab getrennt

angewendete Schritte := [Quelle]

= Csv.Document(File.Contents("D:\Projekte\SAP\Daten\SAP_Export.txt"),[Delimiter=" ", Columns=6, Encoding=1252, QuoteStyle=QuoteStyle.None])


angewendete Schritte := [Quelle_Liste_Feldname_buffered]

= List.Buffer(Table.ColumnNames(Quelle))


angewendete Schritte := [Tabellenfelder_umbenennen]

= Table.RenameColumns( Quelle, List.Zip( { Quelle_Liste_Feldname_buffered, tbl_Tabellenspalten_umbenennen[Ziel_Feld] } ) )


Language M Code

--- SCHNIPP ---

let
    Quelle = Csv.Document(File.Contents("D:\Projekte\SAP\Daten\SAP_Export.txt"),[Delimiter=" ", Columns=6, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    Quelle_Liste_Feldname_buffered = List.Buffer(Table.ColumnNames(Quelle)),
    Tabellenfelder_umbenennen = Table.RenameColumns( Quelle, List.Zip( { Quelle_Liste_Feldname_buffered, tbl_Tabellenspalten_umbenennen[Ziel_Feld] } ) )
in
    Tabellenfelder_umbenennen

--- SCHNAPP ---

Tip Fehlermeldung /-suche
deutet darauf hin, dass 

die Anzahl der Felder in der Quelldatei [Quell_Feld] von der Anzahl der erfassten Felder in der Tabelle abweichen (s. ab Punkt 1.2)

doppelte Feld Namen (Spaltennamen) in Quelle- und / oder Ziel vorhanden sind


siehe auch Microsoft Power BI Community

--- SCHNIPP benutzerdefinierte Funktion fnRenameColumns

let
    fnRenameColumns = (tableName as table) =>
let
    Source = tableName,
    ColumnNames = Table.ColumnNames(Source),
    Transform = List.Transform(ColumnNames, each {_, Text.Upper(Text.Replace(_,"_"," "))}),
    RenameColumns = Table.RenameColumns(tableName,Transform)
in
    RenameColumns
in 
    fnRenameColumns

--- SCHNAPP

Technik, um
Leer- und/oder Steuerzeichen aus Spaltenbeschriftungen 
zu entfernen

siehe auch benutzerdefinierte Funktion fxPowerTrim

 1 neue Abfrage -> leere Abfrage erstellen, Name [AlteSpaltenbeschriftungen]

Language M Code einfügen (Ansicht -> erweiterter Editor)

--- SCHNIPP

let
    Quelle = Excel.CurrentWorkbook(){[Name="tbl_Leerzeichen_Spaltenbeschriftungen"]}[Content],
    List_ColumnNames = Table.ColumnNames(Quelle)
in
    List_ColumnNames

--- SCHNAPP

2 neue Abfrage -> leere Abfrage erstellen, Name [NeueSpaltenbeschriftungen]

Language M Code einfügen (Ansicht -> erweiterter Editor)

---SCHNIPP

let
    Quelle = Excel.CurrentWorkbook(){[Name="tbl_Leerzeichen_Spaltenbeschriftungen"]}[Content],
    List_ColumnNames = Table.ColumnNames(Quelle),
    Text_Trim = List.Transform(List_ColumnNames, each Text.Trim( _ )),
    Text_Clean = List.Transform(Text_Trim, each Text.Clean( _ ))
in
    Text_Clean

--- SCHNAPP


3 neue Abfrage -> leere Abfrage erstellen, Name [Zipped_AltNeu_Spaltenbeschriftungen]

Language M Code einfügen (Ansicht -> erweiterter Editor)

--- SCHNIPP

let
    Quelle = List.Zip({AlteSpaltenbeschriftungen, NeueSpaltenbeschriftungen})
in
    Quelle

--- SCHNAPP

4 in Arbeitsmappenabfrage der relevanten Tabelle folgenden Schritt einfügen

 = Table.RenameColumns(Quelle, Zipped_AltNeu_Spaltenbeschriftungen)




Anwendung List.Zip in Kurzform am Beispiel einer Tabelle mit 3 Spalten
mit den Funktionen
Table.RenameColumns(), List.Zip(), Table.ColumnNames()


= Table.RenameColumns(Quelle, List.Zip({Table.ColumnNames(Quelle),{"WG1","WG2","WG3"}}))


alternativer Ansatz (ohne List.Lip, Table.RenameColumns(), Liste von Listen) siehe Beispielmappe

siehe auch Daten aus dem Power Pivot Datenmodell holen

siehe auch hier

Kommentare

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