Excel im betrieblichen Umfeld

Bewertungsmatrix (Produkte mit Eigenschaften und Ausprägungen) mit einem Produkt vergleichen Lern Video 1 Benutzereingabe (Produkt 1, Produkt 2 usw) als Arbeitsmappenabfrage abbilden Verweis: Werte einer Zelle als Arbeitsmappenabfrage abbilden siehe Power Query / benannte Bereiche  2 Tabelle Bewertungsmatrix als benutzerdefinierte Tabelle formatieren und anschließend     als Arbeitsmappenabfrage abbilden 3 neue, leere Arbeitsmappenabfrage erstellen und folgenden Language M Code kopieren: --- SCHNIPP --- let     Quelle = Vergleichsmatrix,     Gruppierte_Summe_Wert = Table.Group(Quelle, {"Attribut"}, {{"Summe_Produkt", each List.Sum([Wert]), type number}}),     Sortierung_Abst_Summe_Produkt = Table.Sort(Gruppierte_Summe_Wert,{{"Summe_Produkt", Order.Descending}}),     IndexSpalte_Rang = Table.AddIndexColumn(Sortierung_Abst_Summe_Produkt,"Rang",1 ),     Bewertung = Table.RenameColumns(IndexSpalte_Rang,{{"Attribut", "Produkt"}...

Lern Video   neue Abfrage erstellen, unten stehenden Language M Code einfügen und Abfrage in fxZufallszahl umbenennen --- SCHNIPP --- let fxZufallszahl = (Tabelle as table, Minimum as number, Maximum as number) => let     Index = Table.AddIndexColumn(Tabelle, "Index", 0, 1, Int64.Type),     ZufallFaktor = Table.AddColumn(Index, "ZufallFaktor", each List.Random(Table.RowCount(Index)){[Index]}),     ListMax = Table.AddColumn(ZufallFaktor, "Werte", each List.Max({Minimum .. Maximum})),     Zufallszahl = Table.AddColumn(ListMax, "Zufallszahl", each Number.RoundDown([ZufallFaktor]*[Werte])),     Aufraeumen = Table.RemoveColumns(Zufallszahl,{"Index", "ZufallFaktor", "Werte"}) in     Aufraeumen,     documentation = [     Documentation.Author ="Sven Galonska : http://svens-excel-welt.blogspot.com/",     Documentation.Name = "Zufallszahlen in einem angegebenen Bereich generieren",     Docu...

  Tabelle Buchstabe, Filter als Arbeitsmappenabfrage abbilden Tabelle Filter in eine Liste konvertieren Tabelle Filter, Schritt hinzufügen: = Table.ToList(Quelle) Tabelle Buchstabe, Schritt hinzufügen = Table.SelectRows(Quelle, each List.ContainsAny(tbl_Filter, {[Buchstabe]})) Beispiel Arbeitsmappe zum Download Lern Video

Ein Datenimport aus TXT / CSV Dateien kann zu unerwünschten Leerzeichen (am Anfang und / oder am Ende) in den Spaltenbeschriftungen führen. Mit folgender Language M Funktion kann dies nach dem Import / Datenquellenanbindung bereinigt werden. Hierzu eine neue, leere Arbeitsmappenabfrage erstellen, diese in fxPowerTrim umbenennen und folgenden Language M Code einfügen Quelle Ken Puls  https://www.excelguru.ca/blog/2015/10/08/clean-whitespace-in-powerquery/ --- SCHNIPP --- (text as text, optional char_to_trim as text) => let char = if char_to_trim = null then " " else char_to_trim, split = Text.Split(text, char), removeblanks = List.Select(split, each _ <> ""), result=Text.Combine(removeblanks) in  result --- SCHNAPP --- sowie einen weiteren Schritt einfügen = Table.TransformColumnNames(Quelle, fxPowerTrim) Lern Video Methode zur Bereinigung von Spaltenbeschriftungen (Data Cleansing) Mit folgender wiederverwen...

Ein Table Object ist ein strukturierter Bereich. Auf die einzelnen Bereiche / Sektionen kann über VBA Code referenziert werden.  Table Objects können mit Dynamik umgehen (Zeilen und / oder Spalten hinzufügen oder löschen). siehe auch Objekttypen Excel Power Query Shortcut zur Erstellung eines Table Objects = STRG - T Bereiche / Sektionen eines Table Objects Beispiele VBA Funktionen im Umfeld von Table Object VBA Code --- SCHNIPP Sub RemovePartsOfTable() 'Teile eines TableObjects löschen Dim tbl As ListObject Set tbl = ActiveSheet.ListObjects("Table1") 'Entferne 3te Spalte   tbl.ListColumns(3).Delete 'entferne 4te Datenzeile   tbl.ListRows(4).Delete 'entferne 3te bis 5te Datenzeile   tbl.Range.Rows("3:5").Delete 'entferne Gesamtsumme   tbl.TotalsRowRange.Delete End Sub Sub ResetTable() 'Entferne die Datenzeilen in einem TableObject Dim tbl As ListObject Set tbl = ActiveSheet.ListObjects(...

Anhand von Datensätzen einer fiktiven Strukturstückliste soll im Folgenden ein Weg mit Power Query aufgezeigt werden, wie man mittels der Index Funktion Vorgänger und Nachfolger ermitteln kann, um zB zwischen Endprodukten, Baugruppen und Komponenten unterscheiden zu können. Ausgangsstruktur Zielstruktur 1 benutzerdefinierte Spalte hinzufügen [Ebene_Zahl] := Value.FromText(Text.End([Stuecklisten_Ebene],1)) 2 Index Spalte hinzufügen 3 weitere benutzerdefinierte Spalten hinzufügen [Vorgaenger_Wert] : = try #"Hinzugefügter Index"[Ebene_Zahl]{[Index]-1} otherwise 0 [Nachfolger_Wert] : = try #"Hinzugefügter Index"[Ebene_Zahl]{[Index]+1} otherwise 0 [Materialklasse] : = if [Ebene_Zahl] = 1 then "Endprodukt" else  if [Ebene_Zahl] = [Vorgaenger_Wert] or [Ebene_Zahl] = [Nachfolger_Wert] then "Komponente" else  "Baugruppe" siehe auch hier

Aufgabe: laufenden Index innerhalb einer Gruppe (hier: [Bestellnummer]) erstellen.  Nach Gruppenwechsel  (hier: von Bestellnummer 500000000 zu 6000000000)  fängt der Index wieder bei 1 an 1 Gruppieren, neuer Spaltenname [Daten] 2 benutzerdefinierte Spalte [Gruppenindex] hinzufügen =Table.AddIndexColumn([Daten], "Index", 1, 1) 3 Spalte [Daten] entfernen 4 Spalte [Gruppenindex] erweitern Praxisbeispiel SAP ERP Tabelle MVER (Verbrauchsdaten) Language M Code --- SCHNIPP --- let     Quelle = Excel.CurrentWorkbook(){[Name="tbl_SAP_MVER"]}[Content],     #"Entpivotierte Spalten" = Table.UnpivotOtherColumns(Quelle, {"Material", "Jahr", "Periode", "Zeile"}, "Attribut", "Wert"),     Spalte_Verbrauch_hinzufuegen = Table.AddColumn(#"Entpivotierte Spalten", "Verbrauch", each "Verbrauch"),     Spalte_Attribut_entfer...

Ergebnis: Skalarer Wert Kalkulation eines skalaren Werts, im Beispiel die zeilenweise Summe der Werte [Wert1], [Wert2] Ergebnis: List Kalkulation einer Liste von Werten, im Beispiel eine Liste der Werte von [Wert1] bis [Wert2] Ergebnis: Table Kalkulation einer Tabelle, im Beispiel Tabelle mit Spalte [Column1] und den Werten [Wert1], [Wert2] #table Objekt Language M = #table({"Spalte1","Spalte2"},{{"erste Zeile Spalte 1","erste Zeile Spalte 2"},{"zweite Zeile Spalte 1","zweite Zeile Spalte 2"}}) = #table(type table [Spalte 1 = text, Spalte 2 = text],{{"erste Zeile Spalte 1","erste Zeile Spalte 2"},{"zweite Zeile Spalte 1","zweite Zeile Spalte 2"}}) Ergebnis: Record Kalkulation eines Records, im Beispiel ein Record mit den Feldern [Feld1], [Feld2] und den Werten aus Spalten [Wert1], [Wert2] Excel kennt 3 Objekttypen, Tabellenblätter (Sheet), Tabellen (T...

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()

Ausgangslage und Ziel: einem Lieferanten sind eine oder mehrere Warengruppe(n) zugeordnet. Im Falle des Vorhandenseins von mehreren Warengruppen je Lieferant sollen nicht mehrere Zeilen ausgegeben werden. Stattdessen sollen die Warengruppen getrennt durch ein Trennzeichen in einer Zelle abgebildet werden. Language M Code in neue Abfrage einfügen (erweiterter Editor): ---SCHNIPP -- let Quelle = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content], fCombine = Combiner.CombineTextByDelimiter(", "), GruppierteZeilenWarengruppe = Table.Group(   Quelle,   {"Lieferant"},   {{"Warengruppen", each fCombine([Warengruppe]), type text}} ) in     GruppierteZeilenWarengruppe --- SCHNAPP -- weiteres Beispiel ---SCHNIPP --- let     Quelle = Table.NestedJoin(tbl_Lizenz,{"Rolle"},tbl_Lizenztyp,{"Rolle"},"tbl_Lizenztyp",JoinKind.LeftOuter),     fCombine = Combiner.CombineTextByDelimiter(", ...

Power Query Cheat Sheet  , CSV.Document Parameter und deren Bedeutung Logik IF ... THEN ... ELSE Datums-Funktionen Date.FromText(text) gibt Datumswert aus Datumstext zurück Date.Month(date) gibt den Monat aus einem seriellen Datum zurück DateTime.LocalNow() gibt das heutige Datum und Uhrzeit zurück Date.ToRecord(date) gibt ein Record Objekt zurück mit Jahr,Monat,Tag Date.WeekOfYear(date) gibt die Kalenderwoche des Jahres zurück Date.Year(date) gibt das Jahr aus einem seriellen Datum zurück Duration Funktionen Differenz aus zwei DateTime Werten als Argument (duration value) übergeben Bsp.: 25.02.2018 00:00:00 - 28.01.2018 01:22:25 = 27.22:37:35 Duration.Days() gibt die Tage aus einem duration-Wert zurück Duration.From() Duration.Hours() Duration.Minutes() Duration.Seconds() Fehler-Behandlung try <expressions to try> otherwise <default if error> Beispiel: [Column] = "12.12.2016", "Text" try Number.From([C...

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...

Wer regelmäßig mit Power Pivot und mehrdimensionalen Modellen arbeitet kennt die Situation: Man benötigt eine lückenlose Datumstabelle für eine Datumsdimension, doch woher nehmen ? Mit Power Query kann man diese sehr einfach erstellen. Reiter Power Query -> aus anderen Datenquellen -> leere Abfrage erstellen Im Abfrage Editor, Reiter Start (oder Ansicht), in "Erweiterter Editor" wechseln Hier folgenden von Matt Allington entwickelten Quellcode einfügen: let Source = List.Dates, InvokedSource = Source(#date(2010, 1, 1), Duration.Days(DateTime.Date(DateTime.FixedLocalNow())-#date(2010,1,1)), #duration(1, 0, 0, 0)), TableFromList = Table.FromList(InvokedSource, Splitter.SplitByNothing(), null, null, ExtraValues.Error), RenamedColumns = Table.RenameColumns(TableFromList,{{"Column1", "Date"}}), InsertedCustom = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date])), InsertedCustom1 = Table.AddColumn(InsertedCus...