Παραμετροποίηση διαδρομών δεδομένων στο Power Query

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

Τι να κάνετε σε μια τέτοια κατάσταση; Ας δούμε αυτή την περίπτωση πιο αναλυτικά με το παρακάτω παράδειγμα.

Διατύπωση του προβλήματος

Ας υποθέσουμε ότι έχουμε στο φάκελο E:Αναφορές πωλήσεων βρίσκεται το αρχείο Τα κορυφαία 100 προϊόντα.xls, το οποίο είναι μια μεταφόρτωση από την εταιρική μας βάση δεδομένων ή το σύστημα ERP (1C, SAP, κ.λπ.) Αυτό το αρχείο περιέχει πληροφορίες σχετικά με τα πιο δημοφιλή είδη βασικών προϊόντων και έχει την εξής μορφή:

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

Επομένως, δίπλα σε αυτό το αρχείο στον ίδιο φάκελο, δημιουργούμε ένα άλλο νέο αρχείο Χειριστής.xlsx, στο οποίο θα δημιουργήσουμε ένα ερώτημα Power Query που θα φορτώσει άσχημα δεδομένα από το αρχείο μεταφόρτωσης προέλευσης Τα κορυφαία 100 προϊόντα.xlsκαι βάλτε τα στη σειρά:

Υποβολή αιτήματος σε εξωτερικό αρχείο

Άνοιγμα του αρχείου Χειριστής.xlsx, επιλέξτε στην καρτέλα ημερομηνία εντολή Λήψη δεδομένων – Από αρχείο – Από βιβλίο εργασίας του Excel (Δεδομένα — Λήψη δεδομένων — Από αρχείο — Από Excel), στη συνέχεια καθορίστε τη θέση του αρχείου προέλευσης και του φύλλου που χρειαζόμαστε. Τα επιλεγμένα δεδομένα θα φορτωθούν στο πρόγραμμα επεξεργασίας Power Query:

Ας τα επαναφέρουμε στην κανονικότητα:

1. Διαγραφή κενών γραμμών με Αρχική — Διαγραφή γραμμών — Διαγραφή κενών γραμμών (Αρχική — Κατάργηση σειρών — Κατάργηση κενών σειρών).
2. Διαγράψτε τις περιττές κορυφαίες 4 γραμμές Αρχική σελίδα — Διαγραφή σειρών — Διαγραφή κορυφαίων σειρών (Αρχική — Κατάργηση σειρών — Κατάργηση κορυφαίων σειρών).
3. Ανυψώστε την πρώτη σειρά στην κεφαλίδα του πίνακα με το κουμπί Χρησιμοποιήστε την πρώτη γραμμή ως κεφαλίδες αυτί Αρχική (Αρχική σελίδα — Χρήση πρώτης σειράς ως κεφαλίδας).
4. Διαχωρίστε το πενταψήφιο άρθρο από το όνομα προϊόντος στη δεύτερη στήλη χρησιμοποιώντας την εντολή χωρισμένη στήλη αυτί Μεταμόρφωση (Μετατροπή — Διαίρεση στήλης).
5. Διαγράψτε τις περιττές στήλες και μετονομάστε τις επικεφαλίδες των υπόλοιπων για καλύτερη ορατότητα.

Ως αποτέλεσμα, θα πρέπει να έχουμε την ακόλουθη, πολύ πιο ευχάριστη εικόνα:

Απομένει να ανεβάσουμε αυτόν τον εξευγενισμένο πίνακα πίσω στο φύλλο του αρχείου μας Χειριστής.xlsx η ομάδα κλείστε και κατεβάστε (Αρχική — Κλείσιμο & Φόρτωση) αυτί Αρχική:

Εύρεση της διαδρομής προς ένα αρχείο σε ένα αίτημα

Ας δούμε τώρα πώς φαίνεται το ερώτημά μας "κάτω από την κουκούλα", στην εσωτερική γλώσσα που είναι ενσωματωμένη στο Power Query με το συνοπτικό όνομα "M". Για να το κάνετε αυτό, επιστρέψτε στο ερώτημά μας κάνοντας διπλό κλικ σε αυτό στο δεξί παράθυρο Αιτήματα και συνδέσεις και στην καρτέλα Βαθμολογία Κριτικής Choose Advanced Editor (Προβολή — Σύνθετη επεξεργασία):

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

Προσθέστε έναν έξυπνο πίνακα με μια διαδρομή αρχείου

Ας κλείσουμε προς το παρόν το Power Query και ας επιστρέψουμε στο αρχείο μας Χειριστής.xlsx. Ας προσθέσουμε ένα νέο κενό φύλλο και ας φτιάξουμε έναν μικρό «έξυπνο» πίνακα σε αυτό, στο μοναδικό κελί του οποίου θα γραφεί η πλήρης διαδρομή προς το αρχείο δεδομένων προέλευσης:

