Excel im betrieblichen Umfeld

Fehler (errors) können in Power Query mit TRY (Ergebnis Record ) behandelt werden. Achtung, NaN, Infinity und -Infinity sind keine Fehler!  Es handelt sich hierbei um numerische Werte in Power Query Number.PositiveInfinity (positive Zahl wird durch 0 geteilt)  Number.NegativeInfinity (negative Zahl wird durch 0 geteilt) NaN (0 wird durch 0 geteilt) null (Tip null immer durch eine Zahl zB 0 ersetzen) mit folgender benutzerdefinierter Funktion können in Power Query Ergebnisse einer Division (durch 0) geprüft werden --- SCHNIPP --- let     Source = (input as any) =>          let             Null=(if input=null then false else true),             Pinfinity=(if input=Number.PositiveInfinity then false else true),             Ninfinity=(if input=Number.NegativeInfinity then false else true),             Nan=(if Number.IsNaN(inp...

 Lern Video 1 Tabelle [Text], [Find_Replace] als Power Query Abfrage abbilden 2 benutzerdefinierte Spalte hinzufügen (Abfrage Text) = List.Accumulate(     List.Numbers(0, Table.RowCount(Find_Replace)),      [Text],      (state, current) =>          Text.Replace(state,              Find_Replace[Find]{current},             Find_Replace[Replace]{current})) Quelle https://chandoo.org/wp/multiple-find-replace-list-accumulate/ massenhaftes Suchen und Entfernen von Textteilen aus Text --- SCHNIPP --- fxTextBereinigen (input as text, removeWords as list) as text => let     inputText = input,     wordsToRemove = removeWords,     removedText = List.Accumulate(wordsToRemove, inputText, (text, word) => Text.Replace(text, word, "")) in     removedText --- SCHNAPP -- zB Liste removeWords = {"Ffm","Ffm."} neue benutzerde...

  Language M Code, neue leere Abfrage erstellen und Code reinkopieren --- SCHNIPP --- let     Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],     Custom1 = Table.FromList(Table.ColumnNames(Source)),     Text = Table.AddColumn(Custom1, "Text", each "["&[Column1]&"] <> null" ),     Expression = Text.Combine(Text[Text], " and "),     Evaluate = Table.SelectRows(Source, each Expression.Evaluate(Expression, [_ = _] )) in     Evaluate --- SCHNAPP --- Quelle: https://www.sqlxpert.de/in-power-bi-und-power-query-zeilen-ohne-leere-felder-mit-expression-evaluate-auswaehlen/ Spalten mit leere Werten nicht selektieren  --- SCHNIPP --- fxNonNullColumns (tblInputTable as table) => let TabelleOhneNullSpalten = Table.SelectColumns(tblInputTable, List.Select(Table.ColumnNames(tblInputTable), each List.NonNullCount(Table.ToColumns(Table.SelectColumns(tblInputTable, _)){0})>0)) in TabelleOhneNullSpalten ...

 Anbei ein alternativer Ansatz zu einem Table Join (Tabellen über Schlüsselfeld verknüpfen), um einen Wert in einer anderen Datenquelle / Datei nachzuschlagen Ausgangssituation 2 Text- Dateien Ziel: neue Abfrage erstellen, Quellcode einfügen und Abfrage umbenennen in fxLookup ( Dateipfad anpassen ) --- SCHNIPP --- let fxLookup = (input) => let     Source = Csv.Document(File.Contents(" C:\Users\socia\Desktop\temp_vlookup\StateAbbreviations.txt "),[Delimiter=",", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None]),     #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),     #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type"),     Record = Table.First(Table.SelectRows(#"Promoted Headers", each ([State Name] = input))),     Result = Record.Field(Record,"State") in     Result in     fxLookup --- SCHNAPP --- neue Abfrage erstellen, Quellco...

Falls sich die Spaltenbeschriftungen in der Datenquelle ändern, kann man diesem Umstand mit Power Query begegnen. Es ist allerdings zu beachten, dass sich Anzahl und Reihenfolgeposition der Spaltenbeschriftungen in der Datenquelle nicht ändern dürfen. Ansonsten besteht die Gefahr, dass mit falschen Werten weitergearbeitet wird. 1 Table.DemoteHeaders() 2 Spalten umbenennen 3 erste Zeile entfernen alternative Methode alternative Methode 2  List.Zip()

Standardmäßig kann beim Pivotieren mit Power Query nur eine Wertspalte berücksichtigt werden. Wie man diese Limitierung umgehen kann soll folgendes Beispiel aufzeigen. siehe auch Power Query entpivotieren Quelltabelle wird mittels Power Query überführt in  Zieltabelle  Hierzu werden die Spalten [Mengen], [Preis] markiert und entpivotiert: Im nächsten Schritt wird eine benutzerdefinierte Spalte für die neue Spaltenüberschrift =[Bezeichnung] & "." & [Attribut] erstellt. Dann können die Spalten [Bezeichnung] und [Attribut] entfernt sowie eine Pivotierung auf Basis der neuen Spalte [ColumnHeader] durchgeführt werden siehe auch Daten entpivotieren

mit Power Query ist es möglich, 2 Tabellen über ein Schlüsselfeld miteinander zu verknüpfen, um eine Tabelle mit Felder der verknüpften Tabelle anzureichern (Tabellen Join). In Excel wird diese Aufgabenstellung typischerweise mit der Formel SVERWEIS gelöst. siehe auch Performance Tip Table.AddKey / Remove.Duplicates Lern Video anhand Praxis Beispiel SAP Tabellen MARA, MAKT

Mit Power Query können auch Webinhalte angebunden werden. Voraussetzung ist, dass eine HTML Tabelle (Table Objekt) auf der Website vorhanden ist. (statisches HTML) Daten -> Aus anderen Quellen -> Aus dem Web

Wie man ein Geschäftsjahr mit Power Pivot DAX aus einem Datum ableiten kann, ist in folgendem Artikel beschrieben. Ein Geschäftsjahr kann bei Bedarf bereits schon während des ETL-Prozesses mit Power Query abgeleitet werden, um dieses dann dem Power Pivot Modell zur Verfügung zu stellen. Im gewählten Beispiel beginnt das Geschäftsjahr am 1.4. und endet im folgenden Kalenderjahr am 31.3: Ansatz 1: 3 neue benutzerdefinierte Spalten anlegen (Referenzspalte "Date") GJ1 = if Date.Month([Date]) >= 4 and Date.Month([Date]) <= 12 then Text.From(Date.Year([Date])) else Text.From(Date.Year([Date]) - 1) GJ2 = if Date.Month([Date]) >= 4 and Date.Month([Date]) <= 12 then Text.From(Date.Year([Date])+1) else Text.From(Date.Year([Date])) GJ = [GJ1]&"/"&[GJ2] Ansatz 2: 1 neue benutzerdefinierte Spalte anlegen (Referenzspalte "Date") GJ = if Date.Month([Date])=1 or Date.Month([Date])=2 or Date.Month([Date])=3 then Number.ToText(Date....

Wichtig vor der Nutzung der IF Funktion (und aller anderen Power Query Funktionen) ist, dass sie case sensitiv sind (siehe hierzu auch Text Funktionen  in Power Query). Im Folgenden soll anhand einer if ... then ... else Konstruktion die Funktionsweise anhand einem Praxisbeispiel erläutert werden. Zur Verdeutlichung der verwendeten Text Funktionen siehe Artikel Text Funktionen in Power Query

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],{...

Lern Video Mit Excel Power Query Funktion Text.SplitAny kann man sehr elegant ein Teilwort aus einem Text bis zum ersten Auftauchen eines oder mehrerer Trennzeichen (Trunkation) durchführen. Dies kann v.a. dann sehr sinnvoll sein, wenn man ein weiteres Filter Merkmal in einer Tabelle / Modell benötigt (Datenveredelung). Nachdem die Tabelle in Power Query geladen wurde, eine neue benutzerdefinierte Spalte (Werte) erstellen und folgende benutzerdefinierte Spaltenformel hinterlegen: Text.SplitAny([Bezeichnung],"_;,-/,'""' ") Das Ergebnis ist ein List-Objekt. Wenn nur das erste Teilwort benötigt wird, eine weitere benutzerdefinierte Spalte einfügen, in welcher auf das erste Teilwort in dem List-Objekt verwiesen wird: verbesserte Text.SplitAny Funktion (benutzerdefinierte Funktion) neue Abfrage erstellen, Language M Code kopieren und Abfrage in fxTextSplitAnyNew umbenennen Diese verbesserte Funktion erlaubt zusätzlich die Benu...

Um mehrere Tabellen innerhalb einer Excelmappe in eine Tabelle zu integrieren, folgende Schritte durchführen: 1 Tabellen als Tabelle formatieren (STRG - T), Namen vergeben (im Beispiel Namen Tabelle1, Tabelle2, Tabelle3 2 Tabellen in Power Query laden (Excel-Daten, Von Tabelle) Power Query Editor über "Schließen Daten in" verlassen, "nur Verbindung" auswählen 2 Leere Abfrage mit Power Query erstellen (Option "aus anderen Quellen, Leere Abfrage") 3 Folgenden Language M Code über "Ansicht -> erweiterter Editor" in leere Abfrage einfügen und ggfs an die verwendeten Namen anpassen: let     Quelle = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],     Anfügen = Table.Combine({Quelle,Tabelle2}),     Anfügen1 = Table.Combine({Anfügen,Tabelle3}) in     Anfügen1

Hin und wieder müssen verteilte Dateien (zB zur Unterstützung eines Planungsprozess), deren Struktur und Inhalt gleich, Spaltenbeschriftungen aber ungleich sein können geladen (und ggfs zusammengeführt) werden. siehe auch strukturell gleiche Dateien in einem Ordner zusammenführen Beispiel: Die gelb markierten Spaltenbeschriftungen sind ungleich, die Spalte indes enthält in beiden Fällen Anzahl Mitarbeiter Wenn man in der Power Query Anfrage (Prozess Schritt "umbenannte Spalte") lediglich den Spalten Namen in "Anzahl" ändert, wird man auf folgenden Fehler laufen, falls sich die unterschiedlichen Tabellen in der Quellfeld Spaltenbezeichnung (01.01.2016, 31.11.2015) unterscheiden Wenn man sich den Code für die Umbenennung der Spaltenbeschriftung in Anzahl genauer anschaut kann man erkennen, woran das liegt. Table.RenameColumns(Navigation,{{"31.01.2016", "Anzahl"}}) Die Funktion benutzt die Tabelle Navigation, sucht na...

Mit Excel Power Query kann man mehrere strukturell gleiche Excel Dateien, abgespeichert in einem Ordner, auslesen und in eine Tabelle integrieren. Das Ergebnis entspricht in SQL einem UNION Statement, d.h. die Daten fließen ineinander in eine, integrierte Tabelle, Datensatz für Datensatz wird angefügt (coalesce, append). Hierzu verwendet man eine Funktion, die eigentlich zum Auslesen von Metadaten eines Ordners gedacht ist -> Power Query -> aus Datei -> aus Ordner, Ordnerpfad auswählen Im Abfrageeditor eine neue Spalte hinzufügen (Name ExcelDateiinhalt) und folgende (benutzerdefinierte Spalten) Formel eingeben: Excel.Workbook([Content]) Feld "ExcelDateiinhalt" erweitern, Element "Data" markieren Da die einheitlichen Spaltenüberschriften in der neuen, integrativen Tabelle nur einmal benötigt werden, definiert man noch eine weitere, neue Spalte (Überschriften), welche diese Anforderung umsetzt. Die hierfür notwendige benutzerdefi...

Um Daten mit Pivot weiterbearbeiten oder mit Exel Power Pivot in ein Modell zur Kombination mit anderen Daten überführen zu können, bedarf es einer bestimmten Datenstruktur. Oftmals liegen die Ausgangsdaten aber bereits in einer pivotierten Struktur vor (siehe Beispiel) Für eine weitere  Bearbeitung wird für jede Materialgruppe / Datum Kombination eine Datenzeile benötigt, in diesem Beispiel also 12 (3 Materialgruppen x 4 Datumswerte) Datenzeilen. Mit Power Query ist dies sehr einfach umzusetzen: Tabelle in Abfrage-Editor laden (reiter Power Query -> von Tabelle) Einzelne Datumswerte mit CTRL markieren, rechte Maustaste -> Spalten entpivotieren über "Schließen & Laden" wird die entpivotierte Tabelle in ein Excel Tabellenblatt übernommen und kann weiter mit Pivottabelle oder Power Pivot als Datenquelle (Modell) verarbeitet werden. siehe Power Query Daten pivotieren mehrere Wertspalten