Power Query es un complemento de Excel que nos permite gestionar y automatizar todo lo que tiene que ver con búsqueda de información online, limpieza y carga de ficheros de datos, mezcla de datos… Se descarga desde la web de Microsoft, asegurándonos de elegir la versión correcta para nuestra instalación de office, tanto por idioma como por versión (32 o 64 bits).
Su primer uso es facilitarnos encontrar información en internet, a partir de fuentes de datos como Wikipedia, censo y gobierno USA (recordar buscad en inglés)… La herramienta localiza tablas de información en páginas web (en este caso, introducimos una url directamente). En ambos casos, los datos quedan vinculados con el origen, por lo que si estos cambian, se consiguen los datos nuevos al refrescar la conexión.
Su otro modo de funcionamiento es la carga de datos desde ficheros de texto, Excel, al estilo del importador de ficheros csv, delimitados o de ancho fijo.
La herramienta funciona como un asistente por pasos, en los que vamos ejecutando operaciones para ir modificando y limpiando los datos de la forma deseada. Tenemos herramientas para:
- Dividir campos en varias columnas, a partir de delimitadores o de anchos
- Unir columnas
- Hacer conversiones complejas de tipos, fechas…
- Limpiar datos que no cumplen un determinado criterio
- Mezclar varios ficheros
- Relacionar datos de varios ficheros, para crear un único fichero con datos de ambos
- Realizar cálculos, fórmulas…
La potencia de Power Query se pone de manifiesto en su capacidad de repetir las operaciones que realizamos en un fichero sobre otros similares, ya que todas las operaciones que realizamos en los menús generan unas acciones en un lenguaje de programación de la propia herramienta. De esta forma, podemos replicar las operaciones en otros ficheros, o si tenemos necesidades especiales, programar una operación.
Vamos a probarlo con un ejemplo de datos reales, tomados de SmartSantander. La dirección http://maps.smartsantander.eu/getdata.php nos ofrece datos en tiempo real del estado de numerosos sensores, en forma de fichero json.
Desde la pestaña de PowerQuery, seleccionamos la opción ‘De Web’ e introducimos la url anterior. Se cargaran los datos, inicialmente en un único bloque. Usaremos la capacidad de expandir tablas para, en dos pasos, extraer todos los campos del json.
A partir de aquí, hay que analizar los datos y ver cómo prepararlos para el análisis posterior. Hacemos reemplazo de . por , y conversión numérica para los datos de longitud/latitud, cambiamos de nombre columnas….
El paso más importante es extraer la información del estado de los sensores, que aparece en formato texto dentro de una celda que contiene información en HTML. La alternativa que he seguido es crear una nueva columna con fórmulas las que, usando patrones de texto, extraigan cada dato. Por ejemplo, para la fecha de última actualización:
if Text.PositionOf([content], "Last update") = -1 then Date.FromText("01/01/1900") else Date.FromText(Text.Range([content], Text.PositionOf([content], "Last update")+12, 11))
Tras realizar todos los pasos, conseguimos una tabla de datos lista para analizar, con la ventaja de estar conectada al origen de datos. Si pulsamos la opción ‘Datos –> Actualizar’ se cargarán los datos de origen, se procesarán con nuestras reglas y se actualizarán en la tabla.
Ahora, usando tablas dinámicas, podemos hacer diferentes análisis.
Fecha de última actualización de los sensores que parece que actualizan este dato. Parece que bastantes dispositivos están ‘muertos’ o han sido reemplazados.
Situación de batería de los elementos que la actualizan en 2015 (los buses no informan de esto):
Rizando el rizo, si tenemos acceso a Power Map for Excel (disponible con Office 365 o ‘puede que’ con el nuevo PowerBI gratuito), podemos usar los datos de longitud y latitud para mostrar datos en un mapa.
Por ejemplo, un mapa de calor con los datos de temperatura (que por cierto, son muy extremos 
En este enlace podéis consultar y descargar el fichero Excel: http://1drv.ms/1Czi0kf. Para que funcione la actualización de datos es necesario tener PowerQuery instalado, si no, se trabajará con la última versión guardada. Con Excel Online se puede jugar con la tabla dinámica creada.