Για να δημιουργήσετε έναν έξυπνο πίνακα από ένα κανονικό εύρος, μπορείτε να χρησιμοποιήσετε τη συντόμευση πληκτρολογίου Ctrl+T ή κουμπί Μορφοποίηση ως πίνακα αυτί Αρχική (Αρχική σελίδα — Μορφοποίηση ως πίνακα). Η επικεφαλίδα της στήλης (κελί A1) μπορεί να είναι απολύτως οτιδήποτε. Σημειώστε επίσης ότι για λόγους σαφήνειας έδωσα στον πίνακα ένα όνομα παράμετροι αυτί Κατασκευαστής (Σχέδιο).

Η αντιγραφή μιας διαδρομής από τον Explorer ή ακόμη και η μη αυτόματη εισαγωγή της δεν είναι, φυσικά, ιδιαίτερα δύσκολη, αλλά είναι καλύτερο να ελαχιστοποιήσετε τον ανθρώπινο παράγοντα και να προσδιορίσετε τη διαδρομή, αν είναι δυνατόν, αυτόματα. Αυτό μπορεί να υλοποιηθεί χρησιμοποιώντας την τυπική συνάρτηση φύλλου εργασίας του Excel CELL (ΚΥΤΤΑΡΟ), το οποίο μπορεί να δώσει μια δέσμη χρήσιμων πληροφοριών σχετικά με το κελί που καθορίζεται ως όρισμα – συμπεριλαμβανομένης της διαδρομής προς το τρέχον αρχείο:

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

=LEFT(CELL("όνομα αρχείου");FIND("[";CELL("όνομα αρχείου"))-1)&"Τα κορυφαία 100 προϊόντα.xls"

ή στην αγγλική έκδοση:

=LEFT(CELL(«όνομα αρχείου»);FIND(«[«;CELL(«όνομα αρχείου»))-1)&»Топ-100 товаров.xls»

… πού είναι η λειτουργία ΛΕΒΣΙΜΒ (ΑΡΙΣΤΕΡΑ) παίρνει ένα κομμάτι κειμένου από τον πλήρη σύνδεσμο μέχρι την αρχική αγκύλη (δηλαδή τη διαδρομή προς τον τρέχοντα φάκελο) και στη συνέχεια επικολλάται το όνομα και η επέκταση του αρχείου δεδομένων προέλευσης.

Παραμετροποιήστε τη διαδρομή στο ερώτημα

Το τελευταίο και πιο σημαντικό άγγιγμα παραμένει – για να γράψετε τη διαδρομή προς το αρχείο προέλευσης στο αίτημα Τα κορυφαία 100 προϊόντα.xls, αναφερόμενος στο κελί A2 του «έξυπνου» πίνακα που δημιουργήσαμε παράμετροι.

Για να το κάνουμε αυτό, ας επιστρέψουμε στο ερώτημα Power Query και ας το ανοίξουμε ξανά Advanced Editor αυτί Βαθμολογία Κριτικής (Προβολή — Σύνθετη επεξεργασία). Αντί για μια διαδρομή συμβολοσειράς κειμένου σε εισαγωγικά “E:Sales ReportsTop 100 products.xlsx” Ας παρουσιάσουμε την ακόλουθη δομή:

Excel.CurrentWorkbook(){[Name=”Settings”]}[Περιεχόμενο]0 {}[Διαδρομή προς τα δεδομένα πηγής]

Ας δούμε από τι αποτελείται:

• Excel.CurrentWorkbook() είναι μια συνάρτηση της γλώσσας M για την πρόσβαση στα περιεχόμενα του τρέχοντος αρχείου
• {[Name=”Settings”]}[Περιεχόμενο] – αυτή είναι μια παράμετρος βελτίωσης της προηγούμενης συνάρτησης, που υποδεικνύει ότι θέλουμε να λάβουμε τα περιεχόμενα του «έξυπνου» πίνακα παράμετροι
• [Διαδρομή προς τα δεδομένα πηγής] είναι το όνομα της στήλης στον πίνακα παράμετροιστο οποίο αναφερόμαστε
  
• 0 {} είναι ο αριθμός σειράς στον πίνακα παράμετροιαπό το οποίο θέλουμε να πάρουμε δεδομένα. Το καπάκι δεν μετράει και η αρίθμηση ξεκινά από το μηδέν, όχι από το ένα.

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

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

• Τι είναι το Power Query και γιατί χρειάζεται όταν εργάζεστε στο Microsoft Excel
• Πώς να εισαγάγετε ένα απόσπασμα κυμαινόμενου κειμένου στο Power Query
• Επανασχεδιασμός XNUMXD Crosstab σε επίπεδο πίνακα με Power Query