Παράδειγμα στήλης – Τεχνητή νοημοσύνη στο Power Query

Ένα από τα βίντεο με τις περισσότερες προβολές στο κανάλι μου στο YouTube είναι ένα βίντεο σχετικά με το Flash Fill στο Microsoft Excel. Η ουσία αυτού του εργαλείου είναι ότι εάν πρέπει να μετατρέψετε με κάποιο τρόπο τα δεδομένα προέλευσης, τότε απλά πρέπει να αρχίσετε να πληκτρολογείτε το αποτέλεσμα που θέλετε να λάβετε στη διπλανή στήλη. Μετά από πολλά κελιά που πληκτρολογήσατε με μη αυτόματο τρόπο (συνήθως αρκούν 2-3), το Excel θα «καταλάβει» τη λογική των μετασχηματισμών που χρειάζεστε και θα συνεχίσει αυτόματα αυτό που έχετε πληκτρολογήσει, ολοκληρώνοντας όλη τη μονότονη δουλειά για εσάς:

Η πεμπτουσία της αποτελεσματικότητας. Το μαγικό κουμπί «κάνε το σωστά» που όλοι αγαπάμε τόσο πολύ, σωστά;

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

Παράδειγμα 1. Κόλληση/κοπή κειμένου

Ας υποθέσουμε ότι έχουμε έναν τόσο «έξυπνο» πίνακα στο Excel με δεδομένα για τους υπαλλήλους:

Φορτώστε το στο Power Query με τον τυπικό τρόπο – με το κουμπί Από Πίνακας/Εύρος αυτί ημερομηνία (Δεδομένα — Από πίνακα/εύρος).

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

• κάντε δεξί κλικ στην επικεφαλίδα της στήλης με τα δεδομένα προέλευσης και επιλέξτε την εντολή Προσθήκη στήλης από παραδείγματα (Προσθήκη στήλης από παραδείγματα);

• επιλέξτε μία ή περισσότερες στήλες με δεδομένα και στην καρτέλα Προσθήκη στήλης επιλέξτε μια ομάδα Στήλη από παραδείγματα. Εδώ, στην αναπτυσσόμενη λίστα, μπορείτε να καθορίσετε εάν πρέπει να αναλυθούν όλες ή μόνο οι επιλεγμένες στήλες.

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

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

Δώστε προσοχή στον τύπο στο επάνω μέρος του παραθύρου – αυτό δημιουργεί το smart Power Query για να έχουμε τα αποτελέσματα που χρειαζόμαστε. Αυτή, παρεμπιπτόντως, είναι η θεμελιώδης διαφορά μεταξύ αυτού του εργαλείου και Στιγμιαίο γέμισμα στο Excel. Το στιγμιαίο γέμισμα λειτουργεί σαν «μαύρο κουτί» – δεν μας δείχνουν τη λογική των μετασχηματισμών, αλλά απλώς δίνουν έτοιμα αποτελέσματα και τα θεωρούμε δεδομένα. Εδώ όλα είναι διαφανή και μπορείτε πάντα να καταλάβετε με απόλυτη σαφήνεια τι ακριβώς συμβαίνει με τα δεδομένα.

Εάν δείτε ότι το Power Query «έπιασε την ιδέα», τότε μπορείτε να πατήσετε με ασφάλεια το κουμπί OK ή συντόμευση πληκτρολογίου Ctrl+εισάγετε – θα δημιουργηθεί μια προσαρμοσμένη στήλη με έναν τύπο που επινοήθηκε από το Power Query. Παρεμπιπτόντως, μπορεί αργότερα να επεξεργαστεί εύκολα ως μια κανονική στήλη που δημιουργείται με μη αυτόματο τρόπο (με την εντολή Προσθήκη στήλης – Προσαρμοσμένη στήλη) κάνοντας κλικ στο εικονίδιο με το γρανάζι στα δεξιά του ονόματος του βήματος:

Παράδειγμα 2: Περίπτωση όπως στις προτάσεις

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

Βολικό και ωραίο, αλλά σε αυτήν τη λίστα, για παράδειγμα, προσωπικά μου έλειπε πάντα μια ακόμη επιλογή - περίπτωση όπως στις προτάσεις, όταν η κεφαλαία (κεφαλαία) δεν γίνεται το πρώτο γράμμα σε κάθε λέξη, αλλά μόνο το πρώτο γράμμα στο κελί, και το υπόλοιπο κείμενο όταν Αυτό εμφανίζεται με πεζά (μικρά) γράμματα.

Αυτό το χαρακτηριστικό που λείπει είναι εύκολο να εφαρμοστεί με την τεχνητή νοημοσύνη Στήλες από παραδείγματα – απλώς εισάγετε μερικές επιλογές για το Power Query για να συνεχίσετε με το ίδιο πνεύμα:

Ως τύπος εδώ, το Power Query χρησιμοποιεί μια δέσμη συναρτήσεων Κείμενο.Επάνω и Κείμενο.Κάτω, μετατροπή κειμένου σε κεφαλαία και πεζά, αντίστοιχα, και συναρτήσεις Κείμενο.Έναρξη и Κείμενο.Μέσα – ανάλογα των συναρτήσεων Excel LEFT και PSTR, με δυνατότητα εξαγωγής μιας υποσυμβολοσειράς από το κείμενο από τα αριστερά και από τη μέση.

Παράδειγμα 3. Μετάθεση λέξεων

