Χρήση προσαρμοσμένων συναρτήσεων

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

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

Αυτό το άρθρο εστιάζει σε αυτήν την εμπειρία, η οποία παρέχεται μόνο μέσω του περιβάλλοντος εργασίας χρήστη Power Query και στον τρόπο αξιοποίησης της.

Σημαντικό

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

Δημιουργία προσαρμοσμένης συνάρτησης από αναφορά πίνακα

Σημείωμα

Το παρακάτω παράδειγμα δημιουργήθηκε με χρήση της εμπειρίας επιφάνειας εργασίας που βρέθηκε στο Power BI Desktop και μπορείτε επίσης να την παρακολουθείτε χρησιμοποιώντας την εμπειρία Power Query που βρέθηκε στο Excel για Windows.

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

Ξεκινήστε χρησιμοποιώντας την εμπειρία σύνδεσης Φάκελος για να μεταβείτε στον φάκελο όπου βρίσκονται τα αρχεία σας και επιλέξτε Μετασχηματισμός δεδομένων ή Επεξεργασία. Αυτά τα βήματα θα σας μεταφέρουν στην εμπειρία Power Query. Κάντε δεξί κλικ στη δυαδική τιμή της επιλογής σας από το πεδίο Περιεχόμενο και επιλέξτε Προσθήκη ως νέο ερώτημα. Για αυτό το παράδειγμα, η επιλογή έγινε για το πρώτο αρχείο από τη λίστα, το οποίο τυχαίνει να είναι το αρχείο Απρίλιος 2019.csv.

Αυτή η επιλογή δημιουργεί αποτελεσματικά ένα νέο ερώτημα με ένα βήμα περιήγησης απευθείας σε αυτό το αρχείο ως δυαδικό και το όνομα αυτού του νέου ερωτήματος είναι η διαδρομή αρχείου του επιλεγμένου αρχείου. Μετονομάστε αυτό το ερώτημα σε Δείγμα αρχείου.

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

Σημείωμα

Συνιστούμε να διαβάσετε το άρθρο σχετικά με τις Παραμέτρους για να κατανοήσετε καλύτερα τον τρόπο δημιουργίας και διαχείρισης παραμέτρων στο Power Query.

Οι προσαρμοσμένες συναρτήσεις μπορούν να δημιουργηθούν χρησιμοποιώντας οποιονδήποτε τύπο παραμέτρων. Δεν απαιτείται οποιαδήποτε προσαρμοσμένη συνάρτηση να έχει μια δυαδική τιμή ως παράμετρο.

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

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

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

Μετονομάστε το ερώτημα που μόλις δημιουργήθηκε από παράμετρο αρχείου (2) σε μετασχηματισμό δείγματος αρχείου.

Κάντε δεξί κλικ σε αυτό το νέο ερώτημα Μετασχηματισμός δείγματος αρχείου και επιλέξτε Δημιουργία συνάρτησης .

Αυτή η λειτουργία δημιουργεί αποτελεσματικά μια νέα συνάρτηση που συνδέεται με το ερώτημα Μετασχηματισμός δείγματος αρχείου . Οποιεσδήποτε αλλαγές κάνετε στο ερώτημα Μετασχηματισμός δείγματος αρχείου αντιγράφονται αυτόματα στην προσαρμοσμένη συνάρτηση. Κατά τη δημιουργία αυτής της νέας συνάρτησης, χρησιμοποιήστε το αρχείο Transform ως όνομα συνάρτησης.

Αφού δημιουργήσετε τη συνάρτηση, παρατηρήστε ότι δημιουργείται μια νέα ομάδα για εσάς με το όνομα της συνάρτησης. Αυτή η νέα ομάδα περιέχει:

• Όλες οι παράμετροι που αναφέρονται στο ερώτημα αρχείου "Δείγμα μετασχηματισμού" .
• Το ερώτημα αρχείου "Μετασχηματισμός δείγματος ", γνωστό συνήθως ως δείγμα ερωτήματος.
• Η συνάρτηση που μόλις δημιουργήσατε, σε αυτή την περίπτωση , Αρχείο μετασχηματισμού.

Εφαρμογή μετασχηματισμών σε δείγμα ερωτήματος

