089 Power Query

BIMlevel

• Tenía muchas ganas de hablar de Power Query.
  • Lo descubrí hace casi dos años.
  • Ha cambiado totalmente mi forma de usar excel.
• Una herramienta para consultar datos que se llama "Consulta poderosa", tiene mucho que decir en el BIM.
• También es uno de los pilares del tan de moda Power BI.

¿Qué es Power Query?

• https://powerquery.microsoft.com/
• "Editor de consultas"
  • Consultas de datos, generalmente en tablas.
• Se lanzó como un complemento para excel 2010 que había que instalar a parte.
  • Salió junto con Power Pivot (poder hacer tablas dinámicas de datos externos al excel)
• En Excel 2016 se integra por defecto, en la pestaña Datos >Obtener y transformar Datos, y Consultas y conexiones.
• Sirve para 3 cosas:
  • Se conecta a fuentes de datos locales y en la nube, y extraer esos datos.
  • Podemos transformar esos datos, que están en tablas (cambiar filas por columnas, quitar encabezados, añadir o quitar columnas, buscar y reemplazar, etc) sin modificar la fuente original.
  • Podemos fusionar las tablas ya transformadas y cargarlas como tablas en una hoja de excel.
    • Si vamos a superar el límite de una hoja de excel (1.048.576 filas o 16.384 columnas) lo podemos cargar sólo en el modelo de datos de excel.
      • No aparece en ninguna hoja y por lo tanto no tiene límites, pero podemos usar los datos en gráficas y tablas dinámicas.
• Técnicamente a esto se le llama Proceso ETL (Extract-Transform-Load, o Extracción-Transformación-Carga)
  • La magia está en que estos pasos los hacemos una vez, se quedan guardados como si fuera una macro, y si los datos se actualizan en el origen, se actualiza todo.
• No está sólo en excel:
  • Power Apps (Plataforma de Microsoft para crear apps sencillas sin saber código)
  • Power Automate (antes Microsoft Flow, tipo ifttt, Zapier, Automate.io...)
  • Azure Data factory (integrador de datos de la nube de Microsoft)
  • Dynamics 365 (el ERP y CRM de Microsoft)
  • Power BI (Power Query es el corazón de Power BI según Microsoft)

Inciso: ¿Qué es Power BI?

• https://powerbi.microsoft.com/
• Utiliza Power Query para leer datos de muchas fuentes y en muchos formatos distintos.
• En lugar de tablas y tablas dinámicas de excel, tiene un montón de herramientas para hacer gráficos y listados interactivos para que gente sin conocimientos en software pueda analizar datos de una forma muy visual.
  • Si eres de los que en excel crea tablas dinámicas, gráficos y segmentación de datos, es lo mismo pero con muchas, muchas más posibilidades.
• Es más rápido que excel para grandes cantidades de datos.
• Los paneles se pueden consultar online sin tener que instalar nada.
• Power BI es de Microsoft 100% compatible con excel y todo lo de Microsoft, es considerado el mejor del mercado, pero no es el único:
  • Tableau
  • Qlik
  • Domo
  • Google Data Studio
• Hoy vamos a ver cómo usar Power Query en Excel, pero piensa que todo lo que aprendas de Power Query en excel lo puedes aplicar tal cual cuando uses Power BI.

Patrocinador: 360 admin Tools

Pero antes, nuevo patrocinador en BIMlevel, en esta ocasión tenemos a la mejor herramienta para administradores de BIM360, ¡360 Admin Tools! Me hace mucha ilusión este patrocinio porque yo mismo he sido betatester desde el principio del desarrollo, muchas de sus funcionalidades han sido a petición mía, y por supuesto, uso 360 admin Tools en mi día a día para gestionar el Entorno Común de Datos de mi empresa. Pero ¿qué es exactamente 360 Admin Tools? Pues un conjunto de herramientas online, se accede desde una web, sin instalar nada, para BIM360. La joya de la corona es el sincronizador de carpetas y archivos entre BIM360 y otras nubes como SharePoint, Onedrive, Dropbox, Amazon S3 y Google Drive. Pero también puedes hacer cosas como agregar usuarios de forma masiva y más funciones que te iré contando. ¿Cómo funciona? Entras en https://360admintools.com/ inicias sesión con tu cuenta de BIM360 y luego le das permiso a la aplicación en cada plataforma que quieras usar. Que no sabes hacerlo, tienes disponible el soporte técnico e incluso puedes solicitar una demo. Y lo mejor de todo es que es GRATIS y completo para un proyecto, sin registro, ni tarjetas de crédito, ni nada. Luego está la versión PRO para hasta 10 proyectos por 99€/mes, o proyectos ilimitados por 299€/mes.

