Power Query: E-Mail-Adressen aus Zeichenketten auslesen
Daten, die in Freitextfelder eingegeben wurden, enthalten oft Details, die gebraucht werden. Beispielsweise E-Mail-Adressen wie in der folgenden Abbildung. Doch wie lassen sich gezielt die E-Mail-Adressen aus diesen Daten auslesen? Wie können solche wertvollen Informationen für Marketingzwecke, Supportanfragen oder Analysen nutzbar gemacht werden?
In meinem Rezept #25 zeige ich, wie in Power Query E-Mail-Adressen aus Bemerkungsfeldern extrahiert werden, indem ich alle Texte mit einem @-Zeichen verarbeite.
Alle Texte mit einem @ Zeichen extrahieren und in einer neue Spalte auflisten, ggf. getrennt mit |
Sollten Excel-Einsteiger »Power Query« lernen?
Bisher hätte ich diese Frage eher mit Nein beantwortet. Doch dann kam letzte Woche dieser Excel-Einstiegskurs: Dort berichteten 4 von 7 Teilnehmern, dass sie nicht mit Excel arbeiten und es auch nicht nutzen würden, wenn da nicht diese CSV wäre.
Bei weiterer Nachfrage kam heraus: wöchentlich muss eine CSV-Datei bearbeitet werden:
– zuerst die Angaben zum Datum in Ordnung bringen,
– dann die Daten nach einem Kriterium filtern,
– die verbleibenden Daten nach Datum sortieren,
– eine Multiplikation durchführen und
– abschließend eine überflüssige Spalte löschen.
Diese stupide Arbeit steht jede Woche an und es darf nichts vergessen werden.
Meine Reaktion? Ich zeigte, wie diese fünf Aufgaben von Power Query erledigt werden. Und, dass diese Aufbereitungsschritte beim nächsten Mal in kürzester Zeit durchgeführt werden, indem nur der Befehl Aktualisieren aufgerufen wird. Das Erstaunen bei den Teilnehmern war sichtbar, und sie wollten Power Query lernen.
In diesem Power Query Rezept #24 beschreibe ich, WIE es geht.
Die Daten der CSV-Datei filtern, sortieren, berechnen und das Ergebnis in einer Excel-Tabelle anzeigen
Power Query: Böse Überraschungen beim Runden vermeiden
Was ergibt sich, wenn die Zahl 2,5 gerundet wird? Excel liefert mit der Funktion RUNDEN das Ergebnis 3, Power Query hingegen 2.
Der Grund dafür: In Power Query wird bei Werten genau in der Mitte zwischen zwei ganzen Zahlen standardmäßig auf die nächste gerade Zahl auf- oder abgerundet. Aus 2,5 wird somit 2, aus 1,5 wird ebenfalls 2.
Wie Power Query beim Runden tickt und wie es auf kaufmännisches Runden umgestellt werden kann, beschreibe ich in diesem Rezept.
Zum Teil unterschiedliche Ergebnisse beim Runden in Excel und Power Query
Excel 365: Automatische Warnung vor veralteten Werten in Mappen mit manueller Berechnung
Wer die Berechnung seiner Arbeitsmappen auf Manuell einstellt, macht dies meist, um Tempoverluste beim Bearbeiten umfangreicher Mappen mit vielen Formeln zu vermeiden. Excel rechnet bei dieser Einstellung nur dann, wenn es explizit angewiesen wird, z. B. mit F9.
Diesem Vorteil steht allerdings ein gewichtiger Nachteil gegenüber: Wer nicht weiß oder vergessen hat, dass in einer Mappe die Berechnungsoption Manuell eingestellt ist, verlässt sich darauf, dass sich die Formelergebnisse beim Ändern der Daten automatisch anpassen. Ein folgenschwerer Irrtum!
Um dies zu vermeiden, bietet Excel 365 (im Betakanal) eine nützliche Neuerung:
- »Veraltete« Werte werden optisch hervorgehoben.
- Und zwar werden Zellwerte, die nicht mehr korrekt sind und neu berechnet werden müssen, automatisch durchgestrichen.
Hinweis auf eine weitere Neuerung in Excel 365
Power Query: Weniger Datenlast durch automatisches Ausschließen irrelevanter Spalten
Bei Datenbeständen mit technischen Angaben habe ich schon oft erlebt, dass zahlreiche Spalten nur temporär gebraucht werden oder für die Auswertung nicht von Belang sind. Solche Spalten haben beispielsweise Präfixe wie Sys_oder Tmp_ oder User_.
Um die Datenlast zu reduzieren, sollten solche nicht benötigten Spalten bei der Analyse ausgeschlossen werden.
Doch wie lässt sich das in Power Query automatisieren und wie lassen sich gezielt unnötige Spalten ermitteln? In diesem Power Query Rezept zeige ich, wie mit einer Änderung im M-Code solche Spalten automatisch entfernt werden, ohne die Spaltennamen direkt anzusprechen!
Spalten mit dem Präfix Sys_ automatisch von dem zu analysierenden Datenbestand ausschließen
Power Query: Störende null-Werte ersetzen und somit korrekte Berechnungen sicherstellen
Fehlende Werte – sog. null-Werte – sind oft der Grund, dass Berechnungen gar nicht oder nicht korrekt erfolgen können. In meinem Blogbeitrag Power Query: Falsche Ergebnisse bei leeren Zellen vermeiden habe ich erklärt, wie beim Addieren und Subtrahieren von Zahlen sichergestellt wird, dass auch Zellen mit null korrekt berechnet werden – in dem Fall mit Hilfe der Funktion List.Sum.
Im heutigen Blogbeitrag zeige ich, wie mit Hilfe des COALESCE-Operators ?? störende null-Werte auf einfache Art behandelt werden. Die vielfältige Verwendbarkeit des COALESCE-Operators demonstriere ich anhand von drei Beispielen: 1) Ermitteln des Saldos von Einnahmen und Ausgaben, 2) Berechnen eines rabattierten Betrags und 3) Auswerten von Messreihen.
Power Query ohne Chaos: Abfragen effizient dokumentieren
Wer kennt das nicht: Bereits nach wenigen Tagen erinnert man sich kaum noch daran, warum ein bestimmter Schritt in eine Power Query-Abfrage eingebaut wurde oder wozu eine spezielle Hilfsabfrage dienen soll. Noch schlimmer: Jemand verlässt das Team und hinterlässt einen Dschungel unkommentierter Abfragen. Wie lässt sich jetzt der Ablauf von Abfragen herausfinden oder gar ändern?
Die Lösung heißt Dokumentation. Doch mal ehrlich: Wer macht das schon gern? Daher zeige ich hier drei Methoden, wie das möglichst mühelos gelingt.
Power Query: Falsche Ergebnisse bei leeren Zellen vermeiden
Neulich bei einem bekannten Getränkehersteller: Im Kurs überrascht mich jemand mit der Frage, warum Power Query nicht fehlerfrei subtrahieren könne. Allerdings stellte sich schnell heraus, was die tatsächliche Ursache für falsche Ergebnisse war: Zellen ohne Inhalt, die in Power Query mit null angezeigt werden.
Was ist an null so besonders? Wird beispielsweise von einem Ausgangswert eine null-Zelle subtrahiert, gibt Power Query null zurück statt des Ausgangswertes. Das führt zu Fehlern, wenn der Rückgabewert null in weiteren Berechnungen verarbeitet wird. Mit den folgenden Schritten lassen sich solche Fehler vermeiden.
Bei null in einer Zelle rechnen die Operatoren Plus (+), Minus (-), Mal (*) oder Geteilt durch (/) nicht, sondern geben null zurück
Power Query: Duplikate eliminieren durch Gruppieren und Aggregieren und Informationen erhalten
Power Query wird oft genutzt, um Informationen aus einer Zelle zu trennen und auf verschiedenen Zeilen oder Spalten zu verteilen. Doch manchmal wird genau das Gegenteil gebraucht. Diesmal muss ich Inhalte aus mehreren Zeilen in einer Zelle zusammenfassen:
- Und zwar sind links in der grauen Originaltabelle die Artikel wegen verschiedener Herkunftsländer mehrfach aufgelistet.
- Ich benötige jedoch eine Übersicht, in der alle Artikel nur einmal stehen.
- Die Herkunftsländer sollen in einer Zelle als Zusatzinformation zusammengefasst werden.
Links die Artikelliste mit Duplikaten, rechts die Liste nach ArtikelNr gruppiert und die Herkunftsländer in einer Zelle gebündelt
Excel 365: Leichtere Orientierung mit dem Befehl »Fokuszelle«
Microsoft verbessert erneut das Benutzererlebnis: Künftig wird es deutlich leichter, in der Vielzahl von Zellen die jeweils aktive zu erkennen. Dazu wird Excel 365 um den Befehl Fokuszelle erweitert. Im Updatekanal Betaversion ist er bereits verfügbar. Es macht echt Spaß, diese Neuerung auszuprobieren! 🚀 Vorbei die Zeiten von Makros oder bedingten Formaten, die beim Kennzeichnen der aktiven Spalte und Zeile halfen.
Der neue Befehl Fokuszelle auf der Registerkarte Ansicht