Με τη νέα συνάρτηση που δημιουργήσατε, επιλέξτε το ερώτημα με το όνομα Αρχείο μετασχηματισμού δείγματος. Αυτό το ερώτημα είναι πλέον συνδεδεμένο με τη συνάρτηση Μετασχηματισμός αρχείου , επομένως τυχόν αλλαγές που έγιναν σε αυτό το ερώτημα αντικατοπτρίζονται στη συνάρτηση. Αυτή η σύνδεση είναι γνωστή ως η έννοια ενός δείγματος ερωτήματος που συνδέεται με μια συνάρτηση.

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

Η μορφή όλων των αρχείων CSV στον φάκελο είναι ίδια. Έχουν όλες μια κεφαλίδα που εκτείνεται στις τέσσερις πρώτες γραμμές. Οι κεφαλίδες στηλών βρίσκονται στη γραμμή 5 και τα δεδομένα ξεκινούν από τη γραμμή 6 προς τα κάτω, όπως φαίνεται στην επόμενη εικόνα.

Το επόμενο σύνολο βημάτων μετασχηματισμού που πρέπει να εφαρμοστούν στο αρχείο μετασχηματισμού δείγματος είναι:

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

   

   Σημείωμα

   Για να μάθετε περισσότερα σχετικά με το πώς μπορείτε να καταργήσετε γραμμές ή να φιλτράρετε έναν πίνακα κατά θέση γραμμής, μεταβείτε στην επιλογή Φιλτράρισμα κατά θέση γραμμής.

2. Προβιβασμός κεφαλίδων — Οι κεφαλίδες για τον τελικό πίνακα βρίσκονται τώρα στην πρώτη γραμμή του πίνακα. Μπορείτε να τις προβιβάσετε όπως φαίνεται στην επόμενη εικόνα.

   

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

Σημείωμα

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

Προσοχή

Η συνάρτηση Μετασχηματισμού αρχείου βασίζεται στα βήματα που εκτελούνται στο ερώτημα Μετασχηματισμός δείγματος αρχείου . Ωστόσο, εάν προσπαθήσετε να τροποποιήσετε με μη αυτόματο τρόπο τον κώδικα για τη συνάρτηση Transform file , θα εμφανιστεί μια προειδοποίηση που αναφέρει The definition of the function 'Transform file' is updated whenever query 'Transform Sample file' is updated. However, updates will stop if you directly modify function 'Transform file'.

Κλήση προσαρμοσμένης συνάρτησης ως νέας στήλης

Έχοντας δημιουργήσει τώρα την προσαρμοσμένη συνάρτηση και ενσωματώσει όλα τα βήματα μετασχηματισμού, μπορείτε να επιστρέψετε στο αρχικό ερώτημα όπου έχετε τη λίστα των αρχείων από τον φάκελο (αρχεία CSV σε αυτό το παράδειγμα). Μέσα στην καρτέλα Προσθήκη στήλης στην κορδέλα, επιλέξτε Κλήση προσαρμοσμένης συνάρτησης από την ομάδα Γενικά . Μέσα στο παράθυρο Κλήση προσαρμοσμένης συνάρτησης , πληκτρολογήστε Πίνακας εξόδου ως το Νέο όνομα στήλης. Επιλέξτε το όνομα της συνάθησής σας, Μετασχηματισμός αρχείου, από την αναπτυσσόμενη λίστα Ερώτημα συνάρτησης. Αφού επιλέξετε τη συνάρτηση από το αναπτυσσόμενο μενού, εμφανίζεται η παράμετρος για τη συνάρτηση και μπορείτε να επιλέξετε ποια στήλη από τον πίνακα θα χρησιμοποιηθεί ως όρισμα για αυτήν τη συνάρτηση. Επιλέξτε τη στήλη Περιεχόμενο ως την τιμή/όρισμα που θα διαβιβαστεί για την παράμετρο αρχείου.

Αφού επιλέξετε OK, δημιουργείται μια νέα στήλη με το όνομα Πίνακας εξόδου. Αυτή η στήλη έχει τιμές πίνακα στα κελιά της, όπως φαίνεται στην επόμενη εικόνα. Για λόγους ευκολίας, καταργήστε όλες τις στήλες από αυτόν τον πίνακα εκτός από τις στήλες Name και Output Table.

Σημείωμα

Για να μάθετε περισσότερα σχετικά με τον τρόπο επιλογής ή κατάργησης στηλών από έναν πίνακα, μεταβείτε στην επιλογή Επιλογή ή κατάργηση στηλών.

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

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

