Utilizzo di Power Query per estrarre dati non tabulari da pagine Web

introduzione

Ivan: Di recente mi sono trasferito in Australia con la mia famiglia. Durante la ricerca di un posto dove vivere, Ho dovuto trovare una buona coppia di sobborgo e scuola. Ho trovato fatti interessanti in https://www.myschool.edu.au/. tuttavia, non c'è possibilità di confrontare le scuole. Fornisce solo alcune statistiche sulle scuole australiane, una pagina alla volta. Informazioni presentate su una tipica pagina del profilo della scuola (P. Non. Scuola pubblica di Burwood) è come mostrato di seguito.

Significa che, Nel peggiore dei casi, possiamo copiare / incollare i dati manualmente in Excel ed eseguire la nostra analisi. Certo, questo può essere fatto solo per un numero limitato di scuole. Anche per venti scuole ci vorrebbe un po' di tempo. tuttavia, oggi, quando abbiamo strumenti come Power Query (Ottenere & Trasformare) in excel, possiamo raccogliere dati da siti web in modo efficiente e facilmente "aggiornabile". Tutto ciò che descrivo e condivido in questo post è creato solo per scopi didattici non commerciali.

Ottenere dati da una scuola

Prendi ad esempio la Burwood School. Copia l'URL del browser

https://www.myschool.edu.au/SchoolProfile/Index/104736/BurwoodPublicSchool/41369.

E controlla i dati di questo URL in Excel.

Nota: Di solito faccio tutte le query in Excel. Se richiesto, li copio / incolla in Power BI.

Power Query rileva automaticamente tutte le tabelle disponibili nella pagina Web e presenta le opzioni in modo che possiamo scegliere quella che ci interessa. Quando abbiamo bisogno di più di una tabella, dobbiamo creare un'altra query.

Per questa pubblicazione, diciamo che ci interessa il parametro "Lingua sorgente diversa dall'inglese". Ma in questo caso, Power Query rileva solo una tabella nella pagina della scuola, Non sono i dati che voglio!

I dati che voglio sono solo un valore in un grafico e non in una tabella (n. ° 1 prossimo) e, così, non può essere utilizzato a fini di confronto.

Quindi, i dati richiesti non sono in formato tabellare. Puoi solo recuperare tavoli di pagine web utilizzando Ottieni dati dal web. Ciò significa che dobbiamo trovare un modo diverso per ottenere questi dati da una pagina web.. fortunatamente, c'è un modo per farlo e lo condivido con te in questo post.

Il metodo

E Google Chrome, noi possiamo Ispezionare, controllare elementi della pagina Web facendo clic con il pulsante destro del mouse e selezionando Ispeziona (n. ° 1 prossimo).

Nel codice HTML qui sotto, possiamo vederlo “97%” (n. ° 1 prossimo) è un testo tra le etichette “” (n. ° 2 prossimo) che a sua volta appartiene al tag principale “” (n. ° 3 prossimo ), e tutto questo fa parte di"<div id = ”studenti non di lingua inglese” ” (# 4 prossimo). Probabilmente, questo è ciò di cui abbiamo bisogno.

Questo può sembrare spaventoso, ma questi tag ci aiuteranno a trovare i dati che vogliamo nel testo HTML della pagina web. Quindi, dobbiamo prima ottenere quel testo. Tornando a Power Query, “Dal web”, tasto destro sull'url (n. ° 1 prossimo) e quindi fare clic su Modifica (n. ° 2 prossimo).

Power Query avvolge automaticamente il contenuto ricevuto dal Web nella funzione Web.Page come mostrato di seguito.

Ma quello di cui abbiamo bisogno è solo il testo. Quindi dobbiamo cambiare le impostazioni “Apri file come” un “File di testo” nel passaggio Sorgente. Fare clic sull'ingranaggio per modificare il passaggio di origine.

Quando usiamo “File di testo”, Power Query carica le righe html dalla pagina web in una colonna.

Possiamo filtrare la colonna per trovare la riga che contiene il testo "non EnglishSpeakingStudents".

Il passaggio precedente lascia una singola riga nella tabella. Tutte le altre righe sono state rimosse. Selezionando l'unica riga rimasta dopo il filtraggio, possiamo vedere il “97%” richiesto come parte del testo salvato.

Ora, possiamo estrarre facilmente questo testo utilizzando la funzione Power Query standard “Testo tra delimitatori”.

Tutto quello che dobbiamo fare è specificare i delimitatori di inizio e fine, come mostrato di seguito. Molto facile, non è necessario programmare le proprie funzioni, basta usare l'interfaccia utente per aiutare.

