Im Blogbeitrag vom 10.01.2024 habe ich gezeigt, wie unterschiedlich lange Zeichenfolgen auf die gleiche Anzahl von Stellen vereinheitlicht werden. Dazu habe ich eine neue Spalte angelegt und dabei die Funktion Text.PadStart benutzt, die über das Menüband nicht zur Verfügung steht.
Heute zeige ich, wie sich Telefonnummern mit Power Query automatisiert in das internationale Format E.164 transformieren lassen. Diesmal OHNE zusätzliche Spalte. Dabei verwende ich Befehle aus dem Menüband und auch solche, die unter der Motorhaube versteckt sind.
Die Telefonnummern von links automatisiert in das internationale Format E.164 rechts umwandeln
Kurze Analyse der Originalliste mit inkonsistenten Telefonnummern
In der links oben gezeigten Liste besitzen die Telefonnummern zwar eine Landesvorwahl mit dem Plus-Zeichen, aber leider auch Zeichen, die im E.164-Format nicht vorkommen dürfen (mehr zum E.164-Format siehe ganz unten).
Zur Bereinigung sind daher folgende Schritte notwendig:
- die (0) nach der Landesvorwahl ersetzen durch nichts,
- Sonderzeichen wie Schrägstrich (/) und Bindestrich (–) eliminieren,
- alle Leerzeichen entfernen – am Anfang, am Ende und auch zwischen den Ziffern.
Liste in Power Query einlesen und zuerst einen Ersetzungsschritt durchführen
- Zuerst lese ich die Daten in Power Query ein und zwar über Daten > Daten abrufen > Aus Tabelle/Bereich.
- Im Power Query-Editor benenne ich die Abfrage rechts unter Abfrageeinstellungen um in Mobilnummer_Bereinigt [1].
- Um die Daten in der Spalte Mobilnummer mit Textfunktionen bearbeiten zu können, ändere ich per Klick auf das Datentypsymbol [2] den Datentyp auf Text [3].
Vor dem Einsatz von Textfunktionen der Datentyp auf Text einstellen
Um die (0) vor der Landesvorwahl zu entfernen, nutze ich den im Office-Alltag gebräuchlichen Befehl Ersetzen, der auch im Menüband des Power Query-Editors verfügbar ist.
Bei markierter Spalte Mobilnummer wähle ich unter Transformieren > Werte ersetzen > Werte ersetzen und ersetze (0) durch nichts.
Der oft gebrauchten Befehl Ersetzen kann auch in Power Query genutzt werden
Mobilnummern von Leer- und Sonderzeichen befreien
Im nächsten Schritt gilt es, alle Leer- und Sonderzeichen zu entfernen.
Im Menüband unter Transformieren sind einige typische Bereinigungsbefehle zu finden, u.a. auch Kürzen. Dieser Befehl entfernt Leerzeichen, aber nur am Anfang und Ende, nicht zwischen den Zeichen. Das hilft an der Stelle also nur bedingt weiter.
Im Menüband sind mehrere typische Bereinigungsmöglichkeiten für Textspalten verfügbar
Besser ist ein Befehl, der sich zwar »unter der Motorhaube« befindet, der aber ALLE überflüssigen Leer- und Sonderzeichen bereinigt:
- Bei markierter Spalte Mobilnummer wähle ich unter Transformieren irgendeinen Befehl in der Gruppe Textspalte, z. B. Extrahieren > Erste Zeichen: 1.
- Rechts im Aufgabenbereich benenne ich den Schritt um in SonderzeichenEntfernen.
- Die Funktion Text.Start tausche ich aus durch Text.Remove und passe die Argumentliste wie unten gezeigt an.
Mit der Funktion Text.Remove alle Leerzeichen und die gewünschten Sonderzeichen entfernen
Zur Erläuterung:
- Text.Remove entfernt ALLE Vorkommen des angegebenen Zeichens oder der angegebenen Liste mit Zeichen.
- Es können gleich mehrere Zeichen in Form einer Liste zum Entfernen vorgegeben werden.
- Eine solche Liste wird in Power Query mit geschweiften Klammern umschlossen und die Elemente darin (hier: Leerzeichen, Schrägstrich und Bindestrich) werden durch Komma getrennt: {“ „,“/“,“-„}.
Interessiert an mehr Techniken und Tipps zu Power Query?
Wer sein Wissen zu Power Query systematisch auf- und ausbauen will, kann dazu einen meiner Online-Kurse buchen.
Auf dieser Seite gibt es alle aktuellen Termine.
Das E.164-Format …
- ist international anerkannt und definiert die Notation für Telefonnummern weltweit.
- Es wird von der Internationalen Fernmeldeunion (ITU) empfohlen und festgelegt.
- Das Format legt eine einheitliche Darstellung von Telefonnummern fest, unabhängig von Ländercodes, um eine reibungslose Kommunikation zwischen verschiedenen Telefonnetzen und -systemen zu gewährleisten.
- Dazu werden Telefonnummern durch eine Kombination von Ziffern und Zeichen wie Pluszeichen (+) dargestellt, gefolgt von der Ländervorwahl und der eigentlichen Telefonnummer.
- Die Verwendung dieses Formats erleichtert die internationale Kommunikation und stellt sicher, dass Telefonnummern eindeutig und global einheitlich sind.