Los 3 usos de excel

• En cualquiera de nuestros excel, hay de los 3 uso, pero siempre predomina uno.

Calculadora

• Tenemos pocos datos de entrada.
  • Con pocos datos me refiero a cientos de datos como mucho, no miles.
• Necesitamos hacer varios cálculos con fórmulas muy concretas.
• Tenemos pocos datos de salida.
• Ejemplos:
  • Calcular los plazos de una hipoteca.
  • Calcular la velocidad del agua en un canal con forma trapecial.
  • Justificar el HE-1 del CTE por el método simplificado, calculando transmitancias de cerramientos.

Cuadrícula versátil y potente

• Filas y columnas, colores, formatos de fecha y moneda...
• Operaciones sencillas de sumar, contar...
• Ejemplos:
  • Calendario de vacaciones, horarios de trabajo.
  • Planificación de tareas de una obra.
  • Presupuesto doméstico.

Análisis de datos

• Muchos datos de entrada.
  • miles o millones de datos.
• Pocos cálculos, la mayoría estadísticos.
• Muchos datos de salida que queremos analizar de muchas formas distintas a la vez.
• Ejemplos:
  • El análisis de superficies útiles y construidas de un edificio.
    • Reparto de zonas comunes, edificabilidad, ratio útil construida, por plantas...
  • Evolución de los costes de construcción por capítulos y partidas típicas.
  • Análisis de una encuesta de 30 preguntas con 500 participantes.

Buenas prácticas en el análisis de datos

• No querer meter datos como si fuera una calculadora o una cuadrícula con colores y celdas resaltadas.
• Trabajar con tablas, tablas de verdad, no rangos con bordes y colorines.
• Usar tablas diferentes para meter datos y para analizar datos.
  • para meter datos: tablas de excel, tablas externas traídas como consulta.
    • Todo lo que sea importar y actualizar automáticamente, mejor que a mano.
  • Para analizar datos: tablas dinámicas, gráficos dinámicos, cuadros de segmentación de datos, etc.
    • Son más interactivas
    • Todos los análisis se hacen a partir de la misma fuente.
• Power Query es la forma más potente de meter datos de distintas fuentes y con distintos formatos, de forma automática, homogeneizada y actualizable.

Ejemplos de uso de Power Query

Plantilla de contrato con datos del proveedor

• Queremos hacer una plantilla de contrato en Excel en lugar de Word, por aquello de la cuadrícula versátil y hacer alguna fórmula de cálculo de honorarios según alcance seleccionado y demás.
  • Hay que rellenar muchos campos con información de las empresas que firman.
  • Esa información está toda en el CRM de la empresa, por ejemplo, Salesforce.
  • Si tuviera todos los datos de los proveedores en una tabla del excel, con un simple BuscarV, elegiría el nombre de un desplegable (con el típico listado de validación de datos) y todo los demás campos del contrato se rellenarían automáticamente y sin errores.
• Con Power Query podemos tener la información de Salesforce en nuestro excel en tiempo real:
  1. Abrimos el editor de Power Query (En Datos > Obtener Datos > Iniciar editor de Power Query...).
  2. Tenemos una interfaz con la barra de herramientas encima, un listado con todas las consultas a la izquierda, la consulta (se ve como una tabla normal de excel) que estamos editando en el centro, y el listado de transformaciones que hemos aplicado a la derecha.
     • Hacemos clic derecho, nueva consulta en el listado de consultas.
  3. En la barra de fórmulas escribimos "Salesforce.Reports", y ponemos los parámetros conexión.
     • Parece complicado pero es como escribir una fórmula de excel.
     • En realidad es con un lenguaje de fórmulas llamado M, pero es sencillo y está muy bien documentado en la web de microsoft.
       • https://docs.microsoft.com/es-es/powerquery-m/
  4. Nos aparece una tabla con todos los informes de Salesforce que tenemos disponibles con nuestro usuario.
     • Si pinchamos en uno de ellos se abre la tabla del informe en cuestión.
  5. Quitamos las columnas que no queremos.
  6. Filtramos igual que haríamos en una tabla de excel, para quedarnos sólo con el tipo de proveedor que nos interesa.
  7. En el listado de pasos veremos:
     • Origen
     • Navegación
     • Columnas quitadas
     • Filtro aplicado
  8. Con la tabla ya transformada, la cargamos en un hoja del excel como una tabla.
     • Cada vez que actualicemos, powerquery se conectará a salesforce y aplicará todos los pasos.
     • Nosotros tendremos una tabla de excel con los proveedores actualizados.