Μερικές φορές, κατά την επεξεργασία των δεδομένων που λαμβάνονται, καθίσταται απαραίτητη η αναδιάταξη των λέξεων στα κελιά σε μια δεδομένη σειρά. Φυσικά, μπορείτε να χωρίσετε τη στήλη σε ξεχωριστές στήλες λέξεων με το διαχωριστικό και στη συνέχεια να την κολλήσετε ξανά με την καθορισμένη σειρά (μην ξεχάσετε να προσθέσετε κενά), αλλά με τη βοήθεια του εργαλείου Στήλη από παραδείγματα όλα θα είναι πολύ πιο εύκολα:

Παράδειγμα 4: Μόνο αριθμοί

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

Μπίνγκο!

Και πάλι, αξίζει να κοιτάξετε στην κορυφή του παραθύρου για να βεβαιωθείτε ότι το Query δημιούργησε σωστά τον τύπο – σε αυτήν την περίπτωση περιέχει μια συνάρτηση Κείμενο. Επιλέγω, το οποίο, όπως μπορείτε να μαντέψετε, εξάγει τους δεδομένους χαρακτήρες από το κείμενο προέλευσης σύμφωνα με τη λίστα. Στη συνέχεια, αυτή η λίστα, φυσικά, μπορεί εύκολα να επεξεργαστεί στη γραμμή τύπων εάν είναι απαραίτητο.

Παράδειγμα 5: Μόνο κείμενο

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

Σε αυτήν την περίπτωση, χρησιμοποιείται μια συνάρτηση που έχει ήδη αντίθετο νόημα – Text.Remove, η οποία αφαιρεί χαρακτήρες από την αρχική συμβολοσειρά σύμφωνα με μια δεδομένη λίστα.

Παράδειγμα 6: Εξαγωγή δεδομένων από αλφαριθμητικό χυλό

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

Σημειώστε ότι ο τύπος μετατροπής που δημιουργείται από το Power Query μπορεί να είναι αρκετά περίπλοκος:

Για ευκολία στην ανάγνωση και κατανόηση, μπορεί να μετατραπεί σε μια πολύ πιο λογική μορφή χρησιμοποιώντας μια δωρεάν διαδικτυακή υπηρεσία. Power Query Formatter:

Πολύ βολικό πράγμα - σεβασμός στους δημιουργούς!

Παράδειγμα 7: Μετατροπή ημερομηνιών

Εργαλείο Στήλη από παραδείγματα μπορεί να εφαρμοστεί και στις στήλες ημερομηνίας ή ημερομηνίας ώρας. Όταν εισάγετε τα πρώτα ψηφία μιας ημερομηνίας, το Power Query θα εμφανίσει βοηθητικά μια λίστα με όλες τις πιθανές επιλογές μετατροπής:

Έτσι, μπορείτε εύκολα να μετατρέψετε την αρχική ημερομηνία σε οποιαδήποτε εξωτική μορφή, όπως "έτος-μήνας-ημέρα":

Παράδειγμα 8: Κατηγοριοποίηση

Αν χρησιμοποιήσουμε το εργαλείο Στήλη από παραδείγματα σε μια στήλη με αριθμητικά δεδομένα, λειτουργεί διαφορετικά. Ας υποθέσουμε ότι έχουμε φορτωμένα αποτελέσματα δοκιμής υπαλλήλων στο Power Query (βαθμολογίες υπό όρους στο εύρος 0-100) και χρησιμοποιούμε την ακόλουθη διαβάθμιση υπό όρους:

• Masters – αυτοί που σημείωσαν περισσότερα από 90
• Ειδικοί – βαθμολογήθηκαν από 70 έως 90
• Χρήστες – από 30 έως 70
• Αρχάριοι – αυτοί που σημείωσαν λιγότερο από 30

Εάν προσθέσουμε μια στήλη από τα παραδείγματα στη λίστα και αρχίσουμε να τακτοποιούμε αυτές τις διαβαθμίσεις με μη αυτόματο τρόπο, τότε πολύ σύντομα το Power Query θα πάρει την ιδέα μας και θα προσθέσει μια στήλη με έναν τύπο, όπου οι τελεστές είναι φωλιασμένοι μεταξύ τους if Η λογική θα εφαρμοστεί, παρόμοια με αυτό που χρειαζόμαστε:

Και πάλι, δεν μπορείτε να πατήσετε την κατάσταση μέχρι το τέλος, αλλά κάντε κλικ στο OK και, στη συνέχεια, διορθώστε τις τιμές κατωφλίου ήδη στον τύπο - είναι πιο γρήγορο με αυτόν τον τρόπο:

συμπεράσματα

Σίγουρα ένα εργαλείο Στήλη από παραδείγματα δεν είναι ένα «μαγικό χάπι» και, αργά ή γρήγορα, θα υπάρξουν μη τυπικές καταστάσεις ή ιδιαίτερα παραμελημένες περιπτώσεις «συλλογικής φάρμας» στα δεδομένα, όταν το Power Query θα αποτύχει και δεν θα είναι σε θέση να βρει αυτό που θέλουμε σωστά για εμάς. Ωστόσο ως βοηθητικό εργαλείο είναι πολύ καλό. Επιπλέον, μελετώντας τους τύπους που δημιούργησε, μπορείτε να επεκτείνετε τις γνώσεις σας για τις λειτουργίες της γλώσσας Μ, οι οποίες θα είναι πάντα χρήσιμες στο μέλλον.

• Ανάλυση κειμένου με κανονικές εκφράσεις (RegExp) στο Power Query
• Αναζήτηση ασαφούς κειμένου στο Power Query
• Flash Συμπληρώστε το Microsoft Excel