Power Query è piuttosto potente. Ma sto per eseguire Power Query Squared … scrivendo una query per una pagina e quindi facendo in modo che Excel esegua la stessa query per un intero elenco di pagine Web.
Guarda un video
- Il trucco di oggi è adattato dal libro M is for Data Monkey
- Crea una query per ottenere dati da una pagina web
- Modifica la query per trasformarla in una funzione con (VariableName) => prima di Let
- Modificare l'URL hardcoded in VariableName
- Rinomina la query in fxWeather
- Chiudi e carica. I dati scompariranno.
- Usa Excel Trickery per creare una tabella di tutti gli URL
- Crea una query da quella tabella.
- Aggiungi una nuova colonna di Meteo
=fxWeather((URL)) - Espandi la colonna. Deseleziona Prefisso
- Sorprendente!
Trascrizione del video
Impara Excel da Podcast, episodio 2056: Power Query Squared
Ehi, bentornato al netcast, sono Bill Jelen. Mi sono imbattuto in questo trucco mentre mi stavo preparando a fare un seminario a una conferenza a Dallas chiamata Excelapalooza, il più grande nome di conferenza Excel di sempre. Dovresti controllarlo ogni settembre a Dallas.
E merito a Ken Puls e Miguel Escobar perché ho dovuto fare un'ora di power query, quindi ovviamente ho tirato fuori un libro fantastico, il più grande libro al mondo su Power Query. Stavo sfogliando il libro e ho visto che avevano una tecnica e ho detto: “Aspetta un secondo. Vado a vedere se riesco ad adattare questa tecnica. " E questo per me è fantastico nella query di alimentazione. Ed ecco cosa faremo, prenderemo una power query e poi eseguiremo quella power query, quella query, dozzine di volte, va bene?
E così, l'esempio che mi è venuto in mente è stato quello in cui volevo estrarre dati da una pagina web, va bene. E sono andato a - ho solo cercato alcune pagine web che potevo - che potevo usare come esempio. Sono finito a Weather Underground ed ecco l'URL, e puoi vedere che ero a Dallas-Fort Worth, quindi stiamo estraendo i dati per Dallas e sembra il 2 gennaio 2015. Quindi proprio lì nell'URL ci sono i parametri , destra? E questo è un URL che è appena maturo per far funzionare questa cosa.
Daremo una rapida occhiata alla pagina web anche se non è così importante. Vedi, ci sono molti dati diversi qui sulla pagina web e ho deciso che stavo solo cercando di ottenere precipitazioni e temperature alte e basse. Ed ecco la domanda. E lascia che ti dica qui che questo podcast non riguarda come creare questa query, nuova query, da altre fonti, dal Web, specificare l'URL e quindi un gruppo di passaggi che non ho intenzione di dettagliare qui per ottenere il mio risposta finale di Max Temp, Min Temp e precipitazione. Il punto è che prenderai la tua domanda e la farai funzionare per un sacco di cose.
Quindi, faccio clic su Chiudi e carica e questa query funziona, restituisce la mia unica riga. È tutto fantastico. E tornerò, modificherò questa query e andrò a Visualizza, Editor avanzato. Accetterò questa query e la renderò una funzione, va bene? Quindi proprio qui prima della parola LET, premo Invio. E tra parentesi, gli darò una variabile (MyURL) e poi => piccola freccia lì, va bene? Freddo. E poi quaggiù, dove hanno l'URL tra virgolette, voglio eliminare l'intero URL comprese le virgolette e quindi digitare il nome della mia variabile MyURL, va bene. Quindi quello che stiamo dicendo è che lo passeremo all'URL e farà la stessa query, ma con qualunque URL ci capiti di passare.
Ora, un paio di cose sconcertanti qui, quando clicco su Fatto, oh uomini! Tutti i miei passaggi applicati sono andati e vogliono che inserisca un parametro. Ignora tutto ciò. Lo rinomineremo; lo chiameremo fxWeather. FX, ovviamente, essendo l'abbreviazione di funzione e devi ricordare davvero questo nome e ricordare quali lettere sono in maiuscolo, sarà molto importante in un paio di minuti. Home, Close & Load e BAM! Tutto è andato. Oh no! Ma è ok. Va bene, quindi sappiamo che è lì. È solo una connessione. Ora, vengo qui e questo è semplicemente vecchio Excel, va bene? Quindi ecco l'URL, l'ho suddiviso nella prima parte dell'URL, la parte finale dell'URL. So che devo prendere la data; Devo formattarlo in questo strano formato dell'anno,mese e giorno, quindi ho usato la funzione TESTO per farlo. Inserisci qui la data di inizio. Posso persino cambiare aeroporto, quindi ora vado avanti e indietro. Facciamo MCO per Orlando e facciamo alcuni dati recenti. Quindi inizierò il 10/1/2016, va bene. Quindi ora abbiamo questo fantastico tavolino allestito qui. E a proposito, deve essere un tavolo. Devi usare il formato come tabella o Ctrl + T. Quindi, sai, questo è solo prendere quella data e poi + 1 + 1 + 1. Lo formatto, costruisco l'URL.Devi usare il formato come tabella o Ctrl + T. Quindi, sai, questo è solo prendere quella data e poi + 1 + 1 + 1. Lo formatto, costruisco l'URL.Devi usare il formato come tabella o Ctrl + T. Quindi, sai, questo è solo prendere quella data e poi + 1 + 1 + 1. Lo formatto, costruisco l'URL.
Va bene ora, creeremo una query da questa tabella. Va bene, e ci sono le mie informazioni. Sto per aggiungere una nuova colonna, aggiungere una colonna personalizzata, le colonne si sarebbero chiamate Weather e la formula sarà = fxWeather. Assicurati che sia esattamente la stessa maiuscola, le stesse lettere maiuscole e minuscole e inseriremo quel campo chiamato URL in questo modo, chiudendo parentesi. Nessun errore di sintassi, fare clic su OK. Vogliono sapere della privacy qui, sono tutti dati pubblici, fare clic su Salva, va bene. Quindi, ecco il nostro appuntamento. È divertente che abbiano effettivamente cambiato il mio formato in qualcosa che non assomiglia a quello con cui ho iniziato. E poi ecco Weather con il simbolo di espansione. Quindi farò clic sul simbolo di espansione, deselezionerò Usa il nome della colonna originale come prefisso. Voglio il massimo, il minimo, la precipitazione,fare clic su OK. Va bene, e ora tutto ciò di cui ho bisogno è la data e le informazioni là fuori. Quindi farò clic con il pulsante destro del mouse e rimuoverò questa colonna, fare clic con il pulsante destro del mouse e rimuovere questa colonna. Qui, non ho bisogno di quel tempo quindi dirò che questo è solo un appuntamento, va bene. E guarda cosa fa ogni data in cui lo passo; sta restituendo l'alto, il basso e le precipitazioni per Orlando. Ogni riga qui sta andando a una pagina web diversa. Immagina, se non fossero 15 righe ma 5.000 righe, lo avresti impostato per essere eseguito durante la notte. Scrivevo macro per questo. In effetti, una delle pagine web su è come costruire una macro per raschiare le pagine web da migliaia di pagine web diverse in un sito, non più necessario con la query di alimentazione.Fare clic con il tasto destro e rimuovere questa colonna, fare clic con il tasto destro e rimuovere questa colonna. Qui non ho bisogno di quel tempo quindi dirò che questo è solo un appuntamento, va bene. E guarda cosa fa ogni data in cui lo passo; sta restituendo l'alto, il basso e le precipitazioni per Orlando. Ogni riga qui sta andando a una pagina web diversa. Immagina, se non fossero 15 righe ma 5.000 righe, lo avresti impostato per essere eseguito durante la notte. Scrivevo macro per questo. In effetti, una delle pagine web su è come costruire una macro per raschiare le pagine web da migliaia di pagine web diverse in un sito, non più necessario con la query di alimentazione.Fare clic con il tasto destro e rimuovere questa colonna, fare clic con il tasto destro e rimuovere questa colonna. Qui, non ho bisogno di quel tempo quindi dirò che questo è solo un appuntamento, va bene. E guarda cosa fa ogni data in cui lo passo; sta restituendo l'alto, il basso e le precipitazioni per Orlando. Ogni riga qui sta andando a una pagina web diversa. Immagina, se non fossero 15 righe ma 5.000 righe, lo avresti impostato per l'esecuzione durante la notte. Scrivevo macro per questo. In effetti, una delle pagine web su è come costruire una macro per raschiare le pagine web da migliaia di pagine web diverse in un sito, non più necessario con la query di alimentazione.sta restituendo l'alto, il basso e le precipitazioni per Orlando. Ogni riga qui sta andando a una pagina web diversa. Immagina, se non fossero 15 righe ma 5.000 righe, lo avresti impostato per l'esecuzione durante la notte. Scrivevo macro per questo. In effetti, una delle pagine web su è come costruire una macro per raschiare le pagine web da migliaia di pagine web diverse in un sito, non più necessario con la query di alimentazione.sta restituendo l'alto, il basso e le precipitazioni per Orlando. Ogni riga qui sta andando a una pagina web diversa. Immagina, se non fossero 15 righe ma 5.000 righe, lo avresti impostato per l'esecuzione durante la notte. Scrivevo macro per questo. In effetti, una delle pagine web su è come costruire una macro per raschiare le pagine web da migliaia di pagine web diverse in un sito, non più necessario con la query di alimentazione.
Ora, quando chiudo e carico, è divertente che l'anteprima qui mi mostri tutti i risultati. Quando chiudo e carico, in realtà eseguiranno ogni query. E così in questo momento abbiamo caricato le righe di anteprima e ci vorrà molto tempo prima che queste informazioni vengano aggiornate. Quindi, vai a pranzo, fai qualcosa soprattutto se stai facendo più di 15 righe. Ed è divertente che l'anteprima sia corretta ma stanno ancora uscendo e potrebbero frammentarsi, potrebbero frammentarsi, potrebbero frammentarsi attraverso ogni singola riga.
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
Scarica il file di esempio qui: Podcast2056.xlsm
