Αντικατάσταση μαζικού κειμένου στο Power Query με λειτουργία List.Acumulate

Πώς να αντικαταστήσετε γρήγορα και μαζικά το κείμενο σύμφωνα με τη λίστα αναφοράς με τύπους - το έχουμε ήδη ταξινομήσει. Τώρα ας προσπαθήσουμε να το κάνουμε στο Power Query.

Όπως συμβαίνει συχνά εκτελέσει αυτή η εργασία είναι πολύ πιο εύκολη από την εξήγηση WHY λειτουργεί, αλλά ας προσπαθήσουμε να κάνουμε και τα δύο 🙂

Έτσι, έχουμε δύο «έξυπνους» δυναμικούς πίνακες που έχουν δημιουργηθεί από συνηθισμένες περιοχές με συντόμευση πληκτρολογίου Ctrl+T ή ομάδα Αρχική σελίδα – Μορφοποίηση ως πίνακα (Αρχική σελίδα — Μορφοποίηση ως πίνακα):

Κάλεσα το πρώτο τραπέζι ημερομηνία, ο δεύτερος πίνακας – Τηλεφωνικός κατάλογοςχρησιμοποιώντας πεδίο Όνομα πίνακα (Όνομα πίνακα) αυτί Κατασκευαστής (Σχέδιο).

Εργασία: αντικαταστήστε τις διευθύνσεις στον πίνακα ημερομηνία όλες οι εμφανίσεις από μια στήλη Να βρω Εγχειρίδιο στα αντίστοιχα σωστά αντίστοιχά τους από τη στήλη Υποκατάστατο. Το υπόλοιπο κείμενο στα κελιά θα πρέπει να παραμείνει ανέγγιχτο.

Βήμα 1. Φορτώστε τον κατάλογο στο Power Query και μετατρέψτε τον σε λίστα

Αφού ορίσετε το ενεργό κελί σε οποιοδήποτε σημείο του πίνακα αναφοράς, κάντε κλικ στην καρτέλα ημερομηνία (Ημερομηνία)ή στην καρτέλα Ερώτημα ισχύος (εάν έχετε μια παλιά έκδοση του Excel και έχετε εγκαταστήσει το Power Query ως πρόσθετο σε ξεχωριστή καρτέλα) στο κουμπί Από τραπέζι/σειρά (Από πίνακα/Εύρος).

Ο πίνακας αναφοράς θα φορτωθεί στο πρόγραμμα επεξεργασίας ερωτημάτων Power Query:

Για να μην παρεμβαίνει, προστέθηκε αυτόματα ένα βήμα τροποποιημένου τύπου (Αλλαγμένος τύπος) στο δεξιό πλαίσιο, τα βήματα που εφαρμόζονται μπορούν να διαγραφούν με ασφάλεια, αφήνοντας μόνο το βήμα Πηγή (Πηγή):

Τώρα, για να εκτελέσουμε περαιτέρω μετασχηματισμούς και αντικαταστάσεις, πρέπει να μετατρέψουμε αυτόν τον πίνακα σε λίστα (λίστα).

Λυρική παρέκκλιση

• Τραπέζι είναι ένας δισδιάστατος πίνακας που αποτελείται από πολλές σειρές και στήλες.
• Ρεκόρ (Ρεκόρ) – μονοδιάστατη συμβολοσειρά πίνακα, που αποτελείται από πολλά πεδία-στοιχεία με ονόματα, για παράδειγμα [Όνομα = "Masha", Φύλο = "f", Ηλικία = 25]
  
• Λίστα – ένας μονοδιάστατος πίνακας-στήλη, που αποτελείται από πολλά στοιχεία, για παράδειγμα {1, 2, 3, 10, 42} or { "Πίστη αγάπη ελπίδα" }
  

Για να λύσουμε το πρόβλημά μας, θα μας ενδιαφέρει πρωτίστως ο τύπος Λίστα.

Το κόλπο εδώ είναι ότι τα στοιχεία λίστας στο Power Query μπορεί να είναι όχι μόνο απλοί αριθμοί ή κείμενο, αλλά και άλλες λίστες ή εγγραφές. Είναι σε μια τόσο δύσκολη λίστα (λίστα), που αποτελείται από εγγραφές (εγγραφές) που πρέπει να γυρίσουμε τον κατάλογό μας. Στη συντακτική σημειογραφία του Power Query (εγγραφές σε αγκύλες, λίστες σε σγουρές αγκύλες) θα μοιάζει με:

{

    [ Εύρεση = «Αγ. Petersburg”, Replace = “St. Πετρούπολη»] ,

    [ Εύρεση = «Αγ. Petersburg”, Replace = “St. Πετρούπολη»] ,

    [ Find = “Peter”, Replace = “St. Πετρούπολη»] ,

και ούτω καθεξής

}

Ένας τέτοιος μετασχηματισμός πραγματοποιείται χρησιμοποιώντας μια ειδική συνάρτηση της γλώσσας M που είναι ενσωματωμένη στο Power Query – Table.ToRecords. Για να την εφαρμόσετε απευθείας στη γραμμή τύπων, προσθέστε αυτήν τη συνάρτηση στον κώδικα βήματος εκεί Πηγή.

Ήταν:

Μετά:

Μετά την προσθήκη της συνάρτησης Table.ToRecords, η εμφάνιση του πίνακα μας θα αλλάξει – θα μετατραπεί σε μια λίστα εγγραφών. Τα περιεχόμενα των μεμονωμένων εγγραφών μπορούν να προβληθούν στο κάτω μέρος του παραθύρου προβολής κάνοντας κλικ στο φόντο του κελιού δίπλα σε οποιαδήποτε λέξη Ρεκόρ (αλλά όχι με μια λέξη!)

Εκτός από τα παραπάνω, είναι λογικό να προσθέσουμε ένα ακόμη stroke – στην προσωρινή αποθήκευση (buffer) στη λίστα που δημιουργήσαμε. Αυτό θα αναγκάσει το Power Query να φορτώσει τη λίστα αναζήτησης μία φορά στη μνήμη και να μην την υπολογίσει ξανά όταν αργότερα αποκτήσουμε πρόσβαση για να την αντικαταστήσουμε. Για να το κάνετε αυτό, τυλίξτε τον τύπο μας σε μια άλλη συνάρτηση - List.Buffer:

Μια τέτοια προσωρινή αποθήκευση θα δώσει μια πολύ αισθητή αύξηση της ταχύτητας (κατά αρκετές φορές!) με μεγάλο όγκο αρχικών δεδομένων προς εκκαθάριση.

Αυτό ολοκληρώνει την προετοιμασία του εγχειριδίου.

Απομένει να κάνετε κλικ Αρχική σελίδα – Κλείσιμο και φόρτωση – Κλείσιμο και φόρτωση σε… (Αρχική — Κλείσιμο&Φόρτωση — Κλείσιμο&Φόρτωση σε..), ορίστε μια επιλογή Απλώς δημιουργήστε μια σύνδεση (Δημιουργία μόνο σύνδεσης) και επιστρέψτε στο Excel.

Βήμα 2. Φόρτωση του πίνακα δεδομένων

Όλα είναι τετριμμένα εδώ. Όπως και πριν με το βιβλίο αναφοράς, ανεβαίνουμε σε οποιοδήποτε σημείο του πίνακα, κάνουμε κλικ στην καρτέλα ημερομηνία κουμπί Από Πίνακας/Εύρος και το τραπέζι μας ημερομηνία μπαίνει στο Power Query. Αυτόματη προσθήκη βήματος τροποποιημένου τύπου (Αλλαγμένος τύπος) μπορείτε επίσης να αφαιρέσετε:

Δεν απαιτείται να γίνουν ιδιαίτερες προπαρασκευαστικές ενέργειες με αυτό και προχωράμε στο πιο σημαντικό.

Βήμα 3. Εκτελέστε αντικαταστάσεις χρησιμοποιώντας τη συνάρτηση List.Acumulate

Ας προσθέσουμε μια υπολογιζόμενη στήλη στον πίνακα δεδομένων μας χρησιμοποιώντας την εντολή Προσθήκη στήλης – Προσαρμοσμένη στήλη (Προσθήκη στήλης — Προσαρμοσμένη στήλη): και εισαγάγετε το όνομα της στήλης που προστέθηκε στο παράθυρο που ανοίγει (για παράδειγμα, διορθωμένη διεύθυνση) και τη μαγική μας λειτουργία List.Acumulate:

Απομένει να κάνετε κλικ OK – και παίρνουμε μια στήλη με τις αντικαταστάσεις που έγιναν:

Σημειώστε ότι:

• Εφόσον το Power Query κάνει διάκριση πεζών-κεφαλαίων, δεν έγινε αντικατάσταση στην προτελευταία γραμμή, γιατί στον κατάλογο έχουμε "SPb", όχι "SPb".
• Εάν υπάρχουν πολλές υποσυμβολοσειρές προς αντικατάσταση ταυτόχρονα στα δεδομένα προέλευσης (για παράδειγμα, στην 7η γραμμή πρέπει να αντικαταστήσετε τόσο το "S-Pb" και το "Ενημερωτικό Δελτίο"), τότε αυτό δεν δημιουργεί προβλήματα (σε αντίθεση με την αντικατάσταση με τύπους από την προηγούμενη μέθοδο).
• Εάν δεν υπάρχει τίποτα προς αντικατάσταση στο κείμενο προέλευσης (9η γραμμή), τότε δεν παρουσιάζονται σφάλματα (σε αντίθεση, πάλι, από την αντικατάσταση από τύπους).

Η ταχύτητα ενός τέτοιου αιτήματος είναι πολύ, πολύ αξιοπρεπής. Για παράδειγμα, για έναν πίνακα αρχικών δεδομένων με μέγεθος 5000 σειρών, αυτό το ερώτημα ενημερώθηκε σε λιγότερο από ένα δευτερόλεπτο (χωρίς αποθήκευση στην προσωρινή μνήμη, παρεμπιπτόντως, περίπου 3 δευτερόλεπτα!)

Πώς λειτουργεί η συνάρτηση List.Acumulate

Κατ 'αρχήν, αυτό θα μπορούσε να είναι το τέλος (για να γράψω εγώ και να διαβάσετε εσείς) αυτό το άρθρο. Εάν θέλετε όχι μόνο να μπορείτε, αλλά και να καταλάβετε πώς λειτουργεί "κάτω από την κουκούλα", τότε θα πρέπει να βουτήξετε λίγο βαθύτερα στην τρύπα του κουνελιού και να αντιμετωπίσετε τη λειτουργία List.Acumulate, η οποία έκανε όλη τη μαζική αντικατάσταση δουλέψτε για εμάς.

Η σύνταξη αυτής της συνάρτησης είναι:

=List.Acumulate(λίστα, σπόρος, συσσωρευτής)

όπου

• λίστα είναι η λίστα της οποίας τα στοιχεία επαναλαμβάνουμε. 
• σπόρος - αρχική κατάσταση
• συσσωρευτής – μια συνάρτηση που εκτελεί κάποια πράξη (μαθηματική, κείμενο κ.λπ.) στο επόμενο στοιχείο της λίστας και συγκεντρώνει το αποτέλεσμα της επεξεργασίας σε μια ειδική μεταβλητή.
  

Γενικά, η σύνταξη για τη γραφή συναρτήσεων στο Power Query μοιάζει με αυτό:

(όρισμα1, όρισμα2, … επιχείρημαN) => μερικές ενέργειες με ορίσματα

Για παράδειγμα, η συνάρτηση άθροισης θα μπορούσε να αναπαρασταθεί ως:

(α, β) => α + β