Σημείωμα

Αυτό που έχετε διαβάσει μέχρι στιγμής είναι ουσιαστικά η ίδια διαδικασία που συμβαίνει κατά τη διάρκεια της εμπειρίας Συνδυασμός αρχείων , αλλά γίνεται με μη αυτόματο τρόπο.

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

Προσθήκη νέας παραμέτρου σε υπάρχουσα προσαρμοσμένη συνάρτηση

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

Για να πραγματοποιηθεί αυτή η απαίτηση, δημιουργήστε μια νέα παράμετρο που ονομάζεται Market με τύπο δεδομένων κειμένου. Για την Τρέχουσα τιμή, εισαγάγετε την τιμή Παναμάς.

Με αυτήν τη νέα παράμετρο, επιλέξτε το ερώτημα Μετασχηματισμός δείγματος αρχείου και φιλτράρετε το πεδίο Χώρα χρησιμοποιώντας την τιμή από την παράμετρο Αγορά .

Σημείωμα

Για να μάθετε περισσότερα σχετικά με τον τρόπο φιλτραρίσματος στηλών κατά τιμές, μεταβείτε στην ενότητα Φιλτράρισμα τιμών.

Η εφαρμογή αυτού του νέου βήματος στο ερώτημά σας ενημερώνει αυτόματα τη συνάρτηση Μετασχηματισμός αρχείου , η οποία απαιτεί τώρα δύο παραμέτρους με βάση τις δύο παραμέτρους που χρησιμοποιεί το αρχείο "Μετασχηματισμός δείγματος".

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

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

Τώρα, μπορείτε να επιστρέψετε στον Αναπτυγμένο πίνακα εξόδου στα Εφαρμοσμένα βήματα. Ελέγξτε το ερώτημά σας για να επικυρώσετε ότι μόνο οι γραμμές όπου η Χώρα είναι ίση με τον Παναμά εμφανίζονται στο τελικό σύνολο αποτελεσμάτων του ερωτήματος CSV Files .

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

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

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

κωδικός
PTY-CM1090-LAX
LAX-CM701-PTY
PTY-CM4441-MIA
MIA-UA1257-LAX
LAX-XY2842-MIA

Ξεκινάτε με την ύπαρξη μιας παραμέτρου που έχει μια τιμή που χρησιμεύει ως παράδειγμα. Για αυτήν την περίπτωση, είναι η τιμή PTY-CM1090-LAX.

Από αυτή την παράμετρο, δημιουργείτε ένα νέο ερώτημα όπου εφαρμόζετε τους μετασχηματισμούς που χρειάζεστε. Για αυτήν την περίπτωση, θέλετε να διαιρέσετε τον κώδικα PTY-CM1090-LAX σε πολλαπλά στοιχεία:

• Προέλευση = PTY
• Προορισμός = LAX
• Αεροπορική εταιρεία = CM
• FlightID = 1090

Ο ακόλουθος κώδικας M δείχνει αυτό το σύνολο μετασχηματισμών.

let
    Source = code,
    SplitValues = Text.Split( Source, "-"),
    CreateRow = [Origin= SplitValues{0}, Destination= SplitValues{2}, Airline=Text.Start( SplitValues{1},2), FlightID= Text.End( SplitValues{1}, Text.Length( SplitValues{1} ) - 2) ],
    RowToTable = Table.FromRecords( {  CreateRow } ),
    #"Changed Type" = Table.TransformColumnTypes(RowToTable,{{"Origin", type text}, {"Destination", type text}, {"Airline", type text}, {"FlightID", type text}})
in
    #"Changed Type"

Σημείωμα

Για να μάθετε περισσότερα σχετικά με τη γλώσσα τύπων Power Query M, μεταβείτε στη γλώσσα τύπων Power Query M.

Στη συνέχεια, μπορείτε να μετασχηματίζετε αυτό το ερώτημα σε συνάρτηση, κάνοντας δεξί κλικ στο ερώτημα και επιλέγοντας Δημιουργία συνάρτησης. Τέλος, μπορείτε να καλέσετε την προσαρμοσμένη συνάρτηση σε οποιοδήποτε από τα ερωτήματα ή τις τιμές σας, όπως φαίνεται στην επόμενη εικόνα.

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