Superficies útiles y construidas desde Revit

• Tenemos unas tablas de planificación de habitaciones y de áreas en Revit.
• Queremos llevarnos esos datos a excel y hacer algunos cálculos de reparto de zonas comunes, sacar ratios, etc.
• Exportamos las tablas a txt, y las importamos en excel, pero cada vez que hay cambios en Revit, volvemos a importar, hay más filas, algunas fórmulas dejan de funcionar, etc.
• Con Power Query:
  1. En Datos > Obtener Datos > Desde texto/csv
  2. Elegimos el txt de áreas.
     • Se abre Power Query y vemos una tabla con el txt.
  3. Modificamos encabezados, cambiamos nombres a las columnas, sustituimos los m² por vacíos, etc.
  4. Añadimos columnas haciendo los cálculos que necesitamos.
  5. Repetimos con txt de habitaciones.
  6. Cargamos ambas consultas en el excel y creamos nuestras tablas dinámicas para analizarlo todo.

Base de datos con los presupuestos de todos los proyectos de la empresa

• En nuestro servidor local tenemos una carpeta con cada proyecto, y dentro, una carpeta llamada presupuestos con varias versiones del presupuesto en formato excel.
• Queremos analizar cómo varían los precios en función de ciertos parámetros del proyecto, poder hacer comparativas, etc.
• Sin Power Query tendríamos que ir proyecto a proyecto copiando las celdas que nos interesen en nuestro nuevo excel resumen.
• Con Power Query:
  1. En Datos > Obtener Datos > Desde un libro de excel, y elegimos uno de los presupuestos como ejemplo.
     • Se abrirá Power Query con una nueva consulta que nos mostrará una tabla con todas las hojas, tablas y rangos con nombre que contiene el presupuesto.
  2. Elegimos la tabla que nos interese.
  3. Borramos columnas, creamos columnas nuevas con cálculos que queramos hacer, cambiamos datos de formato, etc
  4. Creamos una nueva consulta pero en vez de un libro de excel, de todos los archivos que encuentre en la carpeta de proyectos.
  5. Aplicamos filtros para quedarnos sólo con archivos de excel que tengan la palabra presupuesto en el nombre.
     • Ahora tenemos dos consultas:
       • Una consulta con todos los pasos que hay que hacerle a un presupuesto en concreto.
       • Una consulta que es un listado de todos los presupuestos.
  6. Podemos decirle a Power Query que aplique todos los pasos de la consulta uno a cada fila (presupuesto) de la consulta dos.
     • Obtendremos una tabla super larga con todos los datos de todos los presupuestos.
  7. Cargamos esa tabla en excel y la usamos como base para nuestras tablas dinámicas y gráficos.
     • Un presupuesto cambia, o se añade un nuevo archivo de presupuesto, lo tendríamos todo actualizado sin hacer nada.
• Me he saltado algún paso y he simplificado otros para que fuera más fácil de seguir.
  • Pero con un poco de práctica se pueden conseguir cosas así o incluso más complejas.
  • Por ejemplo, que hubiera dos formatos distintos de presupuesto.
    • Detectamos el tipo de presupuesto que es y a cada uno le aplicamos unos pasos distintos, para que al final sean compatibles.

Aprender Power Query

• Acadevor (en español)
  • https://acadevor.com/p/curso-excel-nivel-2/
• Xelplus Leila Gharani (en inglés pero explica muy muy bien)
  • https://www.xelplus.com/category/power-query/
• En relación esfuerzo de aprendizaje vs resultados y posibilidades, es la herramienta más agradecida que he usado nunca.
• Y recuerda que todo esto te sirve tal cual para Power BI.

¿Quieres escuchar otro episodio? Los tienes todos en la sección de Podcast de esta web.

AVISO: Este post es sólo un apoyo al audio del podcast. Leerlo de forma independiente podría llevar a conclusiones incompletas o incluso opuestas a las que se quieren transmitir.