Uso de Power Query para extraer datos no tabulares de páginas web

Share on facebook
Share on twitter
Share on linkedin
Share on telegram
Share on whatsapp

Contenidos

Introducción

Ivan: Recientemente me mudé a Australia con mi familia. Mientras buscaba un lugar para vivir, tuve que buscar un buen par de suburbio y escuela. Encontré datos interesantes en https://www.myschool.edu.au/. Sin embargo, no existe la opción de comparar escuelas. Solo brinda algunas estadísticas sobre las escuelas australianas, una página a la vez. La información presentada en una página de perfil escolar típica (p. Ej. Escuela Pública de Burwood) es como se muestra a continuación.

img_5b333087a95a4-4304072

Significa que, en el peor de los casos, podemos copiar / pegar datos manualmente en Excel y realizar nuestro propio análisis. Por supuesto, esto solo se puede hacer para un número limitado de escuelas. Incluso para veinte escuelas tomaría un poco de tiempo. Sin embargo, hoy en día, cuando tenemos herramientas como Power Query (Get & Transform) en Excel, podemos recopilar datos de sitios web de manera eficiente y fácilmente “actualizable”. Todo lo que describo y comparto en esta publicación está creado solo con fines educativos no comerciales.

Obtención de datos de una escuela

Tomemos como ejemplo la escuela de Burwood. Copiar URL del navegador

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

Y consulta los datos de esta URL en Excel.

img_5b2230a733a87-1237330

Nota: Normalmente hago todas las consultas en Excel. Si es necesario, los copio / pego en Power BI.

Power Query detecta automáticamente todas las tablas disponibles en la página web y presenta opciones para que podamos elegir la que nos interesa. Cuando necesitamos más de una tabla necesitamos crear otra consulta.

Para esta publicación, digamos que estamos interesados ​​en el parámetro “Idioma de origen distinto al inglés”. Pero en este caso, Power Query detecta solo una tabla en la página de la escuela, ¡no los datos que quiero!

img_5b33345906a55-6562785

Los datos que quiero son solo un valor en un gráfico y no están en una tabla (n. ° 1 a continuación) y, por lo tanto, no se pueden usar para fines de comparación.

img_5b33352257942-8845362

Entonces, los datos requeridos no están en formato tabular. Solo puedes recuperar mesas de páginas web mediante Obtener datos de la web. Esto significa que tenemos que encontrar una forma diferente de obtener estos datos de una página web. Afortunadamente, hay una manera de hacer esto y la comparto contigo en esta publicación.

El método

En Google Chrome, podemos Inspeccionar elementos de la página web haciendo clic derecho y luego seleccionando Inspeccionar (n. ° 1 a continuación).

img_5b333864336d5-1125391

En el código HTML a continuación, podemos ver que «97%» (n. ° 1 a continuación) es un texto entre las etiquetas «» (n. ° 2 a continuación) que a su vez pertenece a la etiqueta principal «» (n. ° 3 a continuación ), y todo esto es parte de “<div id =” nonEnglishSpeakingStudents ”…” (# 4 a continuación). Probablemente, esto es lo que necesitamos.

img_5b3338fe920fa-8988053

Esto puede parecer aterrador, pero estas etiquetas nos ayudarán a encontrar los datos que queremos en el texto HTML de la página web. Entonces, primero debemos obtener ese texto. Volviendo a Power Query, «Desde la Web», haga clic con el botón derecho en la URL (n. ° 1 a continuación) y luego haga clic en Editar (n. ° 2 a continuación).

img_5b3339545828e-2670689

Power Query envuelve automáticamente el contenido recibido de la web en la función Web.Page como se muestra a continuación.

img_5b333990e588a-9786590

Pero lo que necesitamos es solo el texto. Así que tenemos que cambiar la configuración «Abrir archivo como» a «Archivo de texto» en el paso Fuente. Haga clic en el engranaje para editar el paso fuente.

img_5b3339dcb0471-1265728

Cuando usamos «Archivo de texto», Power Query carga las filas html de la página web en una columna.

img_5b333a01ea0df-8619053

Podemos filtrar la columna para encontrar la línea que contiene el texto “nonEnglishSpeakingStudents”.

img_5b333a3130ec6-4490417

El paso anterior deja una sola fila en la tabla. Todas las demás filas se han eliminado. Al seleccionar la única fila que queda después del filtrado, podemos ver el «97%» requerido como parte del texto guardado.

img_5b333a6544350-3301356

Ahora, podemos extraer fácilmente este texto utilizando la función estándar de Power Query «Texto entre delimitadores».

img_5b333aa881a66-8194883

Todo lo que tenemos que hacer es especificar los delimitadores de inicio y fin, como se muestra a continuación. Muy fácil, no es necesario programar funciones propias, solo use la interfaz de usuario para ayudar.

img_5b333ad21eeae-1984917

Esto agrega una nueva columna con el valor que estamos buscando.

img_5b333b1d10c80-6195782

Obtener datos para otra escuela

En teoría, si cambiamos la URL con otra ID de escuela, debería funcionar de la misma manera, porque el texto HTML tendrá una estructura similar en la página de perfil de la otra escuela.
Haga clic en «Editor avanzado» y busque la URL con la identificación de la escuela en el código de idioma ‘M’.

img_5b333b5b9f9b8-1393976

Para aplicar nuestra consulta a la página de perfil de otra escuela, necesitamos cambiar solo la URL.
Tomemos, por ejemplo, Concord Public School. Cambié la consulta y ahora la consulta devolvió 69 (como se muestra a continuación), que es lo mismo que en la página de la escuela, ¡funciona!

img_5b333b8102cd7-2558912

Convertir URL en parámetro

Ahora funciona, convertiré la URL en un parámetro en lugar de codificarla como una cadena. Es mejor crear un parámetro para hacerlo más flexible. Inicio Administrar parámetros Nuevo parámetro

img_5b333bf80ffc9-9295333

Complete los campos necesarios (Nombre del parámetro = URL, Texto, Cualquier valor)

Utilizo otra escuela aquí (Meadowbank Public School https://www.myschool.edu.au/school/41257) para verificar que la solución funcione independientemente de la escuela seleccionada.

img_5b333c2286445-8444819

Ahora que tengo URL como parámetror, necesito cambiar el código de Power Query para que apunte al parámetro en lugar de una cadena codificada.

Para ello, vaya a la configuración del paso Fuente de la consulta. Y seleccione Parámetro en la lista desplegable.

img_5b333c57364ad-5835002

Crear una función a partir de una consulta

La razón por la que comenzamos esta tarea es comparar escuelas. Entonces necesitamos enlaces para todas las páginas de perfil de la escuela.

La forma más eficaz de hacer esto en Power Query es crear una función a partir de una consulta existente. En Power Query Editor, haga clic con el botón derecho en la consulta y seleccione «Crear función».

img_5b333cfd46dd5-3530866

Proporcione un nombre a su función.

img_5b333d2124f68-1261930

La función replica el código M utilizado en la consulta inicial, pero agrega una opción para llamarse a sí misma con una URL de parámetro. Tenga en cuenta que la consulta original todavía está allí, además de una nueva función.

img_5b333d4c10d41-7382756

Obtener URL de varias escuelas

La versión actual del sitio web permite buscar escuelas, por ejemplo, por nombre de suburbio o código postal.

img_5b333d8542517-5320757

Mientras buscamos, en el navegador podemos ver una URL que contiene parámetros de búsqueda –
https://www.myschool.edu.au/school-search?FormPosted=True&SchoolSearchQuery=ryde&SchoolSector=G&SchoolType=P&State=NSW

Utiliza los siguientes parámetros:
SchoolSearchQuery = ryde – por ejemplo, suburbios alrededor de Ryde
SchoolSector = G – Gobierno
SchoolType = P – Primaria
Estado = NSW
Entonces, si es necesario, dicha cadena se puede generar con una fórmula en Power Query.
De la misma manera que se describió anteriormente para ‘Obtener datos de una escuela’, podemos consultar esta página web de resultados de búsqueda y extraer información útil del texto.

Por ejemplo, podemos obtener toda la información que se muestra en la página de resultados de búsqueda junto con las URL de las escuelas que requerimos como parámetros, como se muestra a continuación.

img_5b333e5005d5d-5957985

Teniendo la URL de cada escuela, podemos ampliar esta tabla con información de cada página de perfil de la escuela, por ejemplo, con «% de estudiantes que no hablan inglés» como se muestra a continuación, llamando a la función como una columna agregada.

img_5b333e7ae5be5-3079163

Eso es. La información necesaria para comparar escuelas está disponible ahora.

Puede aplicar la misma técnica a cualquier sitio web donde los datos se publiquen en formato no tabular.

Ejercicios de práctica

Ahora que sabe cómo consultar datos no tabulares desde la web, intente obtener los tipos de cambio de divisas en las siguientes páginas:

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

Una forma sencilla de obtener datos sobre las escuelas australianas

Aunque la intención de esta publicación es demostrar lo que es posible con Power Query, en caso de que tenga curiosidad por saber más sobre las escuelas australianas, existe una opción más sencilla para recuperar los datos de las escuelas.
En el pie de página de https://www.myschool.edu.au sitio web puede encontrar un enlace al http://www.acara.edu.au/contact-us/acara-data-access donde todos los datos están disponibles en formato Excel.

img_5b33405c3ebc9-7944837

sobre el autor

Ivan Bondarenko (también conocido como Ivan Bond) es un especialista en informes basados ​​en Excel y Power BI, desarrollador de VBA, autor de código abierto Herramienta de automatización de SAP Business Objects y Solución basada en Excel para programar la actualización de archivos de Excel (también conocido como Power Refresh).

Suscribite a nuestro Newsletter

No te enviaremos correo SPAM. Lo odiamos tanto como tú.