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.

Antes de continuar vamos a desarrollar una metodología para nuestro caso en concreto. En este ejemplo, tenemos que buscar una forma para poder obtener todas las páginas de una web, sabiendo que no sabemos cuantas hay en realidad. Para ello podemos adoptar el método de fuerza bruta de Matt Mason, el cual examinará tantas páginas como el programa le devuelva hasta que se retorne un null, lo que significa que hemos llegado al final.

Existe también el método de Miguel Escobar, pero por ahora vamos a desarrollar el método de Matt Mason.

Basándonos en este modelo, utilizaremos su función «GetData». Abrimos Power Query y escribimos el siguiente código:

let
Source = ( page as number ) as table =>
let
Src = Web.Page(Web.Contents("http://boxoffcemojo.com/yearly/chart/?page=" & Number.ToText(page) & "&view=releasedate&view2=domestic&yr=2016&p=.htm")),
Data1 = Src{1}[Data],
RemoveBotton = Table.RemoveLastN(Data1,3)
in Src
in Source

Creamos ahora una nueva Query, utilizando «Blank Query». Analizamos cada línea de código.

List.Generate ( ()=>

Este código crea una función sin parámetros.

La siguiente línea es:

[Result= try GetData(1) otherwise null, Page = 1],

Esta línea que dice, ejecuta GetData, en el caso de error, retorna 'null' en la página 1

La siguiente línea es:

each [Result] <> null

Especifica la condición <> null para cada valor de Result de la línea anterior.

La siguiente línea incrementa la página en uno:

each [Result = try GetData([Page]+1) otherwise null, Page = [Page]+1],

La última línea dice a Power Query para que muestre el valor del campo Result:

each [Result])

Todo el código junto sería el siguiente:

let
Source = List.Generate( ()=>
[Result = try GetData(1) otherwise null,Page = 1],
each [Result] <> null,
each [Result = try GetData([Page]+1) otherwise null, Page = [Page] + 1],
each [Result])
in
Source

La imagen anterior se genera tras ejecutar la consulta Query1. Se puede ver que se han generado 12 páginas asociadas a la url que se introdujo.

A partir de aquí se puede expandir la lista y pasar a tabla los datos generados para el año 2016 de la url introducida en la función GetData.

Resultado después de expandir con Power Query
Power Query después de expandir

Seguimos expandiendo las tablas para ver los datos asociados a las diferentes páginas.

Jugando con la ULR

Lo siguiente que vamos a hacer es bajarnos el software Fiddler de Telerik (https://www.telerik.com/fiddler)

Una que nos bajamos Fiddler podemos ver la siguiente pantalla:

La aplicación Fiddler nos permite examinar URLs completas de websites, con esto lo que logramos es poder ver que URL hay detrás de las diferentes páginas de nuestra web de ejemplo de estadísticas de Hockey. Nos servirá para poder montar nuestro Power Query aprovechando las difrentes páginas de la web.

Vamos con el ejemplo, Fiddler nos muestra que nuestro website de ejemplo se mueve entre diferentes páginas con la siguinete URL:

http://www.quanthockey.com/scripts/AjaxPaginate.php?cat=Season&pos=Player&SS=2017-18&af=0&nat=2017-18&st=reg&sort=P&so=DESC&page=2&league=KHL&lang=en&rnd=167379793&dt=1

Lo más importante de la url anterior es lo que se encuentra en negrita.

Poniendo todo junto

Lo primero que tenemos que hacer es crear una nueva query con un parámetro nuevo.

El nombre que le damos es PageNumber y lo configuramos con tipo Decimal con un valor por Defecto o Current Value = 1

Creamos una query en blanco y pegamos el siguiente código:

Source = Web.Page(Web.Contents("http://boxofficemojo.com/yearly/chart/?page=" & Number.ToText(page) & "&view=releasedate&view2=domestic&yr=2013&p=.htm")),

y lo modificamos con la URL proporcionada por Fiddler:

=Web.Page(Web.Contents("http://www.quanthockey.com/scripts/AjaxPaginate.php?cat=Season&pos=Player&SS=2017-18&af=0&nat=2017-18&st=reg&sort=P&so=DESC&page=2&league=KHL&lang=en&rnd=167379793&dt=1"))

En esta URL tenemos que incluir el parámetro creado PageNumber convirtiendo el mismo a Text con la función Text.Form.

=Web.Page(Web.Contents("http://www.quanthockey.com/scripts/AjaxPaginate.php?cat=Season&pos=Player&SS=2017-18&af=0&nat=2017-18&st=reg&sort=P&so=DESC&page=" & Text.Form(PageNumber) & "&league=KHL&lang=en&rnd=167379793&dt=1"))

Después das a intro y se genera una tabla con un campo condensado de tipo tabla, hacemos click en el icono del campo señalado para expandir la tabla.

Creamos una Query en blanco de nuevo y copiamos el siguiente código:

(PageNumber as number) => let
Source = Web.Page(Web.Contents("http://www.quanthockey.com/scripts/AjaxPaginate.php?cat=Season&pos=Player&SS=2017-18&af=0&nat=2017-18&st=reg&sort=P&so=DESC&page=" & Text.From(PageNumber) & "&league=KHL&lang=en&rnd=167379793&dt=1")),
Data0 = Source{0}[Data],
#"Changed Type" = Table.TransformColumnTypes(Data0,{{"Rk",Int64.Type},{"", type text},{"Name", type text},{"Age",Int64.Type},{"Pos",type text},{"GP",Int64.Type},{"G",Int64.Type},{"A", Int64.Type},
{"P",Int64.Type},{"PIM",Int64.Type},{"+/-",Int64.Type},{"PPG",Int64.Type},{"SHG",Int64.Type},{"GWG",Int64.Type},{"G/GP", type number},{"A/GP",type number},{"P/GP",type number}})
in
#"Changed Type"

La segunda parte del código sólo cambia los tipos de datos de cada columna para no tener que hacerlo cada vez, la función lo hace.

Renombramos la función a PageData después enter.

Seguimos creando otra query en blanco y escribimos el siguiente código:

= List.Generate( () =>

[Result= try PageData(1) otherwise null, Page= 1],

each [Result] <> null,

each [Result = try PageData(Page) otherwise null, Page = [Page] + 1],

each [Result])

Pulsa ENTER y renombra la query a AllData

En este caso, sólo tenemos que expandir la lista en tablas, primero convertir el resultado de la lista en tabla y después ya podemos expandir la tabla.

Ya podemos entonces dar a Close and Apply

De esta forma ya tenemos todos los resultados de todas las páginas de la URL, 956 filas en lugar de las iniciales de la página 1. De esta forma hemos podido extraer todas las páginas de una URL que se encuentra paginada.

Seguramente que Microsoft añadirá esta funcionalidad en breve en sus implementaciones, mientras tanto podemos ir utilizando este proceso para extraer toda la información de webs con listados que se encuentran paginados.

Espero que os sea útil. Podéis contactar con mi correo para cualquier aclaración.

Dejo el archivo pbix de Power BI por si queréis echar un veo al código.

Gracias.

luispaton@gmail.com.

En este enlace se puede descargar el pbix comprimido.