Για το List.Acumulate , αυτή η συνάρτηση συσσωρευτή έχει δύο απαιτούμενα ορίσματα (μπορούν να ονομαστούν οτιδήποτε, αλλά τα συνηθισμένα ονόματα είναι κατάσταση и ρεύμα, όπως στην επίσημη βοήθεια για αυτήν τη λειτουργία, όπου:

• κατάσταση – μια μεταβλητή όπου συγκεντρώνεται το αποτέλεσμα (η αρχική της τιμή είναι αυτή που αναφέρεται παραπάνω σπόρος)
  
• ρεύμα – την επόμενη επαναλαμβανόμενη τιμή από τη λίστα λίστα
  

Για παράδειγμα, ας ρίξουμε μια ματιά στα βήματα της λογικής της ακόλουθης κατασκευής:

=List.Acumulate({3, 2, 5}, 10, (κατάσταση, ρεύμα) => κατάσταση + ρεύμα)

Αυτό είναι το πιο πρόσφατο συσσωρευμένο κατάσταση η τιμή είναι η List.Accumulate συνάρτηση και εξόδους ως αποτέλεσμα:

Εάν φανταστείτε λίγο, τότε χρησιμοποιώντας τη συνάρτηση List.Accumulate, μπορείτε να προσομοιώσετε, για παράδειγμα, τη συνάρτηση Excel CONCATENATE (στο Power Query, το ανάλογό της ονομάζεται Κείμενο.Συνδυάστε) χρησιμοποιώντας την έκφραση:

Ή ακόμα και να αναζητήσετε τη μέγιστη τιμή (απομίμηση της συνάρτησης MAX του Excel, η οποία ονομάζεται στο Power Query Λίστα.Μέγ):

Ωστόσο, το κύριο χαρακτηριστικό του List.Acumulate είναι η δυνατότητα επεξεργασίας όχι μόνο απλού κειμένου ή αριθμητικών λιστών ως ορίσματα, αλλά πιο πολύπλοκων αντικειμένων – για παράδειγμα, λίστες-από-λίστες ή λίστες-από-εγγραφές (γεια σας, Κατάλογος!)

Ας δούμε ξανά την κατασκευή που έκανε την αντικατάσταση στο πρόβλημά μας:

List.Acumulate(Τηλεφωνικός κατάλογος, [Διεύθυνση], (κατάσταση, τρέχον) => Κείμενο.Αντικατάσταση(κατάσταση, τρέχον[Εύρεση], τρέχουσα[Αντικατάσταση]) )

Τι πραγματικά συμβαίνει εδώ;

1. Ως αρχική τιμή (σπόρος) παίρνουμε το πρώτο αδέξιο κείμενο από τη στήλη [Διεύθυνση] το τραπέζι μας: 199034, Αγία Πετρούπολη, στρ. Beringa, d. 1
2. Στη συνέχεια List.Accumulate επαναλαμβάνει τα στοιχεία της λίστας ένα προς ένα – Εγχειρίδιο. Κάθε στοιχείο αυτής της λίστας είναι μια εγγραφή που αποτελείται από ένα ζεύγος πεδίων "Τι να βρείτε – Τι να αντικαταστήσετε με" ή, με άλλα λόγια, την επόμενη γραμμή στον κατάλογο.
3. Η συνάρτηση συσσωρευτής εισάγει μια μεταβλητή κατάσταση αρχική τιμή (πρώτη διεύθυνση 199034, Αγία Πετρούπολη, στρ. Beringa, d. 1) και εκτελεί μια λειτουργία συσσωρευτή σε αυτό - τη λειτουργία αντικατάστασης χρησιμοποιώντας την τυπική λειτουργία M Κείμενο.Αντικατάσταση (ανάλογη με τη συνάρτηση SUBSTITUTE του Excel). Η σύνταξή του είναι:

   Text.Replace (αρχικό κείμενο, τι ψάχνουμε, με τι αντικαθιστούμε)

   και εδώ έχουμε:

• κατάσταση είναι η βρώμικη διεύθυνσή μας, που βρίσκεται μέσα κατάσταση (φτάνοντας εκεί από σπόρος)
  
• τρέχουσα[Αναζήτηση] – τιμή πεδίου Να βρω από την επόμενη επαναλαμβανόμενη καταχώριση της λίστας Τηλεφωνικός κατάλογος, που βρίσκεται στη μεταβλητή ρεύμα
• τρέχουσα[Αντικατάσταση] – τιμή πεδίου Υποκατάστατο από την επόμενη επαναλαμβανόμενη καταχώριση της λίστας Τηλεφωνικός κατάλογοςξαπλωμένος μέσα ρεύμα

Έτσι, για κάθε διεύθυνση, εκτελείται κάθε φορά ένας πλήρης κύκλος απαρίθμησης όλων των γραμμών στον κατάλογο, αντικαθιστώντας το κείμενο από το πεδίο [Εύρεση] με την τιμή από το πεδίο [Αντικατάσταση].

Ελπίζω να έχετε την ιδέα 🙂

• Μαζική αντικατάσταση κειμένου σε μια λίστα χρησιμοποιώντας τύπους
  
• Κανονικές εκφράσεις (RegExp) στο Power Query