Excel im betrieblichen Umfeld

Beispiel als Abfrage in Power Query abbilden neue benutzerdefinierte Spalte hinzufügen: Record.FieldOrDefault(    [     // Datensatz mit Zuordnung           DE  = "Deutschland",  // Abkürzungen Land         ES  = "Spanien",         FR =  "Frankreich",         BE  = "Belgien",         IT =  "Italien"   ],    [Abkürzung], // Nachschlagewert in Datensatz         "nicht vorhanden" // default Wert wenn nicht gefunden )   Ergebnis:

Voraussetzungen: Table.DemoteHeader (Überschriften als erste Zeile verwenden) erste Spalte (Column1) = Primärschlüssel (Tabelle 1), Fremdschlüssel (Tabelle 2) --- SCHNIPP --- (tblSecundary as table, tblPrimary as table) as table => let     Source = Table.NestedJoin(tblSecundary,{"Column1"},tblPrimary,{"Column1"},"Joined",JoinKind.LeftOuter),     ColToExp = List.Skip(Table.ColumnNames(tblPrimary),1),     Expand = Table.ExpandTableColumn(Source, "Joined", ColToExp, {ColToExp{0} & Number.ToText(Table.ColumnCount(Source))}) in     Expand --- SCHNAPP --- Quelle https://chandoo.org/forum/threads/useful-powerquery-tricks-chihiros-notes.35658/

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

Lern Video folgendes Query (Abfrage) mit vorgegebenen Filtern auf Merkmale erstellen, downloaden und entzippen: Quelle RKI SurfStat @RKI 2.0 https://survstat.rki.de/Content/Query/Main.aspx Excel Arbeitsmappe downladen und öffnen Download Excel Beispiel Arbeitsmappe Reiter Daten -> Abfragen und Verbindungen Abfrage Daten, step Quelle -> Dateipfad auf Datenquelle anpassen, Schließen & Laden (Wechsel auf Excel Oberfläche) Ergebnis (heat map)

  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

  1 Tabelle in eine Liste von Listen aufteilen wobei jede Liste alle Felder einer Zeile enthält =Table.ToRows(Source) 2 Liste in einer Liste kombinieren, bedeutet, man hat alle Felder der Tabelle in einer großen Liste =List.Combine(Table.ToRows(Source)) 3 Überprüfen, ob diese Liste den gesuchten Text beinhaltet =List.Contains(List.Combine(Table.ToRows(Source)), "Zahnrad") Anwendung (Praxis Beispiel) Suche alle Datensätze (Lieferanten), bei welchen in irgendeiner Spalte (der Tabelle) die Warengruppe Zahnrad beinhaltet ist Ausgangstabelle Tabelle nach [Lieferant] gruppieren und neue benutzerdefinierte Spalte( [check]) hinzufügen = List.Contains(List.Combine(Table.ToRows([Warengruppen])), "Zahnrad") Spalte [check] auf Element TRUE filtern Beispiel Arbeitsmappe Quelle  https://www.thebiccountant.com/ https://www.thebiccountant.com/2019/04/30/table-containsanywhere-function/#more-4044 Lern Video

  neue Abfrage erstellen, Language M Code einfügen, Abfrage umbenennen in fxTablePreviousRow --- SCHNIPP --- (MyTable as table, MyColumnName as text) => let     Source = MyTable,     ShiftedList = {null} &  List.RemoveLastN(Table.Column(Source, MyColumnName),1),     Custom1 = Table.ToColumns(Source) & {ShiftedList},     Custom2 = Table.FromColumns(Custom1, Table.ColumnNames(Source) & {"PreviousRowColumnValue"}) in     Custom2 --- SCHNAPP --- Quelle Imke Feldmann  https://www.thebiccountant.com/ https://www.thebiccountant.com/2018/07/12/fast-and-easy-way-to-reference-previous-or-next-rows-in-power-query-or-power-bi/ siehe Beispielmappe (download) Lern Video

Als Datenquelle fungiert Website  https://www.arbeitstage.org/  neue, leere Abfrage erstellen, Language M Code einfügen und in fnGetDeutscheFeiertageJeBundesland umbenennen: --- SCHNIPP Power Query Language M Code --- let fnAlleFeiertage = (Kalenderjahr as number) as table =>    let      //Liste aller Bundesländer für den Aufbau der URL bei arbeitstage.org      List_Bundeslaender = {          "baden-wuerttemberg",           "bayern", "berlin",           "brandenburg",           "bremen",           "hamburg",           "hessen",           "mecklenburg-vorpommern",           "niedersachsen",           "nordrhein-westfalen",           "rheinland-pf...

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

Mit folgender benutzerdefinierten Language M Funktion können zwei Worte bezüglich ihrer Ähnlichkeit überprüft werden. Dabei wird ein Faktor als Maß für die Ähnlichkeit gebildet. Ein Faktor >= 0.75 kann dabei als hinreichend genaues Maß für die Ähnlichkeit zweier Worte angenommen werden. Selbstverständlich entbindet dieses Ähnlichkeitsmaß nicht von der fachlich / inhaltlichen Prüfung. Dennoch hilft es, die verfügbare Datenqualität zu kategorisieren. ---- SCHNIPP --- //Faktor als Indikator für Ähnlichkeit zweier Wörter (words1 as text, words2 as text) as number => let       Zaehler = 2 * List.Count(List.Intersect({Text.ToList(Text.Clean(Text.Trim(Text.Lower(words1)))), Text.ToList(Text.Clean(Text.Trim(Text.Lower(words2))))})),     Fuzzy = Zaehler / (Text.Length(Text.Clean(Text.Trim(words1))) + Text.Length(Text.Clean(Text.Trim(words2)))) in     Fuzzy --- SCHNAPP --- siehe auch Umlaute

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

Mit folgender benutzerdefinierten Funktion kann die Anzahl Zeichen in einem Text (string) ermittelt werden: fxAnzahlZeichen ---- SCHNIPP (string as text, zeichen as text) => let   ListObjectZeichen=Text.ToList(string),   Loop=List.Accumulate(ListObjectZeichen,                        0,                        (state, current) => if current = zeichen                                            then state + 1                                            else state                        ) in   Loop ---- SCHNAPP Anwendungsbeispiel Ordnerpfad aus einem Dateip...

Mit Hilfe einer benutzerdefinierten Funktion (Language M) ist es möglich, mehrere Bedingungen für verschiedener [Felder] zu prüfen. Damit können mit Power Query komplexe Prüflogiken realisiert werden --- benutzerdefinierte Funktion Language M ---- let CheckWerte= (Wert1 as any, Wert2 as any, WertN as any) => if (Wert1 >=3 and Wert2 >=3 and WertN >=3) or (Wert1 >=5 and Wert2 >=5) then "wahr" else "falsch" in CheckWerte --- benutzerdefinierte Funktion Language M --- Praxis Beispiel, regelbasierte Ermittlung von Planlieferzeiten in Abhängigkeit von 3 Produktattributen [Artikelfamilie], [Modul], [Länge] --- SCHNIPP Praxis Beispiel mit 3 Prüfbedingungen let CheckWerte= (parArtikelfamilie as any, parModul as any, parLaenge as any) => if (parArtikelfamilie ="ZST" or parArtikelfamilie = "ZMT" and parModul = 200 or parModul = 300) and parLaenge <= 1000 then 28 else if (parArtikelfamilie ="ZST...

Metadaten einer Excel Arbeitsmappe können entweder mit Methode 1 VBA (Metadaten einer Excel Datei) ---- SCHNIPP --- Sub Dokument_Metadaten() 'Liest die Items der Dateieigenschaften / Metadaten aus On Error Resume Next With ActiveWorkbook     .Worksheets(1).Activate     Range("A1:B30").ClearContents         For i = 1 To 40             Cells(i, 1).Value = ActiveWorkbook.BuiltinDocumentProperties(i).Name             Cells(i, 2).Value = ActiveWorkbook.BuiltinDocumentProperties(i).Value         Next End With End Sub --- SCHNAPP --- oder Methode 2 Power Query / Language M (Metadaten mehrerer Excel Dateien in einem Ordner) = Folder.Files("Laufwerk:\Verzeichnis") ausgelesen werden. --- SCHNIPP wiederverwendbare Language M Funktion für Metadatum [letztes Änderungsdatum] --- (Ordnerpfad as text, DateiName as text) => let   ...

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

Alle Datenquellen beim Öffnen der Excel Arbeitsmappe aktualisieren Alle Datenquellen oder Datenquellen selektiv öffnen mit VBA ---- SCHNIPP --- Public Sub UpdatePowerQueries() ' VBA um Datenquellen zu aktualisieren Dim lngPowerQuery As Long, objDataSource As WorkbookConnection Dim objWorksheet As Worksheet On Error Resume Next For Each objDataSource In ThisWorkbook.Connections     'Arbeitsmappenabfrage = Power Query ?     lngPowerQuery = InStr(1, objDataSource.OLEDBConnection.Connection, "Provider=Microsoft.Mashup.OleDb.1", vbTextCompare)         If Err.Number <> 0 Then             Err.Clear             Exit For         End If 'Power Query ? Datenquelle aktualisieren 'Variante 1 - Alle Datenquellen aktualisieren If lngPowerQuery > 0 Then objDataSource.Refresh 'Variante 2 - selektiv Datenquellen aktualisieren Select Case objDataSour...

3 stufiger Prozess 1 vom Ende her denken : wie soll das Endresultat aussehen ? 2 finde die M Funktionen, die Dich dahin führen 3 Kombiniere die M Funktionen in einer Formel innerhalb einer benutzerdefinierten Spalte Wie finde ich M Funktionen ? Tip: Spalte hinzufügen -> Spalte aus Beispielen Tip: Alle M Funktionen auflisten Beispiel 1 Ändern des Datums zu Jahr in nur einem Schritt Transformation von [28.06.2018] in  [KJ 2018] neue benutzerdefinierte Spalte einfügen ”KJ”& Text.From(Date.Year([Datum])) nimm den Wert in der Datumsspalte [Datum] Konvertiere [Datum] in [Jahr] indem die Funktion [Date.Year] benutzt wird Füge Präfix [KJ] hinzu (Verkettung) Beispiel 2 von [28.06.2018] zu [KW_02] in einem Schritt Transformiere Datum zu Woche — Date.WeekOfYear                                                      ...

generische URL Struktur einer Sharepoint Site: https://server/sitecollection/site List Services /_vti_bin/ListData.svc Beispiel https://firma.com/abteilungen/einkauf/_vti_bin/ListData.svc 1          (OData-) Datenfeed als Datenquelle an Excel anbinden 2           List Services als URL hinterlegen 3        Liste auswählen 4       Daten -> Verbindungen -> Eigenschaften -> Tab Definition -> Authentifizierungseinstellungen -> ein gespeichertes Konto verwenden [ExcelServicesUnattended] siehe auch ODBC Datenverbindung zu Exceldatei erstellen weiterführender link