Περιεχόμενα
Εάν είστε τουλάχιστον λίγο εξοικειωμένοι με τις κανονικές εκφράσεις, τότε δεν χρειάζεται να τις διαφημίσετε. Εάν δεν είστε ακριβώς στο θέμα, τότε οι κανονικές εκφράσεις (Regular Expressions = RegExp = "regexps" = "κανονικές") είναι μια γλώσσα όπου, χρησιμοποιώντας ειδικούς χαρακτήρες και κανόνες, αναζητούνται οι απαραίτητες υποσυμβολοσειρές στο κείμενο, εξάγονται ή αντικαταστάθηκε με άλλο κείμενο. Αυτό είναι ένα πολύ ισχυρό και όμορφο εργαλείο, μια τάξη μεγέθους ανώτερη από όλους τους άλλους τρόπους εργασίας με κείμενο.
Έχω ήδη περιγράψει λεπτομερώς και με πολλά παραδείγματα από τη ζωή πώς μπορείτε να προσθέσετε υποστήριξη τυπικών εκφράσεων στο Excel χρησιμοποιώντας απλές μακροεντολές – αν δεν έχετε διαβάσει αυτό το άρθρο, σας συνιστώ ανεπιφύλακτα να το διαβάσετε πριν συνεχίσετε. Θα ανακαλύψετε πολλά νέα πράγματα, το εγγυώμαι 🙂
Ωστόσο, το ερώτημα παραμένει ανοιχτό – πώς να προσθέσετε τη δυνατότητα χρήσης κανονικών εκφράσεων στο Power Query; Το Power Query, φυσικά, είναι καλό από μόνο του και μπορεί να κάνει πολλά με το κείμενο (κόψιμο, κόλληση, καθάρισμα κ.λπ.), αλλά αν μπορούσατε να το διασταυρώσετε με τη δύναμη των κανονικών εκφράσεων, θα ήταν απλώς μια βόμβα.
Δυστυχώς, δεν υπάρχουν ενσωματωμένες λειτουργίες για εργασία με το RegExps στο Power Query και η επίσημη βοήθεια και τεχνική υποστήριξη της Microsoft απαντούν αρνητικά σε αυτήν την ερώτηση. Ωστόσο, υπάρχει τρόπος να ξεπεραστεί αυτός ο περιορισμός 🙂
Η ουσία της μεθόδου
Η κύρια ιδέα είναι απλή έως ατιμωτική.
Στη λίστα των ενσωματωμένων δυνατοτήτων Power Query, υπάρχει μια λειτουργία Ιστοσελίδα. Η περιγραφή αυτής της λειτουργίας στον επίσημο ιστότοπο βοήθειας της Microsoft είναι εξαιρετικά συνοπτική:
Σε μετάφραση, αυτό θα ήταν: "Επιστρέφει τα περιεχόμενα του εγγράφου HTML αναλυμένα στις συνιστώσες του, καθώς και μια αναπαράσταση του πλήρους εγγράφου και του σώματος του μετά την αφαίρεση των ετικετών." Τόσο περιγραφή, ειλικρινά.
Συνήθως αυτή η λειτουργία χρησιμοποιείται κατά την εισαγωγή δεδομένων από τον Ιστό και αντικαθίσταται αυτόματα, για παράδειγμα, όταν επιλέγουμε στην καρτέλα ημερομηνία εντολή Από το διαδίκτυο (Δεδομένα — Από τον Ιστό). Δίνουμε στη συνάρτηση μια ιστοσελίδα ως όρισμα και μας επιστρέφει το περιεχόμενό της με τη μορφή πινάκων, έχοντας προηγουμένως καθαρίσει όλες τις ετικέτες.
Αυτό που ΔΕΝ λέει η βοήθεια είναι ότι εκτός από τη γλώσσα σήμανσης HTML λειτουργία Ιστοσελίδα υποστηρίζει σενάρια JavaScript, το οποίο είναι πλέον πανταχού παρόν σε ιστότοπους στο Διαδίκτυο. Και η JavaScript, με τη σειρά της, ήταν πάντα σε θέση να λειτουργεί με κανονικές εκφράσεις και έχει ενσωματωμένες λειτουργίες για το RegExps! Έτσι, για να εφαρμόσουμε κανονικές εκφράσεις στο Power Query, θα χρειαστεί να τροφοδοτήσουμε τις λειτουργίες Web.Page ως όρισμα σε ένα μικρό πρόγραμμα JavaScript που θα κάνει όλη τη δουλειά για το Power Query.
Πώς φαίνεται σε καθαρή JavaScript
Υπάρχουν πολλά λεπτομερή μαθήματα για την εργασία με κανονικές εκφράσεις σε JavaScript στο Διαδίκτυο (για παράδειγμα, ένα, δύο).
Συνοπτικά και απλοποιημένα, ο κώδικας JavaScript θα μοιάζει με αυτό:
Εδώ:
- var str = 'Πληρωμή λογαριασμών 123 και 789 για λουκάνικο'; – δημιουργία μεταβλητής str και του αναθέστε το κείμενο πηγής που θα αναλύσουμε.
- var pattern = /d+/gi; – δημιουργήστε μια τυπική έκφραση και βάλτε την σε μια μεταβλητή πρότυπο.
Η έκφραση ξεκινά με κάθετο (/).
Η ίδια η έκφραση εδώ, για παράδειγμα, είναι d+ σημαίνει οποιαδήποτε ακολουθία ψηφίων.
Μέσω του κλάσματος μετά την έκφραση, υπάρχουν πρόσθετες παράμετροι αναζήτησης (τροποποιητές) - μπορούν να καθοριστούν με οποιαδήποτε σειρά:
- g – σημαίνει καθολική αναζήτηση, δηλαδή μετά την εύρεση αντιστοιχίας, δεν πρέπει να σταματήσετε, αλλά να συνεχίσετε την αναζήτηση μέχρι το τέλος του κειμένου. Εάν αυτός ο τροποποιητής δεν έχει οριστεί, τότε το σενάριό μας θα επιστρέψει μόνο την πρώτη αντιστοίχιση (123)
- i – αναζήτηση χωρίς να λαμβάνεται υπόψη η περίπτωση των γραμμάτων
- m – Αναζήτηση πολλών γραμμών (χρησιμοποιείται όταν το κείμενο προέλευσης χωρίζεται σε πολλές γραμμές)
- var result = str.match(pattern).join(';'); – πραγματοποιήστε αναζήτηση στο κείμενο πηγής (str) από τη δεδομένη κανονική έκφραση (πρότυπο) και βάλτε τα αποτελέσματα σε μια μεταβλητή αποτέλεσμα, συνενώνοντάς τα με ερωτηματικό χρησιμοποιώντας την εντολή ενταχθούν
- document.write(result); – εμφάνιση των περιεχομένων της μεταβλητής αποτελέσματος
Σημειώστε επίσης ότι οι συμβολοσειρές κειμένου (εξαιρουμένων των τυπικών εκφράσεων) στο JavaScript περικλείονται σε απόστροφα, όχι σε εισαγωγικά όπως είναι στο Power Query ή στο VBA.
Στην έξοδο, αυτό το σενάριο θα μας δώσει ως αποτέλεσμα όλους τους αριθμούς που βρίσκονται στο κείμενο προέλευσης:
123, 789
Ο σύντομος κύκλος μαθημάτων JavaScript ολοκληρώθηκε, σας ευχαριστούμε όλους. Ελπίζω να καταλαβαίνεις τη λογική 🙂
Απομένει να μεταφερθεί αυτή η κατασκευή στο Power Query.
Αναζήτηση και εξαγωγή συνάρτησης κειμένου με κανονική έκφραση στο Power Query
Κάνουμε τα εξής:
1. Ανοίξτε το Excel και δημιουργήστε ένα νέο κενό Power Query στην καρτέλα Δεδομένα – Λήψη δεδομένων / Δημιουργία αιτήματος – Από άλλες πηγές – Κενό αίτημα (Δεδομένα — Λήψη δεδομένων / Νέο ερώτημα — Από άλλες πηγές — Κενό ερώτημα). Εάν έχετε μια παλιά έκδοση του Excel 2010-2013 και το Power Query που δεν έχετε ενσωματωμένο, αλλά έχει εγκατασταθεί ως ξεχωριστό πρόσθετο, τότε όλα αυτά θα βρίσκονται στην καρτέλα Ερώτημα ισχύοςΚαι οχι ημερομηνία.
2. Στο κενό παράθυρο του προγράμματος επεξεργασίας ερωτημάτων που ανοίγει, στο δεξιό πλαίσιο, εισάγετε αμέσως το όνομα της μελλοντικής μας συνάρτησης (για παράδειγμα, fxRegExpExtract)
3. Ας πάμε στην καρτέλα Προβολή – Σύνθετη επεξεργασία (Προβολή — Σύνθετη επεξεργασία), διαγράφουμε ολόκληρο τον κωδικό M του κενού αιτήματος και επικολλάμε τον κωδικό της υπερσυνάρτησής μας εκεί:
Προσέξτε τα χέρια σας:
Στην πρώτη γραμμή, λέμε ότι η συνάρτησή μας θα έχει τρία ορίσματα κειμένου: txt – το πρωτότυπο κείμενο που αναλύεται, ρεγεξ – πρότυπο κανονικής έκφρασης, περιορισμένη — χαρακτήρας οριοθέτησης για την εμφάνιση αποτελεσμάτων.
Στη συνέχεια καλούμε τη συνάρτηση Ιστοσελίδα, σχηματίζοντας τον κώδικα JavaScript που περιγράφεται παραπάνω στο επιχείρημά του. Επικολλάμε και αντικαθιστούμε τα ορίσματα μεταβλητής μας στον κώδικα.
Θραύσμα:
[Δεδομένα]{0}[Children]{0}[Children]{1}[Text]{0}
… χρειάζεται για να «πέφτουμε» στον πίνακα με τα αποτελέσματα που χρειαζόμαστε. Το θέμα είναι ότι η συνάρτηση Ιστοσελίδα ως αποτέλεσμα, παράγει πολλούς ένθετους πίνακες που επαναλαμβάνουν τη δομή μιας ιστοσελίδας. Χωρίς αυτό το κομμάτι M-code, η συνάρτησή μας θα έβγαζε αυτό:
… και θα έπρεπε να κάνουμε κλικ στη λέξη αρκετές φορές Τραπέζι, διαδοχικά «πέφτει» σε παιδικούς ένθετους πίνακες σε στήλες Παιδιά:
Αντί για όλη αυτή την προσφορά, υποδεικνύουμε αμέσως στον κωδικό της συνάρτησής μας ποιος ένθετος πίνακας και στήλη (Κείμενο) χρειαζόμαστε.
Εδώ, στην πραγματικότητα, είναι όλα τα μυστικά. Απομένει να πατήσετε το κουμπί φινίρισμα στο παράθυρο προχωρημένος επεξεργαστής, όπου εισάγαμε τον κωδικό μας, και μπορείτε να προχωρήσετε στα πιο νόστιμα – δοκιμάστε τη λειτουργία μας στη δουλειά.
Εδώ είναι μερικά παραδείγματα σπόρων.
Παράδειγμα 1. Ανάκτηση του αριθμού λογαριασμού και της ημερομηνίας από την περιγραφή πληρωμής
Έχουμε ένα αντίγραφο κίνησης τραπεζικού λογαριασμού με περιγραφή (σκοπό) πληρωμών, όπου πρέπει να βγάλετε τους αριθμούς και τις ημερομηνίες των τιμολογίων που έχουν πληρωθεί σε ξεχωριστές στήλες:
Φορτώνουμε τον πίνακα στο Power Query με τον τυπικό τρόπο Δεδομένα – Από πίνακα/Εύρος (Στοιχεία - Από Τικανός/Rάγγελος).
Στη συνέχεια προσθέτουμε μια υπολογιζόμενη στήλη με τη συνάρτησή μας μέσω Προσθήκη στήλης – Κλήση προσαρμοσμένης συνάρτησης (Προσθήκη στήλης — Κλήση προσαρμοσμένης συνάρτησης) και εισάγετε τα ορίσματά του:
Ως κανονική έκφραση (επιχείρημα ρεγεξ) πρότυπο που χρησιμοποιούμε:
(δ{3,5}|d{2}.d{2}.d{4})
… μεταφράζεται στην ανθρώπινη γλώσσα που σημαίνει:
αριθμοί από 3 έως 5 ψηφία (αριθμοί λογαριασμού)
or
θραύσματα της φόρμας «αριθμός 2 bit – σημείο – αριθμός 2 bit – σημείο – αριθμός 4 bit», δηλαδή ημερομηνίες της μορφής ΗΗ.ΜΜ.ΕΕΕΕ.
Ως χαρακτήρας οριοθέτησης (επιχείρημα περιορισμένη) εισάγετε ένα ερωτηματικό.
Μετά το κλικ OK Η μαγική μας συνάρτηση αναλύει όλα τα αρχικά δεδομένα σύμφωνα με την κανονική μας έκφραση και σχηματίζει για εμάς μια στήλη με τους αριθμούς και τις ημερομηνίες των τιμολογίων που βρέθηκαν:
Απομένει να το διαχωρίσουμε με ερωτηματικό χρησιμοποιώντας την εντολή Αρχική σελίδα — Διαίρεση στήλης — Κατά οριοθέτη (Αρχική σελίδα — Διαίρεση στήλης — Κατά οριοθέτη) και παίρνουμε αυτό που θέλαμε:
Ομορφιά!
Παράδειγμα 2: Εξαγωγή διευθύνσεων email από κείμενο
Ας υποθέσουμε ότι έχουμε τον ακόλουθο πίνακα ως αρχικά δεδομένα:
… από όπου πρέπει να βγάλουμε τις διευθύνσεις email που βρίσκονται εκεί (για λόγους σαφήνειας, τις επισήμανα με κόκκινο χρώμα στο κείμενο).
Όπως στο προηγούμενο παράδειγμα, φορτώνουμε τον πίνακα στο Power Query με τον τυπικό τρόπο μέσω Δεδομένα – Από πίνακα/Εύρος (Στοιχεία - Από Τικανός/Rάγγελος).
Στη συνέχεια προσθέτουμε μια υπολογιζόμενη στήλη με τη συνάρτησή μας μέσω Προσθήκη στήλης – Κλήση προσαρμοσμένης συνάρτησης (Προσθήκη στήλης — Κλήση προσαρμοσμένης συνάρτησης) και εισάγετε τα ορίσματά του:
Η ανάλυση διευθύνσεων email είναι πιο δύσκολη υπόθεση και υπάρχουν πολλές κανονικές εκφράσεις διαφορετικού βαθμού εφιάλτη για την επίλυσή της. Χρησιμοποίησα μία από τις απλές επιλογές - όχι ιδανική, αλλά αρκετά λειτουργική στις περισσότερες περιπτώσεις:
[w|.|-]*@w*.[w|.]*
Ως διαχωριστικό (περιορισμένη) μπορείτε να εισάγετε ένα ερωτηματικό και ένα κενό.
Κάντε κλικ στο OK και λαμβάνουμε μια στήλη με διευθύνσεις ηλεκτρονικού ταχυδρομείου που εξάγονται από το αρχικό κείμενο "κουάκερ":
Μαγεία!
PS
Όπως λέει και η παροιμία: «Δεν υπάρχει τόσο καλό πράγμα που να μην μπορεί να γίνει ακόμα καλύτερο». Το Power Query είναι ωραίο από μόνο του και όταν συνδυάζεται με κανονικές εκφράσεις, μας δίνει εντελώς μη ρεαλιστική δύναμη και ευελιξία στην επεξεργασία οποιωνδήποτε δεδομένων κειμένου. Ελπίζω η Microsoft κάποια μέρα να προσθέσει την υποστήριξη RegExp στις ενημερώσεις του Power Query και του Power BI και όλοι οι παραπάνω χοροί με ντέφι θα γίνουν παρελθόν. Λοιπόν, προς το παρόν, ναι.
Θέλω επίσης να προσθέσω ότι είναι βολικό να παίζετε με κανονικές εκφράσεις στον ιστότοπο https://regexr.com/ – απευθείας στον ηλεκτρονικό επεξεργαστή. Εκεί στην ενότητα Πρότυπα Κοινότητας Υπάρχει ένας τεράστιος αριθμός έτοιμων κανονικών σεζόν για όλες τις περιστάσεις. Πειραματιστείτε – όλη η δύναμη των κανονικών εκφράσεων είναι τώρα στη διάθεσή σας στο Power Query!
- Τι είναι οι τυπικές εκφράσεις (RegExp) και πώς να τις χρησιμοποιήσετε στο Excel
- Αναζήτηση ασαφούς κειμένου στο Power Query
- Συναρμολόγηση πινάκων από διαφορετικά αρχεία χρησιμοποιώντας το Power Query