Το Power Query είναι ενσωματωμένο στις εκδόσεις των Office του Office 365, Excel 2016, Excel 2019 και διατίθεται ως δωρεάν λήψη σε εκδόσεις των Windows του Excel 2010 και του Excel 2013. Το εργαλείο έχει σχεδιαστεί για την εξαγωγή, μετατροπή και φόρτωση δεδομένων στο Excel από ποικιλία πηγών. Το καλύτερο μέρος: Το Power Query θυμάται τα βήματά σας και θα τα αναπαράγει όταν θέλετε να ανανεώσετε τα δεδομένα. Αυτό σημαίνει ότι μπορείτε να καθαρίσετε δεδομένα την Ημέρα 1 στο 80% της κανονικής ώρας και μπορείτε να καθαρίσετε δεδομένα τις Ημέρες 2 έως 400 κάνοντας απλώς κλικ στην Ανανέωση
Το λέω αυτό για πολλές νέες δυνατότητες του Excel, αλλά αυτό είναι πραγματικά το καλύτερο χαρακτηριστικό για να χτυπήσει το Excel σε 20 χρόνια.
Λέω μια ιστορία στα ζωντανά σεμινάριά μου για το πώς εφευρέθηκε το Power Query ως δεκανίκι για πελάτες SQL Server Analysis Services που αναγκάστηκαν να χρησιμοποιήσουν το Excel για να έχουν πρόσβαση στο Power Pivot. Αλλά το Power Query συνεχίζει να βελτιώνεται και κάθε άτομο που χρησιμοποιεί το Excel πρέπει να αφιερώνει χρόνο για να μάθει το Power Query.
Λάβετε Power Query
Ίσως έχετε ήδη Power Query. Είναι στην ομάδα Get & Transform στην καρτέλα Δεδομένα.
Αλλά αν είστε στο Excel 2010 ή στο Excel 2013, μεταβείτε στο Διαδίκτυο και αναζητήστε το Λήψη Power Query. Οι εντολές Power Query θα εμφανιστούν σε μια ειδική καρτέλα Power Query στην Κορδέλα.
Καθαρίστε τα δεδομένα την πρώτη φορά στο Power Query
Για να σας δώσουμε ένα παράδειγμα μερικής από την απροσδόκητη ενέργεια του Power Query, ας πούμε ότι λαμβάνετε το αρχείο που εμφανίζεται παρακάτω κάθε μέρα. Η στήλη Α δεν έχει συμπληρωθεί. Τα τέταρτα περνούν αντί για κάτω από τη σελίδα.
Για να ξεκινήσετε, αποθηκεύστε αυτό το βιβλίο εργασίας στο σκληρό σας δίσκο. Τοποθετήστε το σε ένα προβλέψιμο μέρος με ένα όνομα που θα χρησιμοποιείτε για αυτό το αρχείο κάθε μέρα.
Στο Excel, επιλέξτε Λήψη δεδομένων, Από αρχείο, από βιβλίο εργασίας.
Περιηγηθείτε στο βιβλίο εργασίας. Στο παράθυρο προεπισκόπησης, κάντε κλικ στο Φύλλο1. Αντί να πατήσετε Φόρτωση, κάντε κλικ στο Επεξεργασία. Τώρα βλέπετε το βιβλίο εργασίας σε ένα ελαφρώς διαφορετικό πλέγμα - το πλέγμα Power Query.
Τώρα πρέπει να διορθώσετε όλα τα κενά κελιά στη στήλη A. Εάν το κάνετε αυτό στη διεπαφή χρήστη του Excel, η δυσκίνητη ακολουθία εντολών είναι Home, Find & Select, Go To Special, Blanks, Equals, Up Arrow, Ctrl + Enter .
Στο Power Query, επιλέξτε Transform, Fill, Down.
Όλες οι μηδενικές τιμές αντικαθίστανται με την τιμή από πάνω. Με το Power Query, χρειάζονται τρία κλικ αντί για επτά.
Επόμενο πρόβλημα: Τα τέταρτα περνούν αντί να πέφτουν. Στο Excel, μπορείτε να το διορθώσετε με έναν συγκεντρωτικό πίνακα πολλαπλών συγκεντρώσεων. Αυτό απαιτεί 12 βήματα και 23+ κλικ.
Στο Power Query επιλέξτε τις δύο στήλες που δεν είναι τέταρτα. Ανοίξτε το αναπτυσσόμενο μενού Unpivot Columns στην καρτέλα Transform και επιλέξτε Unpivot Other Columns, όπως φαίνεται παρακάτω.
Κάντε δεξί κλικ στη νεοσυσταθείσα στήλη Χαρακτηριστικό και μετονομάστε το Quarter αντί για Attribute. Τα κλικ είκοσι-συν στο Excel γίνονται πέντε κλικ στο Power Query.
Τώρα, για να είμαστε δίκαιοι, δεν είναι κάθε βήμα καθαρισμού μικρότερο στο Power Query από ό, τι στο Excel. Η κατάργηση μιας στήλης εξακολουθεί να σημαίνει δεξί κλικ σε μια στήλη και επιλογή Κατάργηση στήλης. Αλλά για να είμαι ειλικρινής, η ιστορία εδώ δεν αφορά την εξοικονόμηση χρόνου την 1η ημέρα.
Περιμένετε: Το Power Query θυμίζει όλα τα βήματά σας
Κοιτάξτε στη δεξιά πλευρά του παραθύρου Power Query. Υπάρχει μια λίστα με την ονομασία Applied Steps. Είναι ένα στιγμιαίο ίχνος ελέγχου όλων των βημάτων σας. Κάντε κλικ σε οποιοδήποτε εικονίδιο με το γρανάζι για να αλλάξετε τις επιλογές σας σε αυτό το βήμα και κάντε τις αλλαγές να ακολουθούν τα μελλοντικά βήματα. Κάντε κλικ σε οποιοδήποτε βήμα για να δείτε την εμφάνιση των δεδομένων πριν από αυτό το βήμα.
Όταν ολοκληρώσετε τον καθαρισμό των δεδομένων, κάντε κλικ στην επιλογή Κλείσιμο και φόρτωση όπως φαίνεται παρακάτω.
Υπόδειξη
Εάν τα δεδομένα σας έχουν περισσότερες από 1.048.576 σειρές, μπορείτε να χρησιμοποιήσετε το αναπτυσσόμενο μενού Κλείσιμο & φόρτωση για να φορτώσετε τα δεδομένα απευθείας στο Power Pivot Data Model, το οποίο μπορεί να φιλοξενήσει 995 εκατομμύρια σειρές εάν έχετε αρκετή μνήμη εγκατεστημένη στο μηχάνημα.
Σε λίγα δευτερόλεπτα, τα μετασχηματισμένα δεδομένα σας εμφανίζονται στο Excel. Φοβερός.
Η πληρωμή: Καθαρίστε τα δεδομένα αύριο με ένα κλικ
Αλλά και πάλι, η ιστορία του Power Query δεν αφορά την εξοικονόμηση χρόνου την Ημέρα 1. Όταν επιλέγετε τα δεδομένα που επιστρέφονται από το Power Query, εμφανίζεται ένας πίνακας ερωτημάτων και συνδέσεων στη δεξιά πλευρά του Excel και σε αυτό είναι ένα κουμπί Ανανέωση. (Χρειαζόμαστε ένα κουμπί Επεξεργασίας εδώ, αλλά επειδή δεν υπάρχει, πρέπει να κάνετε δεξί κλικ στο αρχικό ερώτημα για να δείτε ή να κάνετε αλλαγές στο αρχικό ερώτημα).
Είναι διασκεδαστικό να καθαρίζετε δεδομένα την Ημέρα 1. Μου αρέσει να κάνω κάτι νέο. Αλλά όταν ο διευθυντής μου βλέπει την προκύπτουσα αναφορά και λέει «Ωραία. Μπορείς να το κάνεις αυτό κάθε μέρα; " Μεγάλωσα γρήγορα να μισώ το μέσον καθαρισμού του ίδιου συνόλου δεδομένων κάθε μέρα.
Έτσι, για να δείξω την Ημέρα 400 του καθαρισμού των δεδομένων, έχω αλλάξει εντελώς το αρχικό αρχείο. Νέα προϊόντα, νέοι πελάτες, μικρότεροι αριθμοί, περισσότερες σειρές, όπως φαίνεται παρακάτω. Αποθηκεύω αυτήν τη νέα έκδοση του αρχείου στην ίδια διαδρομή και με το ίδιο όνομα αρχείου με το αρχικό αρχείο.
Εάν ανοίξω το βιβλίο εργασίας του ερωτήματος και κάντε κλικ στην Ανανέωση, σε λίγα δευτερόλεπτα, το Power Query αναφέρει 92 σειρές αντί 68 σειρές.
Η εκκαθάριση των δεδομένων την Ημέρα 2, την Ημέρα 3, την Ημέρα, 4,… Ημέρα 400,… Η Ημέρα Άπειρο παίρνει τώρα δύο κλικ.
Αυτό το μόνο παράδειγμα γρατσουνίζει την επιφάνεια του Power Query. Εάν περάσετε δύο ώρες με το βιβλίο, το M is for (Data) Monkey των Ken Puls και Miguel Escobar, θα μάθετε για άλλες λειτουργίες, όπως αυτές:
- Συνδυάζοντας όλα τα αρχεία Excel ή CSV από ένα φάκελο σε ένα μόνο πλέγμα Excel
- Μετατροπή ενός κελιού με Apple; Μπανάνα, Κεράσι, Dill; Μελιτζάνα σε πέντε σειρές στο Excel
- Κάνοντας ένα VLOOKUP σε ένα βιβλίο εργασίας αναζήτησης καθώς εισάγετε δεδομένα στο Power Query
- Δημιουργία ενός ερωτήματος σε μια συνάρτηση που μπορεί να εφαρμοστεί σε κάθε σειρά του Excel
Για μια πλήρη περιγραφή του Power Query, δείτε το M Is for (Data) Monkey από τους Ken Puls και Miguel Escobar. Μέχρι τα τέλη του 2019, η δεύτερη έκδοση με τίτλο, Master Your Data, θα είναι διαθέσιμη.
Χάρη στους Miguel Escobar, Rob Garcia, Mike Girvin, Ray Hauser και Colin Michael για το διορισμό του Power Query.
