¿Cómo extraer las URL de una web con power query?.

Hace unos días publicamos un artículo acerca de Microsoft Access, hoy vamos a pasar a Excel y Power Query.

Power Query es una herramienta accesoria que forma parte de la suite de Office, en Excel se instala como un complemento y en Power BI viene de manera predeterminada. Se utiliza para realizar procesos ETL (Extract, Transform and Load / Extraer, transformar y cargar), nos permite extraer datos de un sinfín de fuentes diferentes, transformarlos, limpiarlos o combinarlos y finalmente cargarlos a nuestro software de análisis.

Visualmente podemos ver a Power Query gracias a una GUI (Graphic User Interface) que nos permite ejecutar instrucciones. Se presenta como si se tratara de un software independiente de Excel (vamos a ejemplificar con Excel), al igual que el editor de VBA (Visual Basic for Applications).

Detrás de esta GUI se crea una consulta en un lenguaje llamado M, que en principio puede parecernos complicado, pero que con el tiempo su uso de simplifica, especialmente porque en muchas ocasiones será necesario meter mano al código para modificarlo o para crear una consulta desde 0.

Con Power Query podemos obtener datos de muchas fuentes diferentes, de todo tipo de archivos, bases de datos, servicios en línea y también desde la web. A continuación vamos a exponer un caso-ejemplo para que se pueda entender cómo funciona.

Caso ejemplo: ineficiencia en uso de portal web

Hace mucho tiempo me encontraba en la búsqueda de empleo como «administrativo», estuve revisando un portal de empleos y al pasar los días me di cuenta que perdía tiempo debido a los siguientes problemas:

• El buscador no era muy bueno: buscabas la palabra «administrativo» y se colaban algunos «electricistas» y «guardias de seguridad» en los resultados.
• La frecuencia de actualización de la página era muy rápida, era fácil perderse en los resultados de búsqueda al actualizarla
• En ocasiones dada la alta cantidad de postulaciones que hacía, postulaba dos o más veces en la misma publicación, en la web no quedaba registrado cuando postulabas a una publicación y cuando no.

Debido a esta situación decidí hacer «web scrapping» con Power Query para extraer los links de las publicaciones a Excel y controlar el proceso.

Inspeccionando las URL

El primer paso es obtener las URL de donde queremos extraer la información. Indagando un poco me di cuenta que la URL de búsqueda contiene información relevante: los parámetros de las búsquedas, permitiendo realizar la búsqueda modificando las URL’s.

Para ello en una hoja de Excel diseccionamos la URL en varios pedazos y luego mediante fórmulas la reconstruimos con los parámetros que queramos incluir en la búsqueda, también consideramos la cantidad de páginas que queremos abarcar. En esta oportunidad con extraer los resultados de las primeras tres páginas vamos bien.

Conociendo Power query

En Power Query se pueden crear consultas, que son una serie de instrucciones que devuelven una tabla con los datos solicitados y también se pueden crear funciones definidas por el usuario (User-Defined Functions), que funcionan de la misma forma que una consulta, pero tienen dos ventajas:

1. Aceptan parámetros de entrada, estos son útiles para asignar variables dentro de la función.
2. Pueden ser invocadas por otros procedimientos como por ejemplo otras funciones o consultas.

¿Dónde buscar en una página web?

Antes de continuar y para entender el trabajo que realizan estos procedimientos tenemos que recordar en qué consiste una página web.

En una página web, de cara al usuario vemos una página con formatos y estructura entendible para el usuario, pero detrás de esta existe un código fuente que nuestro navegador interpreta para mostrarnos la web como corresponde. Si no sabes cómo ver el código fuente de una web presiona con clic derecho en tu navegador alguna zona de web que quieras inspeccionar y selecciona ver código fuente. Este debería lucir así:

Funciones y consultas con power query

