En el presente artículo voy a explicar como importar en Power Query todas las tablas de una única URL la cual tiene una tabla de determinado número de filas en cada página, pero la URL no cambia.

Para hacerlo hay que dedicarle algo de tiempo y no es sencillo, sin embargo, voy a hacer la explicación fácil ya que las técnicas empleadas son muy útiles de cada a adquirir práctica con Power Query.

Para empezar utilizaremos como página la siguiente, pero puede servir cualquier tipo de web que disponga de una tabla cuyos datos estén en diferentes páginas.

http://www.quanthockey.com/khl/seasons/2017-18-khl-players-stats.html

Datos de Ejemplo

La página contiene las estadísticas de los jugadores de hockey bastante bien detallada. El reto más importante aquí es que como se ve en la imagen incrustada, existen más de 20 páginas embebidas, de las cuales queremos sacar todos los datos para analizar bien el contenido.

El problema principal lo encontramos a al navegar entre página y página, la url es la misma que la de todas las páginas pero el dato cambia.

¿Cómo vamos a extraer todos los datos? si estás interesado sigue leyendo, el proceso lo estructuraremos en 5 partes básicas.

Parte 1: Obetner los datos manualmente

Dentro de todas las soluciones posibles, vamos a presentar una de las mejores soluciones. Sabemos que Power Query, está disponible tanto para Excel como para Power BI, por lo tanto, para comprobar su versatilidad, vamos a presentar la solución en Excel.

Para importar los datos manualmente de la web que hemos escrito arriba, lo primero que hay que hacer es abrir Excel, navegar hasta la pestaña «Data» o «Datos» y hacer click en la opción «New Query» ó «Nueva Consulta», seleccionar «Other Sources» («Otros Orígenes») y hacer click en «Web».

Aparece un cuadro de diálogo para insertar una URL. Una vez insertado haz click en OK.

Power Query other sources

La pantalla que se muestra a continuación es un cuadro de diálogo con una tabla exacta a la tabla que queremos analizar de la web. Simplemente, cambiar el nombre de la misma haciendo click en «Edit» o «Transformar Datos».

Dentro ya del Editor de «Power Query» daremos a nuestra query un nombre amigagle para nosotros y después pulsamos en «Close & Load».

Power Query Hockey Data
Power Query Cerrar y Cargar

Se puede observar que Power Query sólo muestra las primeras 50 filas. Porque ocurre esto, fácilmente se puede ver porque Power Query retorna los resultados basados en la URL que le hemos pasado. La página a través de Javascript es capaz de devolver las siguientes 50 filas de la tabla, actualizando dinámicamente los resultados, además tenemos otro problema, no sabemos el número total de páginas que componen la tabla de la web.

Tenemos, por lo tanto, tres issues que resolver:

  • No podemos poner de forma manual los resultados de la website.
  • No sabemos el número de páginas de la website y esto puede cambiar en el tiempo.
  • La página web no cambia la URL en cada nueva página de la tabla.

Parte 2: Custom Functions

Centrándonos en el primer punto, hemos visto que es imposible obtener todos los datos de la web de manera manual, Power Query sólo nos retorna las primeras 50 filas de la tabla. Para obtener el resto de las filas, vamos a utilizar las Custom Functions de «Power Query «.

Un «Custom Function» es una query ejetutada por otras queries, es similar a lo que se conoce como Object Method en JavaSript. El beneficio de utilizar una «Custom Function» es repetir el mismo número de pasos las veces que se quieran.

Vemos a poner un ejemplo para que esto quede más claro. Por ejemplo, queremos devolver las ganancias (Gross Earning) de todas las películas de un año en particular, da igual el año en el que empecemos, por ejemplo, comenzamos por el año 2017. Estos datos los cogemos de la siguiente web,

http://www.boxofficemojo.com/yearly/chart/?yr=2017&p=.htm

De nuevo, hacemos los mismo que con la URL del principio de este artículo, lanzamos Power Query / Get & Transform, Data -> New Query ->Other Sources -> Web.

En el cuadro de diálogo ponemos la URL y después OK.

Una vez que tenemos la tabla en Query Editor podemos definir un parámetro, los parámetros son necesarios para que las custom functions funcionen bien.

Creamos un parámetro nuevo llamdo «Year» con el valor inicial 2017.

Parámetros Power Query

Podemos añadir ahora una columna personalizada «Custom Column». Hacemos click en la tabla y después «Add Column» tab y por último «Custom Column.»

Esta columna personalizada la vamos a llamar igual que el parámetro que ya creamos, «Year». Hay que asegurarse también de que cambiamos el tipo de la columna personalizada a tipo «Text».

El siguiente paso, consiste en integrar nuestro parámetro dentro de la URL. Esto nos permitirá cambiar dinámicamente la URL y por lo tanto cambiar la base de datos en función del año que queramos. Dentro de «Table 1», click en la configuración del paso Source dentro de la sección de los pasos aplicados:

Vamos a la sección del editor avanzado y buscamos dentro de la parte de la URL aquello que pone el año, en su lugar ponemos el parámetro que ya hemos creado.

El resultado final quedaría de la siguiente forma, tener en cuenta que hay que ir a Editor Avanzado para dividir la URL en varias partes.

Una vez hecho esto convertimos la query en una función. Right Click en GrossEarnings y después «Create Function». Llamamos a la función «GetMovies».

Cuyo resultado es una carpeta conteniendo la tabla original el parámetro y la función «GetMovies». Cada vez que llamemos a la función está realizará lo mismo para cada año pasado como parámetro.

Creamos una simple lista de números con la función List.Numbers dentro de otra query difrente.

Las tablas tienen mejor flexibilidad y funcionalidad que las listas, por lo que vamos a pasar a tabla la lista que hemos creado, usando la opción «To Table» localizada en el grupo «Convert/Transform».

Ahora cambiamos el tipo de la columna a tipo «Texto». Seleccionando la query «Query1» vamos a invocar a una «Custom Function» dentro de la pestaña «Add Column» y después seleccionamos «Invoke Custom Function».

Cambiamos el nombre de la columna que se va a añadir y seleccionamos la función «GetMovies» que hemos creado anteriormente, ya que la función necesita un parámetro llamado «Year», vamos a utilizar la columna de la tabla de años que hemos creado como entrada de la función.

Power Query Custom Function

Una vez que le damos a aceptar, se genera unas columna en la que cada fila de cada año contiene una tabla que se forma con el año de la columna «Column1». Podemos expandir cada fila o expandir todas de golpe haciendo click en la esquinas superior derecha.

Tenemos algunas limitaciones con las funciones personalizadas o «Custom Functions»:

  • Editar el script M de la función hará que la función y la query se colapsen.
  • Las funciones no pueden ser programadas para actualizarse en Power BI.

Una vez que expandimos la tabla queda como se muestra:

Todavía tenemos que limpiar los datos y por supuesto no sabes nada del número de páginas. Vamos a tratar este tema en el siguiente punto.