Questo aggiunge una nuova colonna con il valore che stiamo cercando.

Ottieni dati per un'altra scuola

In teoria, se cambiamo l'URL con un altro ID scuola, dovrebbe funzionare allo stesso modo, perché il testo HTML avrà una struttura simile nella pagina del profilo dell'altra scuola.
Clicca su “Editor avanzato” e cerca l'URL con l'identificazione della scuola nel codice lingua 'M'.

Per applicare la nostra query alla pagina del profilo di un'altra scuola, dobbiamo cambiare solo l'url.
Prendiamo, ad esempio, Scuola Pubblica Concord. Ho cambiato la query e ora la query è tornata 69 (come mostrato di seguito), che è lo stesso della pagina della scuola, Funziona!

Converti URL in parametro

Ora funziona, Convertirò l'URL in un parametro invece di codificarlo come stringa. Meglio creare un parametro per renderlo più flessibile. Home Gestisci parametri Nuovo parametro

Compila i campi richiesti (Nome parametro = URL, Testo, Qualsiasi valore)

Uso un'altra scuola qui (Scuola pubblica di Meadowbank https://www.myschool.edu.au/school/41257) per verificare che la soluzione funzioni indipendentemente dalla scuola selezionata.

Ora che ho URL come parametroR, necessario cambia il codice di Power Query in modo che punti al parametro anziché alla stringa hardcoded.

Per questo, vai alle impostazioni del passaggio dell'origine della query. E seleziona Parametro dall'elenco a discesa.

Crea una funzione da una query

Il motivo per cui abbiamo iniziato questo compito è confrontare le scuole. Quindi abbiamo bisogno di collegamenti per tutte le pagine del profilo della scuola.

Il modo più efficiente per farlo in Power Query è creare una funzione da una query esistente. Nell'editor di Power Query, fare clic con il tasto destro del mouse sulla query e selezionare “Crea funzione”.

Dai un nome al tuo ruolo.

La funzione replica il codice M utilizzato nella query iniziale, ma aggiungi un'opzione per chiamarsi con un parametro url. Nota che la query originale è ancora lì, più una nuova funzione.

Ottieni URL di più scuole

L'attuale versione del sito Web consente di cercare le scuole, ad esempio, per nome del sobborgo o codice postale.

mentre cerchiamo, nel browser possiamo vedere un URL che contiene i parametri di ricerca –
https://www.myschool.edu.au/school-search?FormPosted=True&SchoolSearchQuery=ryde&SchoolSector=G&SchoolType=P&State=NSW

Usa i seguenti parametri:
SchoolSearchQuery = ryde – ad esempio, sobborghi intorno a Ryde
Settore Scolastico = G – governo
Tipo di scuola = P – Primario
Stato = NSW
Quindi, se richiesto, detta stringa può essere generata con una formula in Power Query.
Allo stesso modo descritto sopra per "Ottieni dati da una scuola", possiamo consultare questa pagina web dai risultati di ricerca ed estrarre informazioni utili dal testo.

Ad esempio, possiamo ottenere tutte le informazioni mostrate nella pagina dei risultati di ricerca insieme agli URL delle scuole che richiediamo come parametri, come mostrato di seguito.

Avere l'URL di ogni scuola, possiamo espandere questa tabella con le informazioni di ogni pagina del profilo della scuola, ad esempio, insieme a “% studenti non di lingua inglese” come mostrato di seguito, chiamare la funzione come colonna aggregata.

Questo è tutto. Le informazioni necessarie per confrontare le scuole sono disponibili ora.

Puoi applicare la stessa tecnica a qualsiasi sito web in cui i dati sono pubblicati in formato non tabellare.

Esercizi pratici

Ora che sai come interrogare dati non tabulari dal web, prova a ottenere i tassi di cambio nelle pagine seguenti:

https://www.xe.com/currencytables/?from=AUD&date=2018-06-19

http://www.floatrates.com/daily/AUD.xml

https://www.exchange-rates.org/converter/AUD/EUR/1

https://www.x-rates.com/table/?from=AUD&amount=1

Un modo semplice per ottenere dati sulle scuole australiane

Sebbene l'intenzione di questo post sia dimostrare ciò che è possibile con Power Query, nel caso foste curiosi di saperne di più sulle scuole australiane, c'è un'opzione più semplice per recuperare i dati dalle scuole.
Nel piè di pagina di https://www.myschool.edu.au sito web puoi trovare un link a http://www.acara.edu.au/contact-us/acara-data-access dove tutti i dati sono disponibili in formato Excel.