Το Power Query είναι αρκετά ισχυρό. Αλλά πρόκειται να κάνω Power Query Squared… να γράψω ένα ερώτημα για μία σελίδα και μετά να κάνω το Excel να εκτελέσει το ίδιο ερώτημα για μια ολόκληρη λίστα ιστοσελίδων.
Δες το βίντεο
- Το σημερινό κόλπο προσαρμόζεται από το βιβλίο M για Data Monkey
- Δημιουργήστε ένα ερώτημα για να λάβετε δεδομένα από μία ιστοσελίδα
- Επεξεργαστείτε το ερώτημα για να το αλλάξετε σε συνάρτηση με (VariableName) => πριν από το Let
- Αλλάξτε το σκληρό κωδικοποιημένο URL σε VariableName
- Μετονομάστε το ερώτημα σε fxWeather
- Κλείσιμο & φόρτωση. Τα δεδομένα θα εξαφανιστούν.
- Χρησιμοποιήστε το Excel Trickery για να δημιουργήσετε έναν πίνακα όλων των διευθύνσεων URL
- Δημιουργήστε ένα ερώτημα από αυτόν τον πίνακα.
- Προσθέστε μια νέα στήλη καιρού
=fxWeather((URL)) - Αναπτύξτε τη στήλη. Αποεπιλέξτε το πρόθεμα
- Φοβερο!
Μεταγραφή βίντεο
Μάθετε το Excel από το Podcast, Episode 2056: Power Query Squared
Γεια σας, καλώς ήλθατε πίσω στο netcast, είμαι ο Bill Jelen. Συνάντησα αυτό το τέχνασμα ενώ ετοίμαζα να κάνω ένα σεμινάριο σε ένα συνέδριο στο Ντάλας που ονομάζεται Excelapalooza, το μεγαλύτερο όνομα διάσκεψης του Excel ποτέ. Θα πρέπει να το ελέγχετε κάθε Σεπτέμβριο στο Ντάλας.
Και πιστέψτε στον Ken Puls και τον Miguel Escobar επειδή έπρεπε να κάνω μια ώρα για το ερώτημα ενέργειας, οπότε, φυσικά, έβγαλα ένα φοβερό βιβλίο, το μεγαλύτερο βιβλίο στον κόσμο για το Power Query. Περνούσα το βιβλίο και είδα ότι είχαν μια τεχνική και είπα: «Περιμένετε λίγο. Θα δω αν μπορώ να προσαρμόσω αυτήν την τεχνική. " Και αυτό για μένα είναι καταπληκτικό στο ερώτημα ισχύος. Και εδώ θα κάνουμε, θα πάρουμε ένα ερώτημα ενέργειας και μετά θα εκτελέσουμε αυτό το ερώτημα ενέργειας, αυτό το ερώτημα, δεκάδες φορές, εντάξει;
Έτσι, το παράδειγμα που βρήκα ήταν όπου ήθελα να αντλήσω δεδομένα από μια ιστοσελίδα, εντάξει. Και βγήκα - έψαχνα μερικές ιστοσελίδες που μπορούσα - που θα μπορούσα να χρησιμοποιήσω ως παράδειγμα. Κατέληξα στο Weather Underground και εδώ είναι η διεύθυνση URL, και μπορείτε να δείτε ότι ήμουν στο Ντάλας-Φορτ Γουόρθ, οπότε τραβάμε δεδομένα για το Ντάλας και μοιάζει με 2 Ιανουαρίου 2015. Λοιπόν, στη διεύθυνση URL υπάρχουν οι παράμετροι , σωστά? Και αυτό είναι ένα URL που είναι απλώς ώριμο για να κάνει αυτό το πράγμα να λειτουργεί.
Θα ρίξουμε μια γρήγορη ματιά στην ιστοσελίδα, αν και δεν είναι τόσο σημαντική. Βλέπετε ότι υπάρχουν πολλά διαφορετικά δεδομένα εδώ στην ιστοσελίδα και αποφάσισα ότι απλώς θα προσπαθούσα να πάρω βροχόπτωση και υψηλή και χαμηλή θερμοκρασία. Και εδώ είναι αυτό το ερώτημα. Και επιτρέψτε μου να σας πω εδώ ότι αυτό το podcast δεν αφορά τον τρόπο δημιουργίας αυτού του ερωτήματος, το νέο ερώτημα, από άλλες πηγές, από τον Ιστό, καθορίστε τη διεύθυνση URL και, στη συνέχεια, μια σειρά από βήματα που δεν πρόκειται να αναλύσω εδώ για να λάβω το δικό μου τελική απάντηση των Max Temp, Min Temp και υετού. Το θέμα είναι ότι θα πάρετε το δικό σας ερώτημα και θα το κάνετε για πολλά πράγματα.
Έτσι, κάνω κλικ στο Κλείσιμο και φόρτωση και αυτό το ερώτημα λειτουργεί, επιστρέφει τη μία σειρά μου Ολα είναι υπέροχα. Και θα επιστρέψω, θα επεξεργαστώ αυτό το ερώτημα και θα πάω στο View, Advanced Editor. Θα λάβω αυτό το ερώτημα και θα το κάνω να λειτουργεί, εντάξει; Λοιπόν εδώ ακριβώς πριν από τη λέξη LET, πατάω Enter. Και σε παρένθεση, θα της δώσω μια μεταβλητή (MyURL) και μετά => μικρό βέλος εκεί, εντάξει; Δροσερός. Και μετά κάτω, όπου έχουν τη διεύθυνση URL σε εισαγωγικά, θέλω να ξεφορτωθώ ολόκληρη τη διεύθυνση URL, συμπεριλαμβανομένων των εισαγωγικών και, στη συνέχεια, πληκτρολογήστε το μεταβλητό μου όνομα MyURL, εντάξει. Λοιπόν, αυτό που λέμε είναι, θα το μεταφέρουμε στη διεύθυνση URL και θα κάνει το ίδιο ερώτημα, αλλά με οποιαδήποτε διεύθυνση URL τυχαίνει να το περάσουμε.
Τώρα, μερικά δυσαρεστημένα πράγματα εδώ, όταν κάνω κλικ στο Τέλος, ω άντρες! Όλα τα εφαρμοσμένα μου βήματα έχουν φύγει και θέλουν να εισαγάγω μια παράμετρο. Απλά αγνοήστε όλα αυτά. Θα το μετονομάσουμε. θα το ονομάσουμε fxWeather. Το FX, φυσικά, είναι η συντομογραφία για τη λειτουργία και πρέπει να θυμάστε πραγματικά αυτό το όνομα και να θυμάστε ποια γράμματα είναι κεφαλαία, αυτό θα είναι πολύ σημαντικό σε λίγα λεπτά. Αρχική σελίδα, Κλείσιμο & φόρτωση και BAM! Όλα έχουν φύγει. Ωχ όχι! Αλλά αυτό είναι εντάξει. Εντάξει, έτσι ξέρουμε ότι είναι εκεί. Είναι μόνο μια σύνδεση. Τώρα, θα έρθω εδώ και αυτό είναι απλώς παλιό Excel, εντάξει; Λοιπόν, εδώ είναι το URL, το έσπασα στο πρώτο μέρος του URL, το τελικό μέρος του URL. Ξέρω ότι πρέπει να κάνω την ημερομηνία. Πρέπει να το μορφοποιήσω σε αυτήν την περίεργη μορφή του έτουςμήνα, και μέρα έτσι χρησιμοποίησα τη συνάρτηση TEXT για να το κάνω αυτό. Βάλτε την ημερομηνία έναρξης εδώ. Μπορώ ακόμη και να αλλάξω το αεροδρόμιο, οπότε τώρα είμαι πίσω και πίσω. Ας κάνουμε MCO για το Ορλάντο και ας κάνουμε μερικά πρόσφατα δεδομένα. Έτσι θα ξεκινήσω στις 10/1/2016, εντάξει. Τώρα λοιπόν έχουμε αυτό το υπέροχο μικρό τραπέζι εδώ. Και παρεμπιπτόντως, πρέπει να είναι τραπέζι. Πρέπει να χρησιμοποιήσετε τη μορφή ως πίνακα ή Ctrl + T. Λοιπόν, ξέρετε, αυτό απλά αρπάζει αυτήν την ημερομηνία και μετά +1 + 1 + 1. Το μορφοποιώ, δημιουργώ τη διεύθυνση URL.Πρέπει να χρησιμοποιήσετε τη μορφή ως πίνακα ή Ctrl + T. Λοιπόν, ξέρετε, αυτό απλά αρπάζει αυτήν την ημερομηνία και μετά +1 + 1 + 1. Το μορφοποιώ, δημιουργώ τη διεύθυνση URL.Πρέπει να χρησιμοποιήσετε τη μορφή ως πίνακα ή Ctrl + T. Λοιπόν, ξέρετε, αυτό απλά αρπάζει αυτήν την ημερομηνία και μετά +1 + 1 + 1. Το μορφοποιώ, δημιουργώ τη διεύθυνση URL.
Εντάξει τώρα, πρόκειται να δημιουργήσουμε ένα ερώτημα από αυτόν τον πίνακα. Εντάξει, και υπάρχουν οι πληροφορίες μου. Πρόκειται να προσθέσω μια νέα στήλη, να προσθέσω μια προσαρμοσμένη στήλη, οι στήλες επρόκειτο να ονομαστούν Weather, και ο τύπος θα είναι = fxWeather. Βεβαιωθείτε ότι είναι ακριβώς η ίδια περίπτωση, τα ίδια κεφαλαία και πεζά γράμματα και θα εισαγάγουμε το πεδίο που ονομάζεται URL όπως αυτό, κλείνοντας παρένθεση. Χωρίς σφάλματα σύνταξης, κάντε κλικ στο OK. Θέλουν να μάθουν για το απόρρητο εδώ, αυτά είναι όλα τα δημόσια δεδομένα, κάντε κλικ στην επιλογή Αποθήκευση, εντάξει. Λοιπόν, υπάρχει η ημερομηνία μας. Είναι αστείο που έχουν αλλάξει πραγματικά τη μορφή μου σε κάτι που δεν μοιάζει με αυτό που ξεκίνησα. Και μετά είναι ο Καιρός με το σύμβολο επέκτασης. Έτσι θα κάνω κλικ στο σύμβολο επέκτασης, καταργήστε την επιλογή Χρήση αρχικού ονόματος στήλης ως πρόθεμα. Θέλω το Μέγιστο, το Ελάχιστο, το Υετόςκάντε κλικ στο OK. Εντάξει, και τώρα το μόνο που χρειάζομαι είναι η ημερομηνία και αυτές οι πληροφορίες εκεί έξω. Θα κάνω δεξί κλικ και θα αφαιρέσω αυτήν τη στήλη, θα κάνω δεξί κλικ και θα αφαιρέσω αυτήν τη στήλη. Εδώ, δεν χρειάζομαι αυτόν τον χρόνο, οπότε θα πω ότι είναι απλώς μια ημερομηνία, εντάξει. Και δείτε τι κάνει κάθε ημερομηνία που το περνάω. επιστρέφει το υψηλό, το χαμηλό και την υετό για το Ορλάντο. Κάθε σειρά εδώ πηγαίνει σε μια διαφορετική ιστοσελίδα. Φανταστείτε, αν δεν ήταν 15 σειρές, αλλά 5.000 σειρές, θα το ρυθμίσατε για να τρέχει όλη τη νύχτα. Συνήθιζα να γράφω μακροεντολές για αυτό. Στην πραγματικότητα, μία από τις ιστοσελίδες είναι πώς να δημιουργήσετε μια μακροεντολή για να αποκόψετε ιστοσελίδες από χίλιες διαφορετικές ιστοσελίδες σε έναν ιστότοπο, δεν είναι πλέον απαραίτητο με το ερώτημα ενέργειας.Κάντε δεξί κλικ και αφαιρέστε αυτήν τη στήλη, κάντε δεξί κλικ και αφαιρέστε αυτήν τη στήλη. Εδώ, δεν χρειάζομαι αυτόν τον χρόνο, οπότε θα πω ότι είναι απλώς μια ημερομηνία, εντάξει. Και δείτε τι κάνει κάθε ημερομηνία που το περνάω. επιστρέφει το υψηλό, το χαμηλό και την υετό για το Ορλάντο. Κάθε σειρά εδώ πηγαίνει σε μια διαφορετική ιστοσελίδα. Φανταστείτε, αν δεν ήταν 15 σειρές, αλλά 5.000 σειρές, θα το ρυθμίσατε για να τρέχει όλη τη νύχτα. Συνήθιζα να γράφω μακροεντολές για αυτό. Στην πραγματικότητα, μία από τις ιστοσελίδες είναι πώς να δημιουργήσετε μια μακροεντολή για να αποκόψετε ιστοσελίδες από χίλιες διαφορετικές ιστοσελίδες σε έναν ιστότοπο, δεν είναι πλέον απαραίτητο με το ερώτημα ενέργειας.Κάντε δεξί κλικ και αφαιρέστε αυτήν τη στήλη, κάντε δεξί κλικ και αφαιρέστε αυτήν τη στήλη. Εδώ, δεν χρειάζομαι αυτόν τον χρόνο, οπότε θα πω ότι είναι απλώς μια ημερομηνία, εντάξει. Και δείτε τι κάνει κάθε ημερομηνία που το περνάω. επιστρέφει το υψηλό, το χαμηλό και το υετό για το Ορλάντο. Κάθε σειρά εδώ πηγαίνει σε μια διαφορετική ιστοσελίδα. Φανταστείτε, αν δεν ήταν 15 σειρές, αλλά 5.000 σειρές, θα το ρυθμίσατε για να τρέχει όλη τη νύχτα. Συνήθιζα να γράφω μακροεντολές για αυτό. Στην πραγματικότητα, μία από τις ιστοσελίδες είναι πώς να δημιουργήσετε μια μακροεντολή για να αποκόψετε ιστοσελίδες από χίλιες διαφορετικές ιστοσελίδες σε έναν ιστότοπο, δεν είναι πλέον απαραίτητο με το ερώτημα ενέργειας.επιστρέφει το υψηλό, το χαμηλό και την υετό για το Ορλάντο. Κάθε σειρά εδώ πηγαίνει σε μια διαφορετική ιστοσελίδα. Φανταστείτε, αν δεν ήταν 15 σειρές, αλλά 5.000 σειρές, θα το ρυθμίσατε για να τρέχει όλη τη νύχτα. Συνήθιζα να γράφω μακροεντολές για αυτό. Στην πραγματικότητα, μία από τις ιστοσελίδες είναι πώς να δημιουργήσετε μια μακροεντολή για να αποκόψετε ιστοσελίδες από χίλιες διαφορετικές ιστοσελίδες σε έναν ιστότοπο, δεν είναι πλέον απαραίτητο με το ερώτημα ενέργειας.επιστρέφει το υψηλό, το χαμηλό και την υετό για το Ορλάντο. Κάθε σειρά εδώ πηγαίνει σε μια διαφορετική ιστοσελίδα. Φανταστείτε, αν δεν ήταν 15 σειρές, αλλά 5.000 σειρές, θα το ρυθμίσατε για να τρέχει όλη τη νύχτα. Συνήθιζα να γράφω μακροεντολές για αυτό. Στην πραγματικότητα, μία από τις ιστοσελίδες είναι πώς να δημιουργήσετε μια μακροεντολή για να αποκόψετε ιστοσελίδες από χίλιες διαφορετικές ιστοσελίδες σε έναν ιστότοπο, δεν είναι πλέον απαραίτητο με το ερώτημα ενέργειας.
Now, when I Close & Load, it's funny the preview here is showing me all the results. When I Close & Load, they're actually going to go do each query. And so right now we have preview rows loaded and it will take a good long time for this information to get updated. So, go to lunch, do something especially if you're doing more than 15 rows. And it's funny the preview is correct but they're still going out and could chunk, could chunk, could chunking through each individual row.
And there it's loaded. Is this amazing or what? Hey, I do a lot of Excel seminars, the Power Excel seminar in Orlando, Florida. Look at these beautiful temperatures we have down here on November 4, 2016. My afternoon will be all about Power Query, Power BI, Power Pivot, Power Map. So, I'm going to invite you down to Orlando to check out this seminar. There'll be a link there in the top-right hand corner.
Alright, so recap. Today's trick is from this book, M is for (DATA) MONKEY. We built a query to get one web page and then edit that query to change it into a function. So right before the Let statement variable name => and then change the hard-coded URL to whatever that variable name is. Rename the query to fxWeather, Close & Load, the data disappears. Then, we use some sort of Excel trickery to create a table of all the URLs we want to crawl, create a query from that table. This has to be a Ctrl+T table, add a new column of Weather = fxWeather and again it has to match the case there, (URL), Expand that column, uncheck Prefix. BAM! It is amazing.
Thanks to Ken and Miguel for writing this book. Thanks to you for stopping by. Hope to see you in Orlando on November 4th 2016. See you next time for another netcast from.
Download File
Κατεβάστε το δείγμα αρχείου εδώ: Podcast2056.xlsm
