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:
- Aceptan parámetros de entrada, estos son útiles para asignar variables dentro de la función.
- 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.
Sobre Guillermo Leon Rodriguez
Fundador de savingl.cl y administrador de empresas de profesión. Trata temas relacionados con el desarrollo de soluciones ofimáticas, bases de datos y aplicaciones. Su lema favorito es: automatiza para que no andes con prisas.
Ver todas las entradas de Guillermo Leon Rodriguez