Μπορεί να είστε αρκετά εξοικειωμένοι με τα ερωτήματα παραμέτρων σχετικά με τη χρήση τους στο SQL ή το Microsoft Query. Ωστόσο, Power Query παράμετροι έχουν βασικές διαφορές:
- Οι παράμετροι μπορούν να χρησιμοποιηθούν σε οποιοδήποτε βήμα ερωτήματος. Εκτός από τη λειτουργία ως φίλτρο δεδομένων, οι παράμετροι μπορούν να χρησιμοποιηθούν για τον καθορισμό πραγμάτων όπως μια διαδρομή αρχείου ή ένα όνομα διακομιστή.
- Οι παράμετροι δεν ζητούν εισαγωγή. Αντί για αυτό, μπορείτε να αλλάξετε γρήγορα την τιμή τους χρησιμοποιώντας Power Query. Μπορείτε ακόμη και να αποθηκεύσετε και να ανακτήσετε τις τιμές από κελιά στο Excel.
- Οι παράμετροι αποθηκεύονται σε ένα απλό ερώτημα παραμέτρων, αλλά είναι ξεχωριστές από τα ερωτήματα δεδομένων στα οποία χρησιμοποιούνται. Αφού δημιουργήσετε, μπορείτε να προσθέσετε μια παράμετρο στα ερωτήματα, ανάλογα με τις ανάγκες.
Σημείωση Εάν θέλετε τον άλλο τρόπο για να δημιουργήσετε ερωτήματα παραμέτρων, ανατρέξτε στο θέμα Δημιουργία ερωτήματος παραμέτρων στο Microsoft Query.
Δημιουργία παραμέτρου
Μπορείτε να χρησιμοποιήσετε μια παράμετρο για να αλλάζετε αυτόματα μια τιμή σε ένα ερώτημα και να αποφύγετε την επεξεργασία του ερωτήματος κάθε φορά για να αλλάξετε την τιμή. Μπορείτε απλώς να αλλάξετε την τιμή της παραμέτρου. Μόλις δημιουργήσετε μια παράμετρο, αποθηκεύεται σε ένα ειδικό ερώτημα παραμέτρων το οποίο μπορείτε εύκολα να αλλάξετε απευθείας από το Excel.
Επιλέξτε δεδομένα>Λήψη δεδομένων>Άλλες προελεύσεις>εκκινούν πρόγραμμα επεξεργασίας Power Query.
Στο πρόγραμμα επεξεργασίας Power Query, επιλέξτε>Home Manage Parameters > New Parameters.
Στο παράθυρο διαλόγου Διαχείριση παραμέτρων , επιλέξτε Δημιουργία.
Ορίστε τα εξής, ανάλογα με τις ανάγκες:
Όνομα Αυτό θα πρέπει να αντικατοπτρίζει τη λειτουργία της παραμέτρου, αλλά να την διατηρεί όσο το δυνατόν συντομότερη. Περιγραφή Αυτό μπορεί να περιέχει λεπτομέρειες που θα βοηθήσουν τους χρήστες να χρησιμοποιήσουν σωστά την παράμετρο. Υποχρεωτικό Κάντε ένα από τα εξής:
Οποιαδήποτε τιμή Μπορείτε να εισαγάγετε οποιαδήποτε τιμή οποιουδήποτε τύπου δεδομένων στο ερώτημα παραμέτρων.
Λίστα τιμών Μπορείτε να περιορίσετε τις τιμές σε μια συγκεκριμένη λίστα εισάγοντάς τις στο μικρό πλέγμα. Πρέπει επίσης να επιλέξετε μια προεπιλεγμένη τιμή και μια τρέχουσα τιμή παρακάτω.
Ερώτημα Επιλέξτε ένα ερώτημα λίστας, που μοιάζει με μια δομημένη στήλη λίστας , διαχωρισμένη με κόμματα και μέσα σε αγκύλες.
Για παράδειγμα, ένα πεδίο κατάστασης θεμάτων μπορεί να έχει τρεις τιμές: {"Νέο", "Σε εξέλιξη", "Κλειστό"}. Πρέπει να δημιουργήσετε το ερώτημα λίστας εκ των προτέρων, ανοίγοντας την Προηγμένο πρόγραμμα επεξεργασίας (επιλέξτε "Κεντρική>Προηγμένο πρόγραμμα επεξεργασίας), καταργώντας το πρότυπο κώδικα, εισάγοντας τη λίστα τιμών στη μορφή λίστας ερωτημάτων και, στη συνέχεια, επιλέγοντας "Τέλος".
Αφού ολοκληρώσετε τη δημιουργία της παραμέτρου, το ερώτημα λίστας εμφανίζεται στις τιμές παραμέτρων.Τύπος Αυτό καθορίζει τον τύπο δεδομένων της παραμέτρου. Προτεινόμενες τιμές Εάν θέλετε, προσθέστε μια λίστα τιμών ή καθορίστε ένα ερώτημα για να δώσετε προτάσεις για εισαγωγή. Προεπιλεγμένη τιμή Αυτό εμφανίζεται μόνο εάν η επιλογή "Προτεινόμενες τιμές " έχει οριστεί σε "Λίστα τιμών" και καθορίζει ποιο στοιχείο λίστας είναι το προεπιλεγμένο. Σε αυτήν την περίπτωση, πρέπει να επιλέξετε μια προεπιλογή. Τρέχουσα αξία Ανάλογα με το πού χρησιμοποιείτε την παράμετρο, εάν αυτή είναι κενή, το ερώτημα μπορεί να μην επιστρέψει κανένα αποτέλεσμα. Εάν είναι επιλεγμένο το στοιχείο "Απαιτείται ", η τρέχουσα τιμή δεν μπορεί να είναι κενή. Για να δημιουργήσετε την παράμετρο, επιλέξτε OK.
Χρήση παραμέτρου για αλλαγή προέλευσης δεδομένων
Ακολουθεί ένας τρόπος για να διαχειριστείτε τις αλλαγές στις θέσεις των προελεύσεων δεδομένων και να αποτρέψετε σφάλματα ανανέωσης. Για παράδειγμα, αν υποθέσουμε ότι υπάρχει παρόμοιο σχήμα και προέλευση δεδομένων, δημιουργήστε μια παράμετρο, για να αλλάξετε εύκολα μια προέλευση δεδομένων και να αποφύγετε σφάλματα ανανέωσης δεδομένων. Ορισμένες φορές αλλάζει ο διακομιστής, η βάση δεδομένων, ο φάκελος, το όνομα αρχείου ή η θέση. Ίσως ένας διαχειριστής βάσης δεδομένων περιστασιακά αλλάζει ένα διακομιστή, μια μηνιαία πτώση αρχείων CSV πηγαίνει σε διαφορετικό φάκελο ή πρέπει εύκολα να κάνετε εναλλαγή μεταξύ ενός περιβάλλοντος ανάπτυξης / δοκιμής / παραγωγής.
Βήμα 1: Δημιουργία ερωτήματος παραμέτρων
Στο παρακάτω παράδειγμα, έχετε πολλά αρχεία CSV που εισάγετε χρησιμοποιώντας τη λειτουργία φακέλου εισαγωγής (επιλέξτε Data>Get Data>From Files>From Folder) από το φάκελο C:\DataFilesCSV1. Αλλά μερικές φορές ένας διαφορετικός φάκελος χρησιμοποιείται περιστασιακά ως θέση για την απόθεση των αρχείων, C:\DataFilesCSV2. Μπορείτε να χρησιμοποιήσετε μια παράμετρο σε ένα ερώτημα ως τιμή υποκατάστασης για τον διαφορετικό φάκελο.
Επιλέξτε Home>Διαχείριση παραμέτρων>Νέα παράμετρος.
Εισαγάγετε τις ακόλουθες πληροφορίες στο παράθυρο διαλόγου "Διαχείριση παραμέτρων ":
Όνομα CSVFileDrop Περιγραφή Εναλλακτική θέση απόθεσης αρχείων Υποχρεωτικό Ναι Τύπος Text Προτεινόμενες τιμές Οποιαδήποτε τιμή Τρέχουσα αξία C:\DataFilesCSV1 Επιλέξτε OK.
Βήμα 2: Προσθήκη της παραμέτρου στο ερώτημα δεδομένων
- Για να ορίσετε το όνομα του φακέλου ως παράμετρο, στις "Ρυθμίσεις ερωτήματος", στην περιοχή "Βήματα ερωτήματος", επιλέξτε "Προέλευση" και, στη συνέχεια, επιλέξτε "Επεξεργασία ρυθμίσεων".
- Βεβαιωθείτε ότι η επιλογή Διαδρομή αρχείου έχει οριστεί σε Παράμετρο και, στη συνέχεια, επιλέξτε την παράμετρο που μόλις δημιουργήσατε από την αναπτυσσόμενη λίστα.
- Επιλέξτε OK.
Βήμα 3: Ενημερώστε την τιμή της παραμέτρου
Η θέση του φακέλου μόλις άλλαξε, επομένως τώρα μπορείτε απλώς να ενημερώσετε το ερώτημα παραμέτρων.
- Επιλέξτε τηνκαρτέλα Συνδέσεις δεδομένων >& Ερωτήματα ερωτημάτων>, κάντε δεξί κλικ στο ερώτημα παραμέτρων και, στη συνέχεια, επιλέξτε Επεξεργασία.
- Εισαγάγετε τη νέα θέση στο πλαίσιο "Τρέχουσα τιμή ", όπως C:\DataFilesCSV2.
- Επιλέξτε Home>Close & Load.
- Για να επιβεβαιώσετε τα αποτελέσματά σας, προσθέστε νέα δεδομένα στην προέλευση δεδομένων και, στη συνέχεια, ανανεώστε το ερώτημα δεδομένων με την ενημερωμένη παράμετρο (Επιλογή ανανέωσης όλων>).
Χρήση παραμέτρου για φιλτράρισμα δεδομένων
Μερικές φορές θέλετε έναν εύκολο τρόπο για να αλλάξετε το φίλτρο ενός ερωτήματος ώστε να λαμβάνει διαφορετικά αποτελέσματα, χωρίς να χρειάζεται να επεξεργαστείτε το ερώτημα ή να δημιουργήσετε ελαφρώς διαφορετικά αντίγραφα του ίδιου ερωτήματος. Σε αυτό το παράδειγμα, αλλάζουμε μια ημερομηνία για να αλλάξουμε εύκολα ένα φίλτρο δεδομένων.
Για να ανοίξετε ένα ερώτημα, εντοπίστε ένα που φορτώθηκε προηγουμένως από το πρόγραμμα επεξεργασίας Power Query, επιλέξτε ένα κελί στα δεδομένα και, στη συνέχεια, επιλέξτε "Επεξεργασία ερωτήματος>". Για περισσότερες πληροφορίες, ανατρέξτε στο θέμα Δημιουργία, φόρτωση ή επεξεργασία ερωτήματος στο Excel.
Επιλέξτε το βέλος φίλτρου σε οποιαδήποτε κεφαλίδα στήλης για να φιλτράρετε τα δεδομένα σας και, στη συνέχεια, επιλέξτε μια εντολή φίλτρου, όπως Ημερομηνία/Ώρα Φίλτρα>μετά. Εμφανίζεται το παράθυρο διαλόγου "Γραμμές φίλτρου ".
Επιλέξτε το κουμπί στα αριστερά του πλαισίου "Τιμή " και, στη συνέχεια, κάντε ένα από τα εξής:
- Για να χρησιμοποιήσετε μια υπάρχουσα παράμετρο, επιλέξτε Παράμετρος και, στη συνέχεια, επιλέξτε την παράμετρο που θέλετε από τη λίστα που εμφανίζεται στα δεξιά.
- Για να χρησιμοποιήσετε μια νέα παράμετρο, επιλέξτε "Νέα παράμετρος" και, στη συνέχεια, δημιουργήστε μια παράμετρο.
Εισαγάγετε τη νέα ημερομηνία στο πλαίσιο Τρέχουσα τιμή και, στη συνέχεια, επιλέξτε Κλείσιμο οικίας>& Φόρτωση.
Για να επιβεβαιώσετε τα αποτελέσματά σας, προσθέστε νέα δεδομένα στην προέλευση δεδομένων και, στη συνέχεια, ανανεώστε το ερώτημα δεδομένων με την ενημερωμένη παράμετρο (Επιλογή ανανέωσης όλων>). Για παράδειγμα, αλλάξτε την τιμή του φίλτρου σε διαφορετική ημερομηνία για να δείτε νέα αποτελέσματα.
Εισαγάγετε τη νέα ημερομηνία στο πλαίσιο Τρέχουσα τιμή .
Επιλέξτε Home>Close & Load.
Για να επιβεβαιώσετε τα αποτελέσματά σας, προσθέστε νέα δεδομένα στην προέλευση δεδομένων και, στη συνέχεια, ανανεώστε το ερώτημα δεδομένων με την ενημερωμένη παράμετρο (Επιλογή ανανέωσης όλων>).
Χρήση μιας τιμής κελιού για φιλτράρισμα δεδομένων
Σε αυτό το παράδειγμα, η τιμή στην παράμετρο ερωτήματος διαβάζεται από ένα κελί στο βιβλίο εργασίας σας. Δεν χρειάζεται να αλλάξετε το ερώτημα παραμέτρων, απλώς ενημερώστε την τιμή του κελιού. Για παράδειγμα, θέλετε να φιλτράρετε μια στήλη κατά το πρώτο γράμμα, αλλά να αλλάξετε εύκολα την τιμή σε οποιοδήποτε γράμμα από το A έως το Z.
Στο φύλλο εργασίας ενός βιβλίου εργασίας όπου έχει φορτωθεί το ερώτημα που θέλετε να φιλτράρετε, δημιουργήστε έναν πίνακα του Excel με δύο κελιά: μια κεφαλίδα και μια τιμή.
MyFilter G Επιλέξτε ένα κελί στον πίνακα του Excel και, στη συνέχεια, επιλέξτε "Λήψη δεδομένων>>από πίνακα/περιοχή". Εμφανίζεται η πρόγραμμα επεξεργασίας Power Query.
Στο πλαίσιο "Όνομα " του παραθύρου " Ρυθμίσεις ερωτήματος " στα δεξιά, αλλάξτε το όνομα του ερωτήματος με πιο χαρακτηριστικό, όπως FilterCellValue.
Για να μεταβιβάσετε την τιμή στον πίνακα και όχι τον ίδιο τον πίνακα, κάντε δεξί κλικ στην τιμή στην Προεπισκόπηση δεδομένων και, στη συνέχεια, επιλέξτε Διερεύνηση.
Παρατηρήστε ότι ο τύπος άλλαξε σε= #"Changed Type"{0}[MyFilter]
Όταν χρησιμοποιείτε τον πίνακα του Excel ως φίλτρο στο βήμα 10, η Power Query αναφέρει την τιμή του πίνακα ως συνθήκη φίλτρου. Μια άμεση αναφορά στον πίνακα του Excel θα προκαλούσε σφάλμα.> Επιλέξτε Home Close & Load>Close & Load To. Τώρα έχετε μια παράμετρο ερωτήματος με όνομα "FilterCellValue" που χρησιμοποιήσατε στο βήμα 12.
Στο παράθυρο διαλόγου "Εισαγωγή δεδομένων ", επιλέξτε "Δημιουργία σύνδεσης μόνο" και, στη συνέχεια, κάντε κλικ στο κουμπί OK.
Ανοίξτε το ερώτημα που θέλετε να φιλτράρετε με την τιμή στον πίνακα FilterCellValue, η οποία είχε φορτωθεί προηγουμένως από την πρόγραμμα επεξεργασίας Power Query, επιλέγοντας ένα κελί στα δεδομένα και, στη συνέχεια, επιλέγοντας "Επεξεργασία ερωτήματος>". Για περισσότερες πληροφορίες, ανατρέξτε στο θέμα Δημιουργία, φόρτωση ή επεξεργασία ερωτήματος στο Excel.
Επιλέξτε το βέλος φίλτρου σε οποιαδήποτε κεφαλίδα στήλης για να φιλτράρετε τα δεδομένα σας και, στη συνέχεια, επιλέξτε μια εντολή φίλτρου, όπως Τα φίλτρα> κειμένουαρχίζουν με. Εμφανίζεται το παράθυρο διαλόγου "Γραμμές φίλτρου ".
Εισαγάγετε οποιαδήποτε τιμή στο πλαίσιο Τιμή , όπως "Ρ" και, στη συνέχεια, επιλέξτε OK. Σε αυτήν την περίπτωση, η τιμή είναι ένα προσωρινό σύμβολο κράτησης θέσης για την τιμή στον πίνακα FilterCellValue που εισάγετε στο επόμενο βήμα.
Επιλέξτε το βέλος στη δεξιά πλευρά της γραμμής τύπων για να εμφανίσετε ολόκληρο τον τύπο. Ακολουθεί ένα παράδειγμα μιας συνθήκης φίλτρου σε έναν τύπο:
= Table.SelectRows(#"Changed Type", each Text.StartsWith([Name], "G"))
Επιλέξτε την τιμή του φίλτρου. Στον τύπο, επιλέξτε "G".
Χρησιμοποιώντας το M Intellisense, πληκτρολογήστε τα πρώτα γράμματα του πίνακα FilterCellValue που δημιουργήσατε και, στη συνέχεια, επιλέξτε τον από τη λίστα που εμφανίζεται.
Επιλέξτε Αρχική>σελίδα, Κλείσιμο>, Κλείσιμο & Φόρτωση.
Αποτέλεσμα
Το ερώτημά σας χρησιμοποιεί τώρα την τιμή του πίνακα του Excel που δημιουργήσατε για να φιλτράρει τα αποτελέσματα του ερωτήματος. Για να χρησιμοποιήσετε μια νέα τιμή, επεξεργαστείτε τα περιεχόμενα του κελιού στον αρχικό πίνακα του Excel στο βήμα 1, αλλάξτε το "G" σε "V" και, στη συνέχεια, ανανεώστε το ερώτημα.
Έλεγχος της χρήσης ερωτημάτων παραμέτρων
Μπορείτε να ελέγξετε αν επιτρέπονται ή όχι τα ερωτήματα παραμέτρων.
- Στην πρόγραμμα επεξεργασίας Power Query, επιλέξτε Επιλογές αρχείου>και Επιλογές>ερωτήματος ρυθμίσεων >πρόγραμμα επεξεργασίας Power Query.
- Στο τμήμα παραθύρου στα αριστερά, στην περιοχή ΚΑΘΟΛΙΚΟ, επιλέξτε πρόγραμμα επεξεργασίας Power Query.
- Στο παράθυρο στα δεξιά, στην περιοχή Παράμετροι, επιλέξτε ή καταργήστε την επιλογή Να επιτρέπεται πάντα η παραμετροποίηση στα παράθυρα διαλόγου προέλευσης δεδομένων και μετασχηματισμού.