Parte del trabajo de nuestros procedimientos es identificar patrones y usarlos para extraer la información que necesitamos. Eso precisamente es lo que hace nuestra primera función GetAllLinks (créditos por la función en el blog de BI de Chris Webb).

Función 1: Obtener todos los Links de 1 sola URL.

(SourceURL as text, AttributeDelimiter as text) =>
let
    //Get HTML source
    Source = Text.FromBinary(Web.Contents(SourceURL)),
    //Function to find each link
    GetLink = (Counter as number) =>
                    let
                        CurrentLink = 
        Text.BetweenDelimiters(
            Source, 
            "href=" & AttributeDelimiter, 
            AttributeDelimiter,
            Counter
        )
                    in
                        if CurrentLink="" 
        then 
            {} 
        else 
            List.Combine({
                {CurrentLink}, 
                @GetLink(Counter+1)}
            ),
    //Call function
    Output = GetLink(0)
in
    Output

Aquí tenemos el código de la función GetAllLinks, la idea del artículo no es explicar cómo funciona porque se haría muy extenso, lo importante es entender que se trata de una función que tiene dos parámetros de entrada: SourceURL (La URL que escanearemos) y AttributeDelimiter (el delimitador que identifica los links en el código fuente), también se hace referencia al atributo href que mencionamos anteriormente.

La función se invoca de la siguiente manera: GetAllLinks(MyURL, «»»»), donde MyURL es la URL que escanearemos y «» los delimitadores que envuelven a los links, nótese que se encuentran dentro de otro par de comillas.

Como se indicó previamente, esta función devuelve una tabla de una columna con todos los links de la web, incluyendo los links de las publicaciones que necesitamos, pero también muchos otros que no nos interesan, como links hacia otros sitios o páginas de la web.

Esta función no será invocada por nosotros directamente, lo hará nuestra siguiente función fxPortalLinks.

Función 2: Filtrar links

(MyURL) =>

let
    /* El origen parte con la UDF GetAllLinks que devuelve todos los Links de la URL "MyURL" encapsulados por los
    delimitadores "" */

    Origen = GetAllLinks(MyURL, """"),
    
    /*La lista que hemos obtenido la convertimos en una tabla */
    
    #"Convertida en tabla" = 
       Table.FromList(
         Origen, 
         Splitter.SplitByNothing(), 
         null, 
         null, 
         ExtraValues.Error
       ),

    // Se filtran valores nulos y vacíos para limpiar los datos.

    #"Filas filtradas" = 
        Table.SelectRows(
        #"Convertida en tabla", 
        each ([Column1] <> null and [Column1] <> "")
        ),
    
    /* La función GetAllLinks trae absolutamente todos los links, incluyendo aquellos que no nos interesan, para filtrar sólo
     lo que nos interesa buscamos un identificador único en el código fuente de la página web que nos lleve a los links que
    queremos, en este caso los links que incluyen "htm?" en su estructura son los que nos interesan */

    #"Filas filtradas1" = 
        Table.SelectRows(
          #"Filas filtradas", 
          each Text.Contains([Column1], "htm?")
        ),

    /* En la página web varios elementos distintos contienen el mismo link: la imagen, el título y el ícono de 
    la publicación y por ello los tenemos duplicados, triplicados y cuatriplicados, acá en este paso se eliminan los links 
    sobrantes. */

    #"Duplicados quitados" = Table.Distinct(#"Filas filtradas1")
in
    #"Duplicados quitados"

Aunque no de la forma más clara, los comentarios en el código explican que hace cada línea. FxPortalLinks parte del origen invocando GetAllLinks (y por ende obteniendo todos los links de la página), luego realizamos una serie de operaciones para filtrar los datos y finalmente obtenemos una tabla de una columna con los datos filtrados, sin embargo hasta el momento este resultado es insuficiente para obtener todo lo que queremos. Pasemos a nuestra siguiente función GetValue.

Función 3 (opcional)

/* Función que toma el nombre de un rango en una hoja de cálculo y devuelve el contenido de la columna 1 del mismo rango. */

(rangeName) => 
    Excel.CurrentWorkbook(){[Name=rangeName]}[Content]{0}[Column1]

GetValue se usará para obtener el valor de la «Secondary Keyword» que dispusimos en nuestro panel de control (ver imagen 2), con este valor aplicaremos un segundo filtro a nuestros datos y lo hacemos porque en ocasiones queremos buscar algún subtipo de «administrativos» como por ejemplo «administrativo de ventas» o «administrativo de compras». Sigamos con nuestra última función AllPortalLinks.

Función 4: consolidar el proceso

let
    /* El origen parte con la tabla de URL's que vamos a escanear, los atributos son: 
    Name: nombre de la tabla, también podría ser el nombre de un rango
    [Content]: implica que devuelve el CONTENIDO de la tabla, también se pudiera especificar devolver otro elemento de una 
    tabla como por ejemplo el HEADER. */
    
    Origen = Excel.CurrentWorkbook(){[Name="URL_TABLE"]}[Content],
    
    // Le cambia el tipo de dato a la columna "URL" y lo convierte en tipo texto. Será necesario para poder manipular este 
    campo

    #"Tipo cambiado" = 
       Table.TransformColumnTypes(
         Origen,
         {{"URL", type text}}
       ),

    /* Hasta el momento nuestra tabla tiene una sola columna con las URL que vamos a escanear. Le agregamos una nueva columna 
    personalizada a la tabla, 
en esta columna se realizará una operación que será invocar a fxPortalLinks y tomaremos como 
    parámetro la columna "URL" */

    #"Personalizada agregada" = 
       Table.AddColumn(
         #"Tipo cambiado", 
         "All Ads", 
         each fxPortalLinks([URL])
       ),

    /* En cada una de las celdas de la columna personalizada que acabamos de crear obtenemos un resultado de tipo tabla, es   
    decir, dentro de cada celda hay un objeto de tipo tabla,
 estos objetos los "expandimos" creando una sola columna con los 
    resultados de dichas tablas obteniendo una sola columna con los links de las URL 1, 2, 3, etc. */

    #"Se expandió All Ads" = 
       Table.ExpandTableColumn(
         #"Personalizada agregada", 
         "All Ads", 
         {"Column1"}, 
         {"All Ads.Column1"}
       ),

    // Ya que tenemos los links que buscabamos, no necesitamos la columna URL por lo que la eliminamos.

    #"Columnas quitadas" = 
       Table.RemoveColumns(
         #"Se expandió All Ads",
         {"URL"}
       ),

    // Eliminamos otra columna innecesaria "Page Number"

    #"Columnas quitadas1" = 
       Table.RemoveColumns(
         #"Columnas quitadas",
         {"Page Number"}
       ),

    /* Si recordamos bien en las URL de búsqueda ya existían unos parámetros que forman parte del sistema de búsqueda de la 
    web, 
por ejemplo: "Administrativo", sin embargo este filtro web no es muy efectivo y trae anuncios no deseados, por lo 
    tanto filtramos 
nuevamente la columna "URL" para obtener sólo las URL con las palabras adecuadas. Explicamos un poco las 
    funciones que componen la instrucción:
    
    "Table.SelecRows()" selecciona y filtra las filas, y usa como filtro la palabra clave "each" que revisa que se cumpla la 
    declaración que le continúa.

    Text.Contains([String], substring, comparador) es función booleana que devuelve true o false dependiendo si la SUBSTRING 
    está contenida en la STRING o no. En este ejemplo si la palabra "administrativo" está contenida en la URL de cada una de 
    las publicaciones de empleo.
        STRING: [NombreTabla.NombreColumna] Se tomará el valor de cada fila de la tabla y columna referenciada como STRING.
        SUBSTRING: Usamos la función GetValue como substring, se evaluará si esta se encuentra en la STRING, si es positivo  
        la función retornará TRUE si es negativo FALSE.
        COMPARADOR: Establece si se tomarán en cuenta las mayusculas o minusculas en la comparación */

    #"Filas filtradas1" = 
       Table.SelectRows(
         #"Columnas quitadas1", 
         each Text.Contains(
           [All Ads.Column1], 
           GetValue("Keyword"), 
           Comparer.OrdinalIgnoreCase
         )
       ),
    
    /* Recordemos que esta función se crea para ser ejecutada en múltiples ocasiones y se hará desde una consulta llamada       
    "Existing Data" que es la que efectivamente mostrará los datos al usuario.
    Este paso lo que hace es combinar el resultado de dos consultas: la que tenemos hasta el momento "Filas filtradas1" con 
    los datos previas consultas que yacen en "Existing Data"
 con esto lo que hacemos es adicionar los resultados nuevos a los 
    resultados antiguos para que se acumulen y no se sobreescriban. */
    
    #"Consulta anexada" = 
       Table.Combine(
         {#"Filas filtradas1", #"Existing Data"}
       ),

    /* Por último al realizar la última operación es posible que estemos consultando datos que ya consultamos previamente, y 
    para no acumular datos duplicados eliminamos las duplicidades. */
    
    #"Duplicados quitados" = Table.Distinct(#"Consulta anexada")

in
    #"Duplicados quitados"

Esta es la función que hace la magia, AllPortalLinks invoca fxPortalLinks las veces que sea necesario dependiendo de la cantidad de URL’s que le pasemos y aplica el resto de operaciones de filtrado al resultado, pero para ver los resultados en pantalla debemos invocar a AllPortalLinks y lo haremos mediante un procedimiento de tipo consulta que llamaremos Existing Data.

Consulta 1: Visualizar los datos

let
    Origen = Excel.CurrentWorkbook(){[Name="AllPortalLinks"]}[Content],
    #"Tipo cambiado" = Table.TransformColumnTypes(Origen,{{"All Ads.Column1", type text}})
in
    #"Tipo cambiado"

Al ejecutar esta consulta los resultados se previsualizan de la siguiente manera.

Finalmente presionamos el botón Cerrar y cargar para dar por finalizado el proceso y cargar los datos a la hoja de cálculo.

Estos datos se pueden actualizar presionando ALT + F5 como si se tratara de una tabla dinámica, al hacerlo estaríamos ejecutando la sucesión de pasos una y otra vez. Si quisiéramos modificar los parámetros nos dirigimos al Constructor de URL (imagen 2) y cambiamos la palabra clave o la cantidad de páginas a buscar.

Conclusiones

Power Query es una herramienta muy potente, mediante ella podemos crear flujos de datos, limpiarlos y trabajar con ellos casi instantáneamente.

Para obtener datos de páginas web debemos que estar conscientes que mientras se mantenga la estructura de la web y de las URL, podemos hacer la extracción, pero si éstas cambian también debemos modificar nuestras consultas. La técnica a emplear variará en función de la web:

1. Hay webs que no incluyen información identificable en las URL, lo cual imposibilitaría el uso de ésta técnica.

2. Esta técnica funciona para sitios web estáticos, donde la información que queremos está contenida en el código fuente. Para sitios web dinámicos la historia puede ser diferente.

Otra cosa a tener en consideración es que para evitar problemas legales debemos extraer datos únicamente de fuentes que permitan su uso, para ello es pertinente leer los términos y condiciones de uso de la fuente.

Nos puedes apoyar suscribiéndote a nuestro boletín donde estarás al tanto de todas las novedades de Savingl, siguiéndonos en Twitter, Instagram o Youtube y compartiendo nuestros artículos en Twitter o Facebook. ¡Nos ayudarías a llegar a más personas y poder crear más contenido!.

Si te ha gustado el artículo y te gustaría implementar una solución de este tipo: contáctanos y te ayudaremos.