El ADN de Power Query by Miguel Caballero Sierra

EL ADN de Power Query

Miguel Caballero Sierra Consultor, Formador y Autor en Business Intelligence

Fabian Torres Hernรกndez Consultor, Formador y Autor en Business Intelligence

Datos de Catalogación Bibliográfica MIGUEL CABALLERO & FABIAN TORRES El ADN de Power Query. Primera Edición COLECCIÓN ADN, Colombia 2019. ISBN: 978-958-46-8524-7 Área: Software de Oficina Formato: 21.50 cm x 27.94 cm

Páginas: 642

Versión digital e impresa autorizada por la marca Excel Free Blog en excelfreebymcs@gmail.com, Titulada: El ADN de Power Query. 1 ra ed., (Capítulo 1 al 13 + 2 Anexos) por: Ing. Miguel A. Caballero Sierra e Ing. Fabian E. Torres Hernández, autores de la Obra. Perteneciente a www.excelfreeblog.com en su Colección ADN y distribuido por los mismos, sus afiliados y aliados de comercio electrónico.

El ADN de Power Query © Copyright por Excel Free Blog. Todos los Derechos Reservados │ Publicado: 23/5/2019 ISBN: 978-958-46-8524-7 PRIMERA DISTRIBUCIÓN DE LA VERSIÓN DIGITAL, PRIMERA IMPRESA. 23/05/2019.

Edición en Español Autores: Miguel Caballero Sierra Fabian Torres Hernández

Editor: Miguel Caballero Sierra Diseño de Portada: María Alejandra Ramírez

Ninguna parte de este producto puede ser reproducida, almacenada, por ningún sistema de obtención de información o transmitido en ninguna forma: electrónica, mecánica, fotocopia, fotoquímica, grabación, escaneo o ninguna relacionada sin el permiso previo de los autores o autorización mediante el pago previo de impuestos y adjudicación de derechos de autor. DECLARACIÓN DE GARANTÍA: LOS AUTORES, EDITORES DISTRIBUIDORES NO INCLUYE NINGUNA GARANTÍA O CONVENIENCIA RESPECTO A LA PRECISIÓN O INTEGRIDAD DEL CONTENIDO DE ESTE TRABAJO Y ESPECIFICAMENTE RENUNCIAN A TODA GARANTÍA, INCLUIDAS LAS GARNATÍAS DE LIMITACIÓN DE ADECUACIÓN PARA UN PROPOSITO PARTICULAR. NINGUNA GARANTÍA PUEDE SER CREADA O AMPLIADA POR VENTTAS O MATRIALES PROMOCIOANALES. LOS CONSEJOS Y ESTRATEGIAS AQUÍ CONTENIDO PUEDEN NO SER CONFORMES PARA CADA SITUACIÓN. ESTE TRABAJO SE VENDE CON EL ENTENIDMIENT DE QUE LOS AUTORES, EDITORES Y DISTRIBUIDORES NO ESTAN INVOLUCRADOS EN LA PRESTACIÓN DE SERVIOOS LEGALES, CONTABLEES U OTROS SERVICIOS PROFESIONALES. SI UN SERVICIO PROFESIONAL ES REQUERIDO, EL SERVICIO DE UNA PERSONA PROFESIONAL DEBERÍA SER BUSCADO. NI LOS EDITORES NI LOS AUTORES SERÁN RESPONSABLES DE LOS DAÑOS Y PERJUICIOS QUE DEREIVEN DE ELLO. El HECHO DE QUE UNA ORGANIZACIÓN O SITIO WEB SEA REFEREIDA EN ESTE TRABAJO COMO CITA O POTENCIAL FUENTE DE INFORMACIÓN NO SIGNIFICA QUE LOS AUTORES O EDITORES RESPALDEN LA INFORMACIÓN QUE LA ORGANIZACIÓN O SITIO WEB PUEDAN PROPRCIONAR O RECOMENDAR. ADEMÁS, LOS LECTORES DEBEN TENER EN CUENTA QUE LOS SITIOS EN INTERNET LISTADOS EN ESTE TRABAJOL INTERVALO DE TIEMPO EN QUE ESTE TRABAJO ES REALIZADO Y CUANDO ES LEEIDO.

Para información general o de nuestros otros productos por favor contactar a: excelfreebymcs@gmail.com ISBN: 978-958-48-3993-0 │ Edición 1, Distribución 1: 26/05/2019.

Contenido Breve

Introducción …………………………………………………………………………….. xxi

Parte I: Conocimiento Esencial en Power Query 1

Fundamentos y Preparación de Datos Inteligente ……………………………………. 46

Ingredientes Esenciales para Trabajar con Power Query……………………………... 70

Conectores que Pueblan Power Query ……………………………………………….. 98

Parte II: Limpieza y Adecuación de Datos 4

Limpieza de Datos de Primer Grado, Técnicas de Depuración ……………………….116

Aplicaciones Superiores con Técnicas de Depuración ……………………………….192

Materialización Alterna de Tablas con Modos de Dinamización …………..………… 286

Grados de Libertad y Pseudo Generalización NxM ………………………………...…344

Transformaciones Superiores con Modos de Dinamización ……………………….... 368

Parte III: Integración de Datos 9 Anexar ……………………………………………………………………………....404 10 Combinar (Tipos de Joins) …………………………………….………………….... 466

Contenido Breve Parte IV: Lenguaje de Funciones de Power Query: M 11 Elementos y Herramientas Primordiales para Trabajar en M…………………………546 12 Expresiones, Declaración de Variables y Literales………………………………...….566 13 Tipos de Datos sus Operadores y Valores Intrínsecos……...………………….…..…582 14 Ambiente ………………………………………………………………….……………. 15 Librería de Funciones Atómicas ………………………………………………………… 16 Librería de Funciones Estructuradas …………………………………..……..………… 17 Funciones Personalizadas y Función Anónima ……………..………………………...… 18 Bucle "Do-While" y/o "While-End" en M…………………………………..…………….... 19 Aplicaciones con el Lenguaje M ……………………………………………………….... Epílogo ………………………………………………………………………...……dcxxii

Anexos A. Anexo A: Topología de Tablas ………………………………...……........………….627 B. Anexo B: Intellisense para Excel……………………………....………...…...………631

→

←

Contenido Detallado Contenido Detallado ............................................................................................................. ix Introducción ......................................................................................................................... xxi Acerca de Este Libro ........................................................................................................................ xxii Es Este Libro Para Ti ........................................................................................................................ xxiii Versiones de Software ................................................................................................................... xxiv Convenciones Utilizadas en el Libro .............................................................................................. xxv Cómo Está Organizado Este Libro ................................................................................................. xxx Descripción de Capítulos ............................................................................................................... xxxi Acerca de la Colección ADN ....................................................................................................... xxxvii Perfila Tu Viaje Por Power Query................................................................................................. xxxix Archivos de Trabajo....................................................................................................................... xxxix Programa de Actualización de Contenido .......................................................................................xl

PARTE I: Conocimiento Esencial en Power Query........................... 43 Capítulo 1: Fundamentos y Preparación de Datos Inteligente ........................................ 45 El Binomio Perfecto ............................................................................................................................... 46 La Distorsión Innegable .................................................................................................................... 46 Prólogo del Final y el Inicio .............................................................................................................. 48 Ejemplificación y Casos Comunes....................................................................................................... 49 Formato de Tabla Dinámica a Formato Tabular ............................................................................ 49 Eliminar Filas en Blanco y Según Criterio ....................................................................................... 53 Power Query .......................................................................................................................................... 56 ¿Qué es Power Query?...................................................................................................................... 56 Proceso ETL........................................................................................................................................ 59 Inteligencia de Negocios ...................................................................................................................... 60 El Proceso de Inteligencia de Negocios.......................................................................................... 61 El Lugar de Power Query .................................................................................................................. 62 Crónicas Power Query .......................................................................................................................... 63 Data Explorer ..................................................................................................................................... 63

Contenido Detallado Power Query ...................................................................................................................................... 63 Power BI Designer ............................................................................................................................. 64 Obtener y Transformar ..................................................................................................................... 64 SSDT, SSAS, CDS y MS Flow............................................................................................................ 65 El Siguiente Paso ......................................................................................................................... 66

Capítulo 2: Ingredientes Esenciales para Trabajar con Power Query ............................. 69 Conceptos Fundamentales .................................................................................................................. 70 Preparación de Datos ....................................................................................................................... 70 Entendimiento de Datos .................................................................................................................. 71 El Proceso ETL.................................................................................................................................... 72 Consulta: La Esencia de Power Query ............................................................................................ 75 El Gran Objetivo: Formato Tabular ................................................................................................. 77 El Editor de Power Query...................................................................................................................... 80 ¿Cómo ir al Editor de Power Query? ................................................................................................. 80 La Interfaz de Power Query: ¡El lugar donde la Magia Ocurre! ...................................................... 83 Metodología Jerárquica A/O ................................................................................................................. 90 Acciones y Operaciones ................................................................................................................... 91 El Siguiente Paso ......................................................................................................................... 94

Capítulo 3: Conectores que Pueblan Power Query (Orígenes & Categorías) ................ 97 Introducción ........................................................................................................................................... 98 Orígenes de Datos Estándar ................................................................................................................ 99 Conector Excel: Pros, Con y Consideraciones ............................................................................... 99 Conector Texto o CSV ................................................................................................................... 109 Otros Orígenes ................................................................................................................................... 110 Orígenes de Bases de Datos Relacionales .................................................................................. 110 Orígenes de Datos en Línea .......................................................................................................... 110 El Siguiente Paso ....................................................................................................................... 110

PARTE II: Limpieza y Adecuación de Datos .................................... 113 Capítulo 4: Limpieza de Datos de Primer Grado, Técnicas de Depuración ..................115 El Primer Paso ..................................................................................................................................... 116 Entendimiento de los Datos ............................................................................................................. 117 Perfil de Datos ................................................................................................................................ 122 Transformación de Datos (Técnicas de Primer Grado) ..................................................................... 128

xi Reducción: Exclusión de Filas ......................................................................................................... 130 Reducción: Promover Encabezados ............................................................................................... 143 Reducción: Quitar Columnas .......................................................................................................... 144 Formateo de Datos: Dividir Columnas........................................................................................... 150 Formateo de Datos: Reemplazar los Valores ................................................................................ 160 Formateo de Datos: Transformación a Nivel de Columna ............................................................ 164 Formateo de Datos: Asignación de Nombres ................................................................................ 176 Transposición ................................................................................................................................. 184 El Siguiente Paso ....................................................................................................................... 190

Capítulo 5: Aplicaciones Superiores con Técnicas de Depuración ................................193 Introducción ........................................................................................................................................ 194 Agregar Columnas .............................................................................................................................. 195 Columnas a Partir de Ejemplos..................................................................................................... 196 Columna Condicional .................................................................................................................... 206 Columna Personalizada ................................................................................................................. 210 Otras Columnas ............................................................................................................................. 215 Operación de Rellenar: Uso y Consideraciones ................................................................................. 220 Limpieza de Datos Inicial............................................................................................................... 222 Rellenar: Aplicación y Consideraciones ........................................................................................... 231 Zipped Table: Manipulación de Datos Comprimidos .......................................................................... 236 Bonus: Agrupar Por ........................................................................................................................ 260 División de Columnas con Múltiples Criterios Dinámicos ............................................................ 263 El Siguiente Paso ....................................................................................................................... 284

Capítulo 6: Materialización Alterna de Tablas con Modos de Dinamización ...............287 Introducción ........................................................................................................................................ 288 Modos de Dinamización................................................................................................................ 289 Técnicas de Segundo Grado ......................................................................................................... 289 Anulación de Dinamización (Unpivot) ............................................................................................. 290 Mecánica de Anulación de Dinamización ................................................................................... 291 Recomendaciones y Buenas Prácticas ........................................................................................ 298 Unpivot: Un Nivel de Encabezado de Columna ......................................................................... 306 Unpivot: Dos Niveles de Encabezados Columna ....................................................................... 308 Un Pivot: Dos Niveles de Rótulo de Fila ...................................................................................... 321

xii

Contenido Detallado Columna Dinámica (Pivot) ................................................................................................................ 325 Mecánica de Columna Dinámica (Versión Simple) .................................................................... 326 Recomendaciones y Buenas Prácticas ........................................................................................ 328 Pivot (Columna Dinámica): Versión Simplificada ...................................................................... 329 Mecánica de Columna Dinámica (V. Expandida) ....................................................................... 333 Pivot (Columna Dinámica): Versión Extendida .......................................................................... 335 El Siguiente Paso ....................................................................................................................... 342

Capítulo 7: Grados de Libertad y Pseudo Generalización NxM ......................................345 Preámbulo ........................................................................................................................................... 346 Grados de Libertad ............................................................................................................................. 347 Entendiendo Grados de Libertad ................................................................................................. 347 Perdida de Información y Flexibilidad .......................................................................................... 350 Pseudo Generalización ...................................................................................................................... 355 3 encabezados y 3 Rótulos ........................................................................................................... 355 Pseudo Generalización NxM ......................................................................................................... 365 El Siguiente Paso ....................................................................................................................... 366

Capítulo 8: Transformaciones Superiores con Modos de Dinamización .....................369 Prefacio ................................................................................................................................................ 370 Stacked Table: Tratamiento de Tablas Apiladas................................................................................. 371 Nombre de campos coincidentes ................................................................................................ 372 Nombre de campos no coincidentes (Tabla de conversión) ....................................................... 382 Bulky Table: Posibles Manejos a Tablas Abultadas............................................................................. 392 Patrón Claro en los Nombres de Campos ................................................................................... 393 No Hay Patrón en los Nombres de Campos ............................................................................... 399 El Siguiente Paso ....................................................................................................................... 402

PARTE III: Integración de Datos ...................................................... 405 Capítulo 9: Anexar ..............................................................................................................407 Introducción ........................................................................................................................................ 408 ¿Qué es la Integración de Datos?................................................................................................. 408 ¿Qué es Anexar?.................................................................................................................................. 409 Anexar Consultas................................................................................................................................ 413 Anexar dos o Más Consultas ........................................................................................................ 413 Anexar Varias Hojas de un Archivo de Excel ............................................................................... 428

xiii Anexar desde una Carpeta ................................................................................................................ 438 Anexar desde una Carpeta –Archivos únicos en la Carpeta ..................................................... 439 Anexar desde Carpeta – con Archivos Extra .............................................................................. 449 El Siguiente Paso ....................................................................................................................... 466

Capítulo 10: Combinar y Tipos de Joins............................................................................469 Preámbulo ........................................................................................................................................... 470 ¿Qué es Combinar? ........................................................................................................................ 470 Combinar Dos Tablas ..................................................................................................................... 472 Combinar Dos Tablas: Relación Muchos a Muchos ................................................................... 484 Combinar con Múltiples Columnas Coincidentes ..................................................................... 493 Tipos de Uniones / Types of Joins ..................................................................................................... 502 Tabla Izquierda / Tabla Derecha.................................................................................................... 507 Externa Izquierda (Todas de la Primera, Coincidencias de la Segunda) ................................. 509 Externa Derecha (Todas de la Segunda, Coincidencias de la Primera) ................................... 512 Externa Completa (Todas las Filas de Ambas)............................................................................ 516 Interna (Todas las Filas Coincidentes)......................................................................................... 520 Anti Izquierda (Solo Filas de la Primera) ..................................................................................... 524 Anti Derecha (Solo Filas de la Segunda) ..................................................................................... 528 Bonus: Coincidencia Difusa .......................................................................................................... 534 El Siguiente Paso ....................................................................................................................... 544

PARTE IV: Lenguaje M....................................................................... 547 Capítulo 11: Elementos y Herramientas para Trabajo con el Lenguaje M ....................549 Interfaz y Vías de Acceso................................................................................................................... 550 UI para Programar en M ................................................................................................................ 551 Entorno del Editor Avanzado ............................................................................................................ 554 Opciones de Presentación ............................................................................................................ 556 Reglas de Formato ......................................................................................................................... 558 Debugging (Depuración de Programa) ........................................................................................... 560 Depuración integrada .................................................................................................................... 561 Post-mortem debugging ............................................................................................................... 563 Consideraciones ................................................................................................................................. 565 El Siguiente Paso ....................................................................................................................... 566

Capítulo 12: Expresiones, Declaración de Variables y Literales ....................................569

xiv

Contenido Detallado Introducción ........................................................................................................................................ 570 Escribir M desde Cero (Consulta en Blanco) .............................................................................. 570 Expresiones ......................................................................................................................................... 572 Expresión: «let» .............................................................................................................................. 572 Expresión: «in» ............................................................................................................................... 573 Declaración de Variables ................................................................................................................... 574 Variables, Expresiones y Sub-expresiones................................................................................... 576 Representación en el Editor .......................................................................................................... 577 Acera de Variables: Identificador y Nombres .............................................................................. 578 Literales ............................................................................................................................................... 579 Construcción de tipo de datos en M ........................................................................................... 580 Comentarios ................................................................................................................................... 580 El Siguiente Paso ....................................................................................................................... 582

Capítulo 13: Tipos de Valores sus Operaciones y Valores Intrínsecos .........................585 Clases de Valores................................................................................................................................ 586 Valores Primitivos (Atómicos) .......................................................................................................... 587 Valores: Numéricos ........................................................................................................................ 587 Valores: Fecha................................................................................................................................. 591 Valores: Hora .................................................................................................................................. 594 Valores: Fecha y Hora .................................................................................................................... 597 Valores: Fecha, Hora y Zona Horaria............................................................................................ 598 Valores: Duración ........................................................................................................................... 602 Valores: Texto ................................................................................................................................. 605 Valores: Lógico ............................................................................................................................... 607 Valores: Nulo (null) ........................................................................................................................ 609 Valores: Binarios ............................................................................................................................. 612 Operadores de Tipo: Compatibilidad y Aserción ............................................................................ 613 Operador is ..................................................................................................................................... 614 Operador as .................................................................................................................................... 614 Valores Estructurados (Compuestos) ............................................................................................. 615 Valores: Lista y Operador de Índice Posicional ............................................................................... 616 Valores: Registros (Record) y Operador de Búsqueda ................................................................... 621 Valores: Tabla (Table) .................................................................................................................... 625

xv Comentarios Finales ................................................................................................................. 626

EpÃlogo ......................................................................................... dcxxvii Anexos ............................................................................................... 629 Anexo A ................................................................................................................................631 Anexo B ................................................................................................................................635

xvi

Contenido Detallado

xvii

Sobre los Autores MIGUEL CABALLERO SIERRA: Es formador, consultor, coach y autor en inteligencia de negocios y analítica de datos utilizando las tecnologías de Microsoft BI y sus partners de software libre. Entrena a miles de personas anualmente en las diversas ciudades Colombia, ello gracias a la vinculación con empresas privadas, su gestión mediante la web Excel

Ing. Miguel A. Caballero S.

Free Blog y su empresa Escuela de Inteligencia de Negocios SAS. Miguel, También se ha ido erigiendo como un autor prolífico gracias a su coautoría en los libros Inteligencia de Negocios con Excel y Power BI, Tablas Dinámicas la Quinta Dimensión y El ADN de Power Pivot, este último posicionado como uno de los mejores títulos en habla hispana en la bibliografía para aprender y dominar Power Pivot y DAX para Excel. El también escribió Funciones Primordiales en Excel, un minilibro en versión beta que enfatiza en las funciones claves de Excel para generar un diferencial ágilmente. Es pionero en la enseñanza y divulgación de Power BI, Power Pivot con lenguaje DAX y Power Query con lenguaje M en habla hispana. Ingeniero industrial por formación y profesional Excel developer por entrenamiento, ha desarrollado proyectos en el sector textil, financiero y comercial, así mismo ha trabajado en el área de retribución salarial creando y validando modelos de compensación fija y variables apoyado en hojas de cálculos sofisticadas.

xviii

Sobre los Autores

FABIAN TORRES HERNÁNDEZ: Es creador de contenido, escritor y consultor en análisis de datos utilizando Excel y la suite de herramientas de Microsoft BI. Miembro del equipo en EFB y Coautor de los libros: Tablas Dinámicas La Quinta Dimensión y El ADN de Power Pivot e Inteligencia de negocios con Excel y Power BI.

Ing. Fabian E. Torres H.

Fabian es egresado como ingeniero Industrial, desarrollador de Cuadros de Mando, reportes Inteligentes e informes interactivos, adicionalmente, es empresario y líder en la compañía Escuela de Inteligencia de Negocios SAS. Él ha ejecutado variedad de entrenamientos con empresas privadas y grupos independientes, gracias al trabajo continuo en Excel Free Blog. Fabian es entusiasta en visualización de datos y se especializa en la optimización a través de la comunicación mediante gráficos, objetos visuales y herramientas de diseño, no menos importante, ha dirigido la vanguardia en la propagación de conocimiento de Power Query y lenguaje M en español y Power BI para representación visual de información.

Dedicatorias Miguel Caballero Sierra, A la persona que me hizo mejor, a quien me ayudo a convertirme en quien siempre quise ser, a mi esposa … Gretzy Parra Marín No sólo te amo por quién eres, sino por lo que has hecho de mí, no por tu belleza que es tan fácil de ver, sino por haberme completado.

Fabian Torres Hernández Para Enith, mi madre, por mostrarme su amor infinito y enseñarme el increíble poder de luchar por los sueños …

Sobre los Autores

xxi

Introducción Lo que estás a punto de ver parece una tecnología sacada de las páginas de una novela Ciberpunk de Ernest Client, de hecho, si alguien nos hubiera relatado las maravillas de Power Query 10 años atrás, nuestros oídos hubieran dado más crédito a grifos, dragones y titanes colosales si alguien hubiera manifestado su hallazgo. — ¿Es así de revolucionario lo que se trata en el libro? … Y por mucho. Su riqueza es de órdenes de magnitud astronómicos, es más, afirmar que Power Query es una mastodóntica y exponencial funcionalidad especializada para la limpieza de datos, sería pecar de comedido. Power Query puede hacer prender el fuego en la mente de los hombres, arrancarles lágrimas a los corazones más duros, minar los problemas más complejo y alfombrar tus habilidades con técnicas profesionales para alcanzar el cénit de cualquier carrera laboral. Por lo anterior y mucho más, invertir en la tecnología por excelencia para preparación de datos es una decisión de ganadores, y así como un barco no deja rastro en el olaje, cualquier ápice de duda se desvanecerá con total certeza desde el primer momento que nos adentremos en el estudio de las páginas que yacen en El ADN de Power Query, y dará lugar a una intensa curiosidad y una martilleante sensación de exploración y descubrimiento. Power Query es una tecnología descomunal sin fronteras ni limitaciones, ya que permite: entender, explorar, preparar, adecuar, limpiar, formatear, integrar, enriquecer, crear, refinar, formular y hasta resumir datos de forma veloz, intuitiva y coherente. En conclusión: hacer milagros con los datos.

¡Bienvenido al 2040!, al OASIS. — (Eso sí, sin la parte distópica)

xxii

Acerca de Este Libro

Acerca de Este Libro ¡Metodología de acciones y operaciones!

El presente libro es una obra cuya generatriz es la lógica, a saber, no está destinado a brindar recetas y soluciones del estilo “copy/paste”, no es el corazón del libro por lo menos, en su lugar, este texto oscila en desarrollar una explicación de exactitud milimétrica para conferirnos una compresión profunda de cada aspecto tratado de Power Query, todo ello, para resolver no solo los escenarios expuestos en sus páginas, sino además, dotarnos del raciocinio suficiente para dar solución a todo tipo de problemas y variaciones. Por esta razón, hemos venido desarrollando y mejorando con el pasar de los años la metodología jerárquica de acciones y operaciones, que, básicamente consiste en una GPS del largo y ancho espectro de comandos que componen a Power Query, para así lograr: preparar, limpiar, integrar y enriquecer cualquier base de datos.

xxiii

Es Este Libro Para Ti Ninguna persona está exenta de los favores de Power Query si trabaja con datos, así sea en escalas diminutos, más aún, si tienes que manipular los mismo para llegar a un formato adecuado, crear tablas dinámicas, análisis, reportes y cuadros de mando. Algunas personas con una peculiar falta de tino creen que al estar contacto mayoritariamente con gestores de bases de datos como: SQL, PostgreSQL, Oracle, etc. No deben molestarse de pasar ni un nanosegundo en la parte de limpieza de datos, pero la realidad va más allá, al ser Power Query una parte integral del proceso de inteligencia de negocios en algún punto su manipulación facilitará los desarrollos. ¡Este es un libro para todos!

Por otra parte, este libro está diseñado tanto para usuarios de Excel como para usuarios de Power BI que quieran catapultarse a fronteras inimaginables, llegar a otra dimensión.

xxiv

Acerca de Este Libro

Versiones de Software Power BI Desktop / Excel 2019 y 2016 • No Aplica Totalmente para Excel 2013

La temática aquí tratada es desarrollada en un 90% en Power BI de escritorio para su actualización de abril del 2019, no obstante, se dejan referencias claras a versiones anteriores de Power BI donde las funcionalidades podían aún no existir, de la misma manera se dejan referencias claras a Power Query para Excel 2019 y 2016, por lo tanto, el temario de este libro aplica a: Power BI de escritorio, Excel 2019 y 2016.

▪

Power BI de Escritorio: La versión utilizada es la correspondiente al mes de abril del 2019, cualquiera posterior a esa fecha encaja a la perfección.

▪

Para Excel: Aplica perfectamente a Excel 2019 y sus actualizaciones asociadas al primer trimestre del 2019, igualmente aplica para Excel 2016, donde se dejan referencias claras si existen diferencias. Si bien no se hace mucha referencia a Power Query de Excel 2013 ni 2010, gran parte de la temática que se estudia a lo largo de este texto también aplica a estas versiones de Excel, sin embargo, se debe tener en mente que las funcionalidades más recientes no están implementadas.

▪

MS Flow y Common Data Services for Apps: ¿Quieres aplicar Power Query para Microsoft Flow o CDSA?, las partes 1 a 3 será lo que mejor encaje para ti de este libro, excluyendo todo aquello que implique el lenguaje M.

xxv

Convenciones Utilizadas en el Libro Ayudas Visuales, Esquemas y Otros Elementos En el libro encontrarás elementos especiales para enriquecer la explicación y para brindar una lectura más amena. Cuadros Principales:

▪

Pausemos un momento: Es una pausa activa para señalar temáticas externas, otros recursos, aclarar conceptos, resaltar conclusiones de lo que se está tratando en este momento o mencionar algún punto relacionado con el tema o capítulo.

Pausemos un Momento La técnica anteriormente descrita se aplica para eliminar valores de error, valores faltantes o según criterio. El único que varía de los tres pasos es el primero, ya que el valor filtrado se haría por el deseado y no por vacío. (También es licito pensar en Macros y automatizar el

proceso, tomando ventaja de la grabadora es una encomienda de complejidad moderada)

▪

¡Tom nota!: Como su nombre lo indica, este es un cuadro clave para recalcar cosas importantes, puede ser una conclusión, un concepto, un ejercicio, etc.

¡Toma Nota! Para agregar saltos de línea entres sentencias de código en el editor avanzado de Power Query, es suficiente con utilizar la combinación de teclas: Shift + Enter. Para indexar utiliza la tecla Tab para mayor rapidez.

xxvi

Acerca de Este Libro ▪

¡Riesgo: Advertencia! Señalan posibles obstáculos que puedas encontrar, un peligro inminente del cual debes blindarte (Por eso la máscara de gas en el icono del recuadro). Presta atención, te alertan de problemas que de otra manera te podrían tomar horas o días.

¡Riesgo: Advertencia! Ofreciendo una sonrisa bajos cero y gesto de despreocupación un desarrollador del equipo de Microsoft para Power Query han manifestado que el comando A.D.C será erradicado. Es perfectamente válido traer a colación que si llega a suceder sabrás que en realidad con el comando A.D.O.C es suficiente. (En nuestra

opinión tenemos reservas de que ocurra)

▪

¡Referencia Cruzada! Este cuadro es el que aparece con menor frecuencia en el libro, no obstante, tiene como objetivo brindar bibliografía a otro libro de la colección ADN, aparecen como sugerencia si se desea profundizar aún más en la temática que se está tratando en el momento.

¡Referencia Cruzada! La parte de creación de: indicadores, KPIs, KRIs y métricas para el análisis de datos es territorio del lenguaje DAX, que corresponda a la segunda etapa de proceso de inteligencia de negocios, todo esto es tema de otro libro de la Colección ADN: El ADN de Power Pivot ISBN: 978-958-46-8542-7

xxvii

Cuadros Secundarios:

▪

Let’s Play: Este cuadro señala donde encontrar el archivo de trabajo, para esa sección o apartado que viene incluido en el compendio de archivos.

¡Let’s Play! ARCHIVO DE TRABAJO: En el compendio de archivos y carpeta

Capítulo 4 puedes encontrar el archivo de Excel con nombre: CAP4EJ1 – Ventas_Pedidos.xlsx.

▪

Descargar:

Este cuadro señala un enlace externo para descarga de material

complementario que no se encuentra en el compendio de archivos.

¡Descargar! Para cualquier consulta de la lista de criterios a cumplir para el formato tabular se puede descargar el siguiente check list: http://bit.ly/2X7y3IQ

▪

«Definición»: Este cuadro brinda la definición de un nuevo concepto que es de particular importancia para el libro en general.

Valores Numéricos Un tipo de valor numérico en el lenguaje M que es utilizado para llevar a cabo operaciones aritméticas. Estos valores incluyen: número entero, entero exponencial, fracciones, fracciones exponenciales y numero hexadecimal.

xxviii ▪

Acerca de Este Libro Conceptos de Fuego: Los conceptos de fuego son una serie de términos que hemos creados lo autores del libro, por ello los enmarcamos en recuadro propio y diferente, para distinguirlo de los conceptos universales del desarrollo teórico a los del equipo de EFB.

Conceptos de «Fuego» | Acciones y Operaciones La metodología jerárquica de acciones y operaciones ha sido nuestro esfuerzo en categorizar cada actividad que podemos realizar en Power Query, si bien, aún seguimos trabajando en ello, a continuación, veremos de que se trata.

▪

Resaltado: Este elemento no es un recuadro, en su lugar, con el tratamos de emular el resaltado que hacemos manualmente cuando leemos un libro, aquí simplemente iluminamos aquellos párrafos que nosotros resaltaríamos.

Las operaciones que presentaremos en breve: anulación de dinamización y columna dinámica

eclosionaron de una

fundamental: La transposición, en aras de resolver formatos de tablas con etiquetas de jerarquías para categorizar columnas (También las llamamos niveles de encabezados).

xxix

Elementos Complementarios:

Tablas: Encontrarás tablas para compactarán información, especialmente, para resumir parámetros y descripción de funciones

Código M: Encontrarás las líneas de código M con un fondo acompañado del icono: Código M para fácil identificación.

Notas de costado: Son como las notas principales, pero utilizadas por comodidad.

Ayudas visuales: Mini notas explicativas para enriquecer la explicación de imágenes

xxx

Acerca de Este Libro

Cómo Está Organizado Este Libro Este libro esta divido en cuatro grandes partes, cada uno agrupa una serie de capítulos de acuerdo con su afinidad según la temática.

▪

Parte I: Conocimiento Esencial en Power Query (Capítulo 1 a 3) Esta primera parte está compuesta por tres capítulos, donde se proporciona todo el contexto, fundamentos y base teórica para trabajar con Power Query.

▪

Parte II: Limpieza y Adecuación de Datos (Capítulo 4 a 8): Esta segunda parte está formada por 5 capítulos que orquestan el estudio y profundización de las operaciones asociadas a la acción de limpieza de datos, es decir: reducción, formateo, rellenado, transposición, anulación de dinamización y columna dinámica.

▪

Parte III: Integración de Datos (Capítulo 9 a 10): Esta tercera parte está constituida por 2 capítulos que nos envuelve en un recorrido riguroso en las operaciones que manipulan múltiples tablas, es decir, anexar y combinar, en resumidas cuentas, trata sobre la integración de datos.

▪

Parte IV: Lenguaje M (Capítulos 11 a 19): Esta cuarta parte está compuesta por 9 capítulos, aquí brindamos toda la información que conocemos acerca del lenguaje de funciones de Power Query, también conocido como: M. Véase nota de contenido resumido.

▪

Anexos (A, B): Al final del libro encontraras dos anexos, los cuales están destinados a resumir la topología de tablas estudiadas a lo largo del libro y brindar más información sobre el Intellisense para otras tecnologías distintas a Power BI.

xxxi

Descripción de Capítulos Cada capítulo es una aventura en sí misma, esto es así porque, en cada uno de ellos encontraras información preciosa para ser más productivos, además, hallaras huevos de pascua a otros libros, novelas e incluso series de televisión donde buscamos sorprender al lector que coincida con el gusto a estas referencias con nosotros. A continuación, descripción capítulo a capítulo:

PARTE I: Conocimiento Esencial Sobre Power Query ▪

Capítulo 1: En este capítulo mostramos al desnudo las limitaciones que llego a tener Excel cuando no contaba con Power Query en su arsenal, además, exploramos su historia y todas las tecnologías que hoy por hoy cuentan con la mejor funcionalidad para ETL.

▪

Capítulo 2: La base teórica pura y dura es la documentación de cada párrafo en este capítulo, un requisito obligatorio antes de cualquier otra cosa, saltarlo puede llevar a una comprensión cortada de los capítulos posteriores.

▪

Capítulo 3: El primer hito que pone Power Query a nuestra disposición es la posibilidad de tomar fuentes de datos de prácticamente cualquier origen, en este capítulo enfilamos detalle sobre los orígenes estándar.

xxxii

Acerca de Este Libro

PARTE II: Limpieza y Adecuación de Datos ▪

Capítulo 4: Nada menos que la preparación detallada y extendida de la clásica tabla: Pedidos, por ello, se estudia reducción de registros y campos, formateo, transposición, perfil de datos, división de columnas, configuración regional, proceso ETL entre muchas otras cosas.

▪

Capítulo 5: Casos reales utilizando el conocimiento edificado con las operaciones de limpieza de datos es el foco de estudio de este capítulo, donde no sólo se precisan advertencias a errores comunes, sino que también resuelve casos de mayor complejidad.

▪

Capítulo 6: En este capítulo se revela conocimiento que nos permitirá solventar escenarios de transformación que anteriormente requerían un trabajo extenuante, y que, a partir de ahora otorgaran nuestro toolkit de preparación y limpieza de datos con «artefactos» que parecen sacados de mundos mitológicos

▪

Capítulo 7: En este capítulo mostramos como la utilización de diferentes comandos pueden derivar en menor o mayor flexibilidad en el código interno generado por Power Query, adicionalmente, construimos pasos a paso el algoritmo general para darle solución al formato NxM.

▪

Capítulo 8: El tratamiento de formatos de tabla que cuentan con n encabezados de columnas con m rótulos de fila será la misión de capítulo 8, esto será la semilla para abordar cualquier tipo de tarea relacionada, adicionalmente, también deja el algoritmo para crear una función M personalizad más adelante.

xxxiii

PARTE III: Integración de Datos ▪

Capítulo 9: Hasta este puno en el libro no se profundiza en la manipulación de múltiples tablas para consolidarlas, por lo anterior, este capítulo marca el punto de partida real para aprender a resolver este tipo de situaciones tan recurrentes.

▪

Capítulo 10: Continua la misma línea que le capítulo 9, donde se ve como combinar tablas para crear una nueva según ciertos criterios de intersección o extensión de las consultas, es el capítulo que nos trae el detalle de los tipos de Joins.

PARTE IV: Lenguaje M ▪

Capítulo 11: Este capítulo marca la hora, por fin, de aprender sobre el fino arte de escribir líneas del lenguaje de funciones de Power Query, conocido más ampliamente como: M. Aquí se enmarca el conocimiento clave para trabajo óptimo con el Editor avanzado.

▪

Capítulo 12: Este capítulo es la iniciación a lenguaje M propiamente, donde se ve los bloques fundamentales de programación, cómo se manejan y consideraciones vitales.

▪

Capítulo 13: Entender el lenguaje M es entender cada tipo de valor que puede soportar, por lo tanto, las páginas de este capítulo se dedican a indagar y excavar en cada uno de ellos de forma clara, precisas y con ejemplos. Si bien en este capítulo se dejan unos cuantos tipos de valores por fuera, el grueso de ellos se ve en él.

xxxiv

▪

Acerca de Este Libro

Capítulo 14: La comunidad empieza a converger su opinión que el estudio del ambiente en el lenguaje M es uno de los conceptos más importantes, y nosotros no diferimos, es por dicha razón que este capítulo es una oda a uno de los conceptos menos obvios en el lenguaje de funciones de Power Query, pero de relevancia excelsa si realmente se deseas llegar a la experticia.

▪

Ver nota en Contenido Breve

Capítulo 15: Como si fuera un libro de funciones de Excel, en este capítulo se estudian las funciones atómicas del lenguaje M, para ver las más comunes, su sintaxis, parámetros y ejemplos.

▪

Ver nota en Contenido Breve

Capítulo 16: Siguiendo ese trazo del capítulo 15, en el presente se ven las funciones más complejas, es decir, aquellas asociadas a tipos de valores como: listas, registros y tablas.

▪

Ver nota en Contenido Breve

Capítulo 17: En el lenguaje M tenemos la posibilidad de crear funciones de nuestro puño y letra, dicho de otro modo, crear nuestras propias funciones. Dicha misión es la encomendad al capítulo 17, donde no sólo se crean desde la interfaz, sino que también se crean de cabo a rabo con lenguaje M. Este capítulo también es el hogar de estudio de la función anónima.

▪

Ver nota en Contenido Breve

Capítulo 18: Un concepto que se pierde en M es el de iteración o bucles de programación, básicamente porque ellos son inherentes a este lenguaje, no obstante, sí que podemos emular este comportamiento, por lo tanto, las páginas de este capítulo se dedican a ello.

▪

Ver nota en Contenido Breve

Capítulo 19: Al final el día, todo lo estudiado en la parte cuatro del libro tiene como objetivo este capítulo, ver aplicaciones de casos comunes y situaciones reales utilizando el lenguaje M.

Ver nota en Contenido Breve

xxxv

Anexos ▪

Anexo A: A lo largo del libro se dejan mini representaciones visuales de formatos comunes en tablas, en este anexo congrega todo para dar inicio al proyecto del estudio topológico de tablas.

▪

Capítulo B: Referencia para creación de un Intellisense que apoye Excel.

xxxvi

Acerca de Este Libro

xxxvii

Acerca de la Colección ADN Libros de Colección de EFB: El Mundo de Microsoft BI

La Colección ADN de EFB es un conjunto de libros encaminados a inteligencia de negocios y análisis de datos, para ello recorre las herramientas de Microsoft Business Intelligence principales con el objetivo de brindar el arsenal necesario para extraer buenas decisiones basadas en información obtenida de cantidades voluminosas de datos, de esta manera direccionar los objetivos de negocio de manera oportuna, precisa, accionable y con un fuerte valor agregado.

La Colección ADN de EFB está compuesta por los siguientes títulos:

▪ El ADN de las Tablas Dinámicas La Guía Definitiva de Análisis con Excel ▪ El ADN de Power Pivot DAX Aplicado al Análisis de Datos Efectivo ▪ El ADN de Power Query Power BI Aplicado a Limpieza de Datos Inteligente ▪ El ADN de Power BI Reportes, Cuadros de Mando y Distribución de Información

→

←

xxxviii

Acerca de Este Libro

xxxix

Perfila Tu Viaje Por Power Query Aunque este libro está concebido como un todo y cada capítulo es parte de un objetivo global. Está más que claro que este libro esta divido en cuatro partes.

▪

Conocimiento esencial.

▪

Limpieza de datos.

▪

Integración de datos.

▪

Lenguaje M.

Si el lector le urge aprender a consolidar múltiples tablas, puede arrancar con la Parte III sin ningún problema, no obstante, si tu rutina diaria te demanda adecuar y preparar tablas, recorre cada capítulo de la Parte II es buen inicio, del mismo modo, si lo que deseas es conocer acerca del lenguaje M la Parte IV es donde se enfocará tu atención.

Archivos de Trabajo Uno y cada uno de los archivos que se ven y trabajan a lo largo del libro se encuentran en el compendio de archivos:

COMPENDIO DE ARCHIVOS Incluye archivos de trabajo y documentos extra: • Clic Aquí para Descargar

http://bit.ly/2JKLRGv

Acerca de Este Libro

Programa de Actualización de Contenido La presente obra la hemos creado con un cariño gigantesco junto con una dedicación desmesurada, y, sobre todo: dejando la piel en la cancha, para así brindarte lo mejor. No obstante, minimizar a cero errores e inconvenientes de un libro de esta envergadura es una tarea compleja y de aproximaciones sucesivas.

– Programa de Actualización de Contenido: Por lo anterior este libro cuenta con algo llamado: Programa de Actualización de Contenido, es decir, que haremos correcciones y mejoras a este texto a lo largo del tiempo, para así mejorarlo y perfeccionarlo constantemente como un compromiso de los autores para dotarte con la mejor referencia de Power Query con Lenguaje M en habla hispana. Al adquirir este libro quedas registrado automáticamente en nuestra base de datos, por lo que, al momento de ocurrir una actualización te llegará un correo con todas las instrucciones sin ningún costo adicional.

– Distribuciones: Estas actualizaciones o versiones mejoradas del libro digital las llamamos Distribuciones y puedes identificar a cuál corresponde en la página ocho del PDF en la parte inferior.

xli

– Retroalimentación (Mejoras Sucesivas): Tu como el lector de este libro eres uno de los críticos más valioso, por esto es muy importante para nosotros escuchar de tu parte cualquier opinión, critica, corrección, sugerencia, mejoras, ilustraciones, consejos, complementos, anomalías etc. En cuyo caso puedes escribirnos al cualquiera de los siguientes correos electrónicos:

▪

E-mail 1: excelfreebymcs@gmail.com

▪

E-mail 2: fabian.torrres.h@outlook.com

También te invitamos que una vez finalices lectura de este libro o en el momento que lo consideres oportuno nos regales tu retroalimentación, es invaluable para nosotros, lo puedes hacer en nuestra página de Facebook:

▪

Facebook: https://www.facebook.com/ExcelFreeBlog/

▪

Instagram:

Capítulo 6

Capítulo 6: Materialización Alterna de Tablas con Modos de Dinamización

288

Capítulo 6 • Materialización Alterna de Tablas con Modos de Dinamización

Introducción Las operaciones que presentaremos en breve: anulación de dinamización y columna dinámica

eclosionaron de una

fundamental: La transposición, en aras de resolver formatos de tablas con etiquetas de jerarquías para categorizar columnas (También las llamamos niveles de encabezados). Por otro lado, sabemos que de la misma forma que un sumiller puede anticipar de forma astuta los matices de una añada de especial bouquet que pone delante del comensal, nuestro estimado lector vislumbrara que se avecina conocimiento que nos permitirá solventar escenarios de transformación que otrora requerían un trabajo extenuante, y que, a partir de ahora dotara nuestro toolkit de preparación y limpieza de datos con «artefactos» que parecen sacados de mundos mitológicos. — A Wizard did it! —

Pausemos un Momento Don Gustavo exclama en voz alta: «Esto lo hizo un mago, es magia». Es una corta anécdota de una formación que estábamos impartiendo por allá en el año 2016, cuando uno de nuestros estudiantes (Don Gustavo) dijo de forma inconsciente y para oídos de todos que la anulación de dinamización (tema de este capítulo) era magia; su primera impresión fue que dicha operación estaba materializando datos de la nada, sin embargo, después de la explicación comprendió que era una manera elegante de transposición. Por ello lo parafraseamos con: A wizard did it!

www.excelfreeblog.com

El ADN de Power Query

289

Modos de Dinamización Para comprender esta nueva consigna es crucial recalcar que tenemos dos operaciones:

▪ Anulación de Dinamización ▪ Dinamización (Columna Dinámica)

Una operación es la inversa de la otra y en ambos casos consiste en formas convenientes de transposición de los datos, que en términos generales es una invariancia traslacional.

NOTA La dinamización en la interfaz de Power Query se encuentra bajo el nombre de columna dinámica en su comando respectivo, en referencia directa a lo que pasaría con la columna a seleccionada si se agrega un área de colocación distinta a valores en una tabla dinámica

Acaparamos las dos operaciones bajo la sombrilla: Modos de dinamización, en esencia una manera «cool» de nombrarlas al unísono.

Técnicas de Segundo Grado En nuestro esquema de acciones y operaciones, todo aquello que implique los modos de dinamización aderezado con otras técnicas, métodos y trucos con el objeto de abrazar el formato tabular, las englobaremos con la etiqueta: técnicas de segundo grado. Materialización Alterna: Si bien en Power Query tenemos la

Re-Materialización

tabla visible de forma material, estas técnicas nos permitirán pasar de un formato con etiquetas de jerarquías en columnas al tabular, re-materializando la tabla con un aspecto alterno. www.excelfreeblog.com

290

Capítulo 6 • Materialización Alterna de Tablas con Modos de Dinamización

Anulación de Dinamización (Unpivot) La operación de la cual nos ocuparemos enseguida lleva por nombre anulación de dinamización, que en resumidas cuentas fue bautizada con tan florido nombre para hacer hincapié en

NOTA

que permite pasar del formato generado por una tabla

Llamamos tabla dinámica sencilla a aquella que solo tiene un campo en el área de columnas y puede tener uno o más campo en el área de filas.

dinámica sencilla al formato al tabular en la brevedad de un clic. En lugar de explicarte que realiza esta operación al son de palabra escrita, lo cual destacaría por su poco tino, optaremos por mostrártelo con una ilustración:

Formato Tabular (Formato) Tabla Dinámica

Iniciamos con la tabla de datos con un formato semejante al de una tabla dinámica.

Llegamos al ideal del formato

Figura 6. 1 – Esquema del Input y Output de Anulación de Dinamización

¡Toma Nota! Es ineludible dejar claro que la operación de anulación de dinamización consiste en revertir o anular el proceso natural que ocurre cuando se crea un reporte de tabla dinámica.

www.excelfreeblog.com

El ADN de Power Query Ahora que entendemos el fin último de la operación que nos atañe, es propicio comprender el funcionamiento interno o mecánica que sigue para llegar el resultado deseado, esto nos permitirá desarrollar una lógica exclusiva para resolver escenarios más complejos.

Mecánica de Anulación de Dinamización La operación de anulación de dinamización gravita en Power Query en 3 versiones, cada uno expresado en su propio comando, estos son:

▪

Anulación de Dinamización de Columnas (A.D.C)

▪

Anulación de Dinamización de Otras Columnas (A.D.O.C)

▪

Anulación de Dinamización de Columnas Seleccionadas únicamente (A.D.S.U)

Pausemos un Momento ¡En realidad solo son dos versiones! En la interfaz de Power Query contaremos con tres comandos, a pesar de esto, dos de ellas: ▪ ▪

Anulación de Dinamización de Columnas Anulación de Dinamización de Otras Columnas

Detrás de cámaras resulta siendo lo mismo, pero la interfaz de Power Query nos brinda dos opciones para eficiencia en el trabajo según nos convenga. A continuación, describiremos la mecánica de funcionamiento de las 3 versiones, pero desde el punto de vista visual y no netamente interno del motor M.

www.excelfreeblog.com

291

292

Capítulo 6 • Materialización Alterna de Tablas con Modos de Dinamización Entremos en materia desentrañando la mecánica de la primera vertiente:

Mecánica A.D.C.

Mecánica de Anulación de Dinamización de Columnas La anulación de dinamización de columnas ejecuta los siguientes

NOTA

pasos:

La explicación también la puedes estudiar de forma visual, gracias a que la lección del vídeo curso

Laboratorio de Datos con Power Query se encuentra disponible en YouTube.

1. Gira las etiquetas de las columnas seleccionaadas a valores, es decir, se transponen para pasar a ser elementos de una nueva columna que tendrá por nombre Atributo, sin embargo, esto ocurre n veces en la misma columna, donde n es el número de filas en la tabla inical. 2. Los elementos de las columnas seleccionadas a anular dinamización, se toman fila a fila y de forma completa para ser trasnpuestas una única vez uno debajo de otro en una columna nueva cuyo nombre será valor. 3. Las casillas vacías en las columnas que no se anula dinamización siguen la operación rellenar abajo. ¡Lo sabemos! es más fácil entender el lenguaje alien de la película arrival que la descripción anterior, sin emargo, apoyate del vídeo y del esquema siguiente para esclarecer:

www.excelfreeblog.com

El ADN de Power Query

Versiรณn: A.D.C

Inicio

Recordar remover: totales,

subtotales, filas en blanco, columnas en blanco y errores en primer lugar. Proceso / Mecรกnica

Final

Los nombres de columnas Atributos y Valor siempre se los asigna por defecto la operaciรณn, ya que no tiene manera de saber cuรกles son los nombres originales de dichos campos.

www.excelfreeblog.com

293

294

Capítulo 6 • Materialización Alterna de Tablas con Modos de Dinamización La mecánica de la segunda versión es descaradamente intuitiva una

vez

etendemos

plenamente

mecánica

funcionamiento de Anulación de Dinamización de Columnas, a pesar de ello, no esta demás dejar su descripción y esquema. Mecánica A.D.O.C

Mecánica de Anulación de Dinamización de Otras Columnas Es valido recalar que: La A.D.O.C es para todos los fines identica a A.D.C, de hecho su función M como resultado es la misma, sin embargo, la diferencia radica en la selcción de columnas en la interfaz. Esos son los pasos: 1. Gira las etiquetas de las columnas no seleccionadas a valores, en otras palabras, toma los nombres de los campos de aquellas columnas que no se seleccionaron y las transpone para pasar a ser elemntos de una nueva columna que tendrá por nombre: Atributo, esto se replicara n veces una debajo de otra en la misma columna, donde n es el número de filas en la tabla inical. 2. Los elementos de las columnas no seleccionadas, se toman como filas completa para ser trasnpuestas en una columna nueva que será denominada valor. 3. Las casillas vacías en las columnas seleccionadas siguen la operación rellenar abajo. El esquema será más explicativo:

www.excelfreeblog.com

El ADN de Power Query

Versiรณn: A.D.O.C

Inicio

No Olvidar Quitar: totales,

subtotales, filas en blanco, columnas en blanco y errores. Proceso / Mecรกnica

Final

Los nombres de columnas Atributos y Valor siempre se los asigna por defecto la operaciรณn, ya que no tiene manera de saber cuรกles son los nombres originales de dichos campos.

www.excelfreeblog.com

295

296

Capítulo 6 • Materialización Alterna de Tablas con Modos de Dinamización Podremos ser insistentes pero no queremos el mas minimo vestigio de duda, por ello recordemos: Las dos versiones de la operación anulación de dinamización anteriores son exactamente iguales internamente, esto es tan crucial que debemos tomar nota:

¡Toma Nota! Las versiones de la operación de anulación de dinamización: A.D.C y A.D.O.C. al final del día internamente desembocan en lo mismo, en otras palabras, el motor del lenguaje M arroja la misma función M, que sería algo del siguiente estilo:

= Table.UnpivotOtherColumns ( NombreTabla , {"País"} , "Atributo" , "Valor" )

Se debe destacar que las columnas a no anular dinamización en nuestro ejemplo es solo una: País y si representa en el segundo argumento como {“País”}, si son más de dos columnas se representa como {“Columna1”,”Columna2”, …}) De lo anterior podemos ver que la columna País es la queda fija en la función, por lo tanto, cada vez que se agreguen nuevas columnas en nuestro origen de datos a estas inexorablemente también se les anulará la dinamización.

www.excelfreeblog.com

El ADN de Power Query

297

Nuestro lector más espabilado en el recorrido de estas páginas le saltará a la vista la diferencia entre las dos vertientes anteriores y A.D.S.U. — ¿En qué se sustenta la diferencia?

Cuando se aplica el comando Anulación de Dinamización de Columnas Seleccionadas Únicamente (A.D.S.U) y luego llegan nuevos campos a la tabla original por actualización, a estas no se les anulara la dinamización, de donde se infiere que, las columnas a anular dinamización quedan fijas. Mecánica A.D.S.U

Mecánica de A.D.S.U El funcionamiento visual cotiza en el mismo ramo que el de Anulación de Dinamización de Columnas sin ninguna contraste aparante, en consecuencia, el esquema gráfico es el mismo. No obstante, sabemos que intermanete con el motor M su diferencia estriva en la actualización.

opinión tenemos reservas de que ocurra)

www.excelfreeblog.com

298

Capítulo 6 • Materialización Alterna de Tablas con Modos de Dinamización

Recomendaciones y Buenas Prácticas Antes de utilizar esta poderosa funcionalidad debemos percatarnos en una primera instancia de ciertas características en la tabla que alimentará a Power Query, específicamente de: Totales, subtotales, filas y columnas en blanco, así como de filas y columnas de error. Ley 0 Limpieza Inicial

La cuestión es simple, lo primero a realizar es remover:

▪

Totales generales (Si aplica)

▪

Subtotales (Si aplica)

▪

Filas en blanco (Si aplica)

▪

Columnas blanco (Si aplica)

▪

Filas de error (Si aplica)

▪

Columnas de error (Si aplica)

▪

Filas Duplicadas (Si aplica)

▪

Columnas Duplicadas (Si aplica)

▪

Etiquetas no congruentes en el contexto de los datos (Si aplica)

El archivo de trabajo que utilizaremos es el siguiente:

¡Let’s Play! ARCHIVO DE TRABAJO: En el compendio de archivos y carpeta

Capítulo 6 puedes encontrar el archivo de Excel con nombre: CAP6EJ1 - IngSemestreI y extensión xlsx.

www.excelfreeblog.com

El ADN de Power Query El archivo es el siguiente:

Figura 6. 2 – Ingresos Semestre I para limpieza inicial

Es bueno observar el formato desde su origen para conocer y entender que limpieza inicial necesitan. 4. Abrimos un archivo nuevo de Power BI. 5. Vamos al grupo Datos Externos y desplegamos las opciones comando Obtener datos, para allí seleccionar Excel. 6. Navegamos en nuestro PC y seleccionamos el cargamos el archivo CAP6 EJ1 - Ing SemestreI.xlsx. 7. En el cuadro de dialogo Navegar seleccionamos el elemento Ingresos Semestre I 2015 y clic en el botón Editar.

www.excelfreeblog.com

299

300

Capítulo 6 • Materialización Alterna de Tablas con Modos de Dinamización Antes de ir a la ventana de Power Query queremos destacar desde el formato de Excel la limpieza inicial que se debe ejecutar.

Etiquetas no congruentes: • Año, • Semestre, • Encabezado Global,

filas en blanco …

Filas en blanco

Columna en blanco

Totales

Columna en blanco

Al no tener toda la fila completamente vacía no es una fila en blanco, en realidad se debe filtrar el gran total

Fila en blanco

Figura 6. 3 – Limpieza Inicial a Ejecutar

Estructurando y resumiendo, encontramos en este formato lo siguiente:

1. Remover columnas y filas en blanco 2. Remover totales 3. Remover etiquetas no congruentes

www.excelfreeblog.com

El ADN de Power Query Ahora si observemos como ha cargado Power Query la tabla de datos:

Figura 6. 4 – Fragmento de la Tabla Ingresos Semestre 2015 en Power Query

A destacar con prontitud es que la etiqueta general: Ingresos Generados por Países Suramericanos fue promovida como encabezados de columna, dejando a su paso de las columnas 2 en adelante con la etiqueta genérica Column.

¡Toma Nota! En algunas versiones de Power Query y sobre todo en otras tecnologías: Excel, MS Flow, ect. puede que no promueva la primera fila como encabezado.

www.excelfreeblog.com

301

302

Capítulo 6 • Materialización Alterna de Tablas con Modos de Dinamización

Quitar Paso: Encabezado Promovido

Inicialmente procederemos a quitar los encabezados y ponerlos como una fila normal de datos. Si bien podemos optar por ir a la pestaña Transformar y en el grupo Tabla ubicar las opciones Usar la Primera Fila como Encabezado para desplegar y finalmente utilizar el comando Usar encabezados como primera fila.

Figura 6. 5 – Comando Usar Encabezados como Primera Fila

Lo mejor será remover el paso en el panel de Configuración de la Consulta pulsando clic encima de la «x» en el extremo izquierdo del paso con nombre Encabezados Promovidos, así lo dinamitamos.

Figura 6. 6 – Eliminar Paso Encabezados Promovidos

www.excelfreeblog.com

El ADN de Power Query Con lo anterior no solo logramos ahorrar un paso, sino que restamos uno. (Con fines económicos de líneas M esto es ideal) Primero quitemos filas en blanco, esto es algo muy sencillo que ya conocemos, por lo tanto, teniendo cualquier columna

Quitar filas en Blanco

seleccionada vamos a la pestaña Inicio grupo Reducir Filas, desplegamos las opciones de Quitar Filas para finalmente pulsar clic encima del comando Quitar Filas en Blanco.

Figura 6. 7 – Quitar Filas en Blanco

Procedamos a remover ahora etiquetas no congruentes y las etiquetas totales para columnas, todo ello también en un

Aplicar Filtros

mismo paso, tal tarea la podemos conseguir con el filtro de la columna numero 1 deseleccionado los elementos:

▪

(nulo)

▪

Ingresos Generados …

▪

Año 2015

▪

Semestre 2015

La imagen siguiente lo muestra:

www.excelfreeblog.com

303

304

Capítulo 6 • Materialización Alterna de Tablas con Modos de Dinamización

Figura 6. 8 – Elementos de Filtros a Desactivar

— ¿Qué hace falta? Remover las columnas en blanco, concretamente las columnas 2 y 10. Para ello seleccionamos la columna 2 y a continuación con la tecla «Ctrl» sostenida seleccionamos la columna 10, luego de ello pulsamos clic derecho, en el menú que emerge ubicamos el comando Quitar pulsamos clic. La tabla hasta este punto debe lucir.

www.excelfreeblog.com

El ADN de Power Query

305

Figura 6. 9 – Tabla de Datos Después de Limpieza Inicial

Es menester hacer énfasis en que:

Promover Encabezado

Un punto crítico consiste en utilizar la primera fila como encabezados, después de la limpieza inicial. En ocasiones se puede pasar por alto algo tan importante y posteriormente dar con un resultado extraño, por lo previo debemos ir a la pestaña Transformar grupo Tabla y pulsar en el comando Usar la Primera Fila como Encabezado.

Figura 6. 10 – Tabla de Datos con Fila Promovida

www.excelfreeblog.com

306

Capítulo 6 • Materialización Alterna de Tablas con Modos de Dinamización

Pausemos un Momento Tratar de utilizar operaciones como anulación de dinamización y columna dinámica sin haber realizado la limpieza inicial, que en realidad consisten en técnicas de primer grado y por ello el orden del presente libro, es una lucha propia de necios, peor aún si se deja total y subtotales para la parte de análisis.

Unpivot: Un Nivel de Encabezado de Columna ¡Ha llegado la hora de la verdad! Las tres vertientes de la operación de anulación de dinamización se pueden encontrar en la pestaña Transformar grupo Cualquier Columna y opciones Anula dinamización de Columnas.

Figura 6. 11 – Variaciones de la Operación Anulación de Dinamización

Los comandos también pueden encontrarse fácilmente gracias a la sutil magia del clic derecho. — ¿Cuál de las tres utilizar?

www.excelfreeblog.com

El ADN de Power Query

Depende del caso … En nuestra situación descartamos la vertiente Anulación de Dinamización de Columnas Seleccionadas Únicamente por que a medida que aparezcan más meses: Julio, agosto, septiembre, octubre, etc., etc. Queremos que se vaya anulando la dinamización de dichas columnas también. Entre los comandos Anulación de Dinamización de Columnas y Anulación de Dinamización de Otras Columnas que al fin de cuentas es lo mismo, pero en nuestro escenario lo más económico sería el comando Anulación de Dinamización de Otras Columnas debido a que solo debemos seleccionar la columna País en contraste con Anulación de Dinamización de Columnas donde se deben seleccionar seis de ellas, esta diferencia sutil pero de micro productividad nos hace decantarnos por Anulación de Dinamización de Otras Columnas. — Ejecutemos la operación: Seleccionamos la columna País, luego pulsamos clic derecho y ejecutamos el comando Anulación Dinamización de Otras Columnas. Y como por arte de mágica llegamos al formato tabular, sin mayor trabajo adicional que renombrar los campos con nombre Atributo y Valor.

www.excelfreeblog.com

307

308

Capítulo 6 • Materialización Alterna de Tablas con Modos de Dinamización

Figura 6. 12 – Fragmento de Formato Después de A.D.O.C

Inicialmente procederemos a quitar los encabezados y ponerlos como una fila normal de datos.

Unpivot: Dos Niveles de Encabezados Columna Aunque el caso anterior es extremadamente sorprendente ya que en días pretéritos lograr darle el formato indicado a este tipo de tablas requería de un trabajo riguroso, no obstante, las posibilidades que se derivan de la operación de anulación de dinamización van muchísimo más allá, debido a que con el apoyo de otras operaciones y una buena lógica podremos resolver escenarios más complejos y formatos enrevesados, no en su lector, sino en su cantidad de variables para llegar al formato tabular.

www.excelfreeblog.com

El ADN de Power Query

309

El subsiguiente caso de estudio nace de manera natural, hablamos de: formato con dos niveles de encabezados en columnas.

Dos Niveles de Encabezado

Un nivel de encabezado de columna, como ya hemos mencionado antes, agrupa varios campos bajo una categoría. Un nivel de encabezado de columna, como ya hemos mencionado antes, agrupa varios campos bajo una categoría. Esta categoría se indica en una fila que cobija una o más columnas mediante etiquetas. Generalmente los usuarios de Excel utilizan el comando Combinar y Centrar para vincularlas entre sí, otros usuarios conscientes de las implicaciones y posibles dolores de cabeza (que puede devenir con dicho comando) prefieren la opción de Centrar en la selección. Lo anterior nos da entender que al tener otro nivel existirá una categoría que agrupa otros campos. A todas luces, una imagen dejara claro como agua de que va lo de niveles de encabezados de columnas, así que vamos al archivo:

¡Let’s Play! ARCHIVO DE TRABAJO: En el compendio de archivos y carpeta

Capítulo 6 puedes encontrar el archivo de Excel con nombre: CAP6, EJ2.xlsx.

www.excelfreeblog.com

310

Capítulo 6 • Materialización Alterna de Tablas con Modos de Dinamización

El segundo nivel de encabezado vemos que agrupa varias columnas, es decir, enero cobija las columnas Ingresos, Presupuesto y Variación. El primer nivel de encabezado será siempre la etiqueta natural de la columna o nombre del campo, este caso únicamente agrupa una única columna, por ejemplo: Ingresos.

Figura 6. 13 – Formato con Dos Niveles de Encabezados

— ¿Cómo llevar la tabla anterior al formato tabular?

Lista de Columnas

Para estar todos 100% en sintonía, listamos las columnas que debe tener al final nuestra tabla con el formato tabular:

▪

País

▪

Medida (Los elementos indican si es ingreso, presupuesto o variación)

www.excelfreeblog.com

▪

Mes

▪

Valor

El ADN de Power Query

Si nuestra tabla no tuviera la etiqueta de los meses, es decir, el nivel de encabezado de mes la solución sería muy sencilla, pues como ya hemos estudiado antes bastaría con anular dinamización, a pesar de ello, lo peliagudo del escenario es que debemos pasar tanto mes a elementos de columnas como las medidas, que corresponde a ingresos, presupuesto y variación. — ¡He aquí el truco!: Combinar las dos primeras filas de la tabla de la siguiente forma:

Figura 6. 14 – Objetivo con los dos niveles de encabezados de columnas

Manos a la obra: 1. Abrimos un archivo nuevo de Power BI. 2. Vamos al grupo Datos Externos y desplegamos las opciones comando Obtener datos, para allí seleccionar Excel. 3. Navegamos en nuestro PC y seleccionamos el archivo CAP6, EJ2.xlsx y lo cargamos. 4. En el cuadro de diálogo Navegar seleccionamos el elemento IPV y clic en el botón Editar www.excelfreeblog.com

311

312

Capítulo 6 • Materialización Alterna de Tablas con Modos de Dinamización

Así se verá nuestra tabla en la interfaz de Power Query en Power BI:

Figura 6. 15 – Fragmento de la Tabla con Dos Niveles de Encabezados de Columnas

Primero, lo primero: la limpieza incial: (1) Limpieza Inicial

1. Seleccionamos la primera columna, vamos a la pestaña Inicio, mostramos las opciones de Quitar Filas para luego pulsar clic en Quitar filas en blanco. 2. Nos desplazamos al extremos derecho de la tabla en la interfaz de Power Query para observar que las columnas 8 y 9 estan en blanco, las selccionamos con la tecla Ctrl sostenida, clic derecho para luego pulsar en el comando Quitar Columnas. 3. En la Columna 1 (Column1) desplegamos los filtros y deshabilitamos TOTAL.

www.excelfreeblog.com

El ADN de Power Query

Podremos notar que Power Query nos situó el encabezado de Mes como encabezados de campos, sin embargo, necesitamos que sea una fila normal, para ello vamos a el panel de Configuración de la Consulta y seleccionamos el paso Encabezados Promovidos, posteriormente pulsamos clic en la x en su extremo izquierdo para remover el paso.

313

NOTA Es válido recordar que dependiendo de la tecnología o la versión de Power Query puede que automáticamente no añada el paso: Encabezados

Promovidos automáticamente,

puede ocurrir especialmente en Excel y Microsoft Flow.

Figura 6. 16 – Remover Paso: Encabezados Promovidos

Al hacerlo Power Query arrojara en la interfaz un error inevitable:

Figura 6. 17 – Error al Eliminar Paso Encabezados Promovidos

www.excelfreeblog.com

314

Capítulo 6 • Materialización Alterna de Tablas con Modos de Dinamización La solución al problema anterior consiste en eliminar todos los pasos Tipo Cambiado, en nuestro caso particular solo tenemos uno.

Figura 6. 18 – Eliminar Paso Tipo Cambiado

¡Riesgo: Advertencia! Cada vez que eliminemos un paso de Encabezados Promovidos y existan pasos ulteriormente, con toda seguridad la interfaz nos arrojara un error. Esto ocurre porque al pasar a lo nombres genéricos Column1, Column2, Column3, etc. Los siguientes pasos, y en concreto el paso siguiente de Tipo Cambiado no encuentra los nombres de Columnas Enero ni Febrero. Para zanjar el inconveniente basta con eliminar todos los pasos tipo cambiado, sin importar si es el inmediatamente siguiente o alguno más adelante. (Recordemos, que una buena práctica que recomendamos

consiste en asignar los formatos de cada columna al final - siempre que sea posible -)

www.excelfreeblog.com

El ADN de Power Query

315

A este punto nuestra tabla debe estar así:

Figura 6. 19 – Tabla con dos niveles de encabezados después de limpieza inicial

El siguiente paso, y el principal para este escenario, reside en tratar de combinar las dos primeras filas, empero: En Power Query no se pueden combinar filas, solamente columnas. Para abordar este desafío debemos pensar un poco por fuera de la caja, justamente transponer la tabla para combinar las

(2) Transponer la Tabla

dos primeras columnas, esto nos da indicar que, debemos ir la pestaña Transformar y en el grupo Tabla para ubicar el comando Transponer y pulsar clic encima de este.

Figura 6. 20 – Fragmento de la Tabla Transpuesta

www.excelfreeblog.com

316

Capítulo 6 • Materialización Alterna de Tablas con Modos de Dinamización Antes de combinar las dos primeras columnas, es menester rellenar abajo, ya que si no lo hacemos al combinar los elementos Presupuesto y Variación quedarían sin el mes correspondiente Para ello seleccionamos la primera columna 1, para ir a la

(3) Rellenar Abajo

pestaña Transformar y en el grupo Cualquier Columna desplegar las opciones de Rellenar, allí oprimimos Abajo.

Figura 6. 21 – Fragmento de la Tabla luego de Rellenar Abajo

(4) Combinar Columnas

Por fin, combinaremos nuestras dos columnas, esto es simple: seleccionamos las dos columnas (Columns1 y Columns2), luego clic derecho y pulsamos clic encima del comando Combinar Columnas. Con lo anterior aparecerá el cuadro de diálogo: Combinar Columnas.

www.excelfreeblog.com

El ADN de Power Query

317

Figura 6. 22 – Cuadro de Diálogo Combinar Columnas

Lo primero es seleccionar un separador que después nos permita dividir sin ninguna dificultad las columnas en sus partes principales, puesto que, si los elementos de las columnas tienen, por ejemplo: coma (,) y seleccionamos precisamente esta como separador, más temprano que tarde tendremos que dividir nuevamente y se tornara pesadamente complicado; esto quiere decir que lo mejor será seleccionar un separador poco común. La siguiente tabla listas caracteres de uso poco probable:

NOTA Es bueno escoger un carácter que sea fácil de sacar con nuestro teclado, muchas veces, las combinaciones Alt en los computadores portátiles no funcionan correctamente por la ausencia del teclado numérico en la parte derecha, por ello, la pleca es una opción universal. Combinaciones:

Tabla 6. 1

Caracteres Poco Comunes ideales como separadores

También es perfectamente válido utilizar cualquier combinación de caracteres como separador, por ejemplo: Excel, Power Query, asdjasd, todas son opciones buenas.

www.excelfreeblog.com

318

Capítulo 6 • Materialización Alterna de Tablas con Modos de Dinamización Entonces, procedemos a mostrar la lista desplegable y escoger la opción Personalizado, con lo previo se habilita una caja de texto para digitar nuestro carácter especial, en nuestro caso, utilicemos la barra vertical: | La última parte nos ofrece la posibilidad de señalar el nombre de la nueva columna, por defecto es Combinada, dejemos intacto el nombre y clic en aceptar.

(4) Transponer

Si bien han quedado combinados, es propicio recordar que las necesitamos como filas, por lo tanto, transponemos la tabla nuevamente.

Figura 6. 23 – Fragmento de Tabla con columnas combinadas y posteriormente transpuesta

— ¿Puedes indicarnos cuál paso hace falta? Genial: Utiliza la primera fila como encabezados. La tabla de mostrarse así:

www.excelfreeblog.com

El ADN de Power Query

Figura 6. 24 – Fragmento de Tabla, Transpuesta y Fila Promovida

Nuestro amigo(a) lector(a) ya podrá inferir que el comando a utilizar de las tres opciones de Anulación de Dinamización debe ser; A.D.O.C, por lo tanto, seleccionamos la columna número 1, clic derecho y comando Anulación de Dinamización de Otras Columnas.

Figura 6. 25 – Fragmento de la Tabla después de anular dinamización

www.excelfreeblog.com

319

320

Capítulo 6 • Materialización Alterna de Tablas con Modos de Dinamización Nos restan unos finos detalles, puntualmente dividir la columna Atributo por el separador (|) y luego renombrar las columnas por nombres más descriptivos, así como asignar los formatos si es necesario. 1. Seleccionamos la columna Atributo, clic derecho y vamos a las opciones de Dividir y allí nos decantamos por Dividir Columna por Delimitador y aceptamos 2. Renombramos las columnas como: País, Mes, Medida y Valor ¡Y voilá!

Figura 6. 26 - Tabla con dos Niveles de Encabezado en Formato Tabular

www.excelfreeblog.com

El ADN de Power Query

Un Pivot: Dos Niveles de Rótulo de Fila Visiblemente más niveles de encabezados de columnas son perfectamente lícitos, en el caso previo teníamos dos niveles (los nombres de los campos los contamos como el primer nivel de encabezado), sin embargo, otra posibilidad es tener una etiqueta o rótulo que agrupe varias filas. La imagen a continuación muestra un nivel de rótulo de fila:

El rótulo Alta Población agrupa las tres primeras filas mientras que el rótulo de Baja Población agrupa de la 4 fila en adelante.

Figura 6. 27 – Tabla con un Niel de Rótulo de Fila

www.excelfreeblog.com

321

322

Capítulo 6 • Materialización Alterna de Tablas con Modos de Dinamización

A las filas naturales de la base de datos las contamos como el primer rótulo de fila, en dicho caso será evidente que cada rótulo ocupa una sola fila.

¡Let’s Play! ARCHIVO DE TRABAJO: En el compendio de archivos y carpeta

Capítulo 6 puedes encontrar el archivo de Excel con nombre: CAP6, EJ3.xlsx.

1. Abrimos un archivo nuevo de Power BI. 2. Vamos al grupo Datos Externos y allí desplegamos las opciones de Obtener Datos para finalmente pulsar clic en Excel. 3. Navegamos en nuestro computador para seleccionar y cargar el archivo CAP6, EJ 3.xlsx 4. Eliminamos los pasos: Promover encabezado y Tipo cambiado. 5. Quitamos filas en blanco y columnas en blanco (de forma idéntica en cómo se hizo en la sección previa) 6. Desplegamos los filtros de la columna 2 (Column2) y deshabilitamos el elemento TOTAL. Hasta este punto, en esencia es lo mismo, pero la diferencia marcada radica en el paso siguiente:

www.excelfreeblog.com

El ADN de Power Query 7.

Seleccionamos la primera columna y rellenamos abajo.

Figura 6. 28 – rellenar Abajo Primera Columna que corresponde al primer nivel de Rótulo

8. Transponer la tabla. 9. Seleccionar la primera columna y rellenar abajo 10. Seleccionamos las columnas 1 y 2 y las combinamos, en esta oportunidad podemos utilizar el carácter: «¶» para variar 11. Transponemos la tabla nuevamente y utilizamos la primera fila como encabezados 12. Seleccionamos columnas 1 y 2 y aplicamos A.D.O.C

www.excelfreeblog.com

323

324

Capítulo 6 • Materialización Alterna de Tablas con Modos de Dinamización

Figura 6. 29 – Fragmento de la Tabla Después de A.D.O.C con un nivel de rótulo de fila

13. Dividir Columna Atributo por delimitador: ¶ 14. Renombrar Columnas, así: Densidad de Población, País, Mes, Medida y Valor.

Figura 6. 30 – Fragmento de la Tabla con nivel de rótulo después de separar columnas y renombrar

www.excelfreeblog.com

El ADN de Power Query

¡Toma Nota! ¡Nomenclatura NxM! La nomenclatura NxM informa que podemos encontrar tablas con formatos donde existen N rótulos de filas y M niveles de encabezados de columnas. Como se ha podido apreciar en los casos anteriores (1x1, 1x2 y 2x2) se entrevén patrones de transformación, el objetivo es llegar al “algoritmo” para cualquier caso, sin embargo, la generalidad de esta situación será tratada en el próximo capítulo (capítulo 7) y la automatización total en el capítulo 19.

Columna Dinámica (Pivot) Es hora de fijar el foco del capítulo en el segundo hijo de la transposición: La columna dinámica. Así como lo son los integrales a las derivadas o la resta a la suma, la operación de columna dinámica es la inversa de la anulación de dinamización. Una ilustración para mayor claridad: Formato Tabular (Formato) Tabla Dinámica

Figura 6. 31 - Esquema del Input y Output de la Operación de Columna Dinámica

www.excelfreeblog.com

325

326

Capítulo 6 • Materialización Alterna de Tablas con Modos de Dinamización Como ya es costumbre a estas alturas daremos paso al estudio de la:

Mecánica de Columna Dinámica (Versión Simple) La operación de columna dinámica realiza lo siguiente: Toma cada valor único de los elementos de la columna seleccionada y los convierte en encabezados de columnas; para las demás columnas excluyendo una especifica llamada Funcionamiento: Lógico de Columna Dinámica

Columna de Valor, deja la lista de elementos únicos o la combinación de elementos únicos si son varias columnas. Para la columna especial denominada Columna de Valor, realiza el agregado que indiquemos, es decir: Suma, Promedio, recuento, etc. - Dependiendo del caso -. Este proceso de columna dinámica o dinamización de columnas (Pivot) se puede ver cómo crear una «tabla dinámica» (su formato) en Power Query, donde en el área de columnas sólo puede ir un campo (que sería la columna dinámica) y varios en el área de filas.

¡Toma Nota! Una diferencia notable con la analogía de tablas dinámicas respecto al resultado de la operación de Columna Dinámica es que esta última no incluye ni totales ni subtotales.

www.excelfreeblog.com

El ADN de Power Query

Versión: Simplificada

Inicio

¡Información! La tabla inicial debe ser idealmente una tabla con formato tabular óptimo, es decir, aplicar la limpieza de datos inicial.

Proceso / Mecánica

Final Recordemos que este proceso de columna dinámica o dinamización de columnas (Pivot Column) se puede ver cómo crear una “tabla dinámica” (su formato) en Power Query, donde en el área de columnas solo puede ir un campo (que sería la columna dinámica).

Además, este formato no incluye ni totales ni subtotales.

www.excelfreeblog.com

327

328

Capítulo 6 • Materialización Alterna de Tablas con Modos de Dinamización

Recomendaciones y Buenas Prácticas El requisito para antes orquestar al tempo de la operación de columna dinámica es la limpieza, entonces, se infiere que: Lo primero a realizar es remover:

Regla General

▪

Totales generales y subtotales (Si aplica)

▪

Filas y columnas en blanco (Si aplica)

▪

Filas y columnas de error (Si aplica)

▪

Filas y columnas Duplicadas (Si aplica)

▪

Etiquetas no congruentes en el contexto de los datos (Si aplica)

Estamos viviendo un Déjà vu. Ni de cerca, puesto que a estas alturas ya debe ser 100% claro que lo primero siempre a ejecutar es la limpieza de datos inicial. Veamos, ahora sí, la operación de columna dinámica directamente en la interfaz de Power Query:

¡Let’s Play! ARCHIVO DE TRABAJO: En el compendio de archivos y carpeta

Capítulo 6 puedes encontrar el archivo de Excel con nombre: CAP6EJ4 – ColumnaDinámica.xlsx.

www.excelfreeblog.com

El ADN de Power Query 7. Abrir un archivo nuevo de Power BI 8. Inicio → Datos Externos → Obtener Datos → Excel 9. Navegamos en nuestro computador para ubicar, seleccionar y cargar el archivo: CAP6EJ4 – Columna Dinámica.xlsx 10. En el cuadro de diálogo Navegador seleccionamos el elemento Ejemplo11 y clic en el botón Editar.

Nuestra tabla tiene un aspecto inofensivo, de hecho, se concluye de primer vistazo que no es necesario la limpieza de datos inicial.

Figura 6. 32 – Tabla para Dinamizar Columna

Pivot (Columna Dinámica): Versión Simplificada Si queremos aplicar la operación de Columna Dinámica a la tabla previa, lo primero que demos preguntarnos es: de cuál de los campos apetecemos extraer los elementos únicos para que pasen a ser encabezados de columnas, visto de otra forma, cuál sería el campo que llevaríamos al área de columnas si el objetivo fuera crear una tabla dinámica.

www.excelfreeblog.com

329

330

Capítulo 6 • Materialización Alterna de Tablas con Modos de Dinamización La respuesta varía dependiendo en cómo se desee mostrar la información, de donde se infiere que, la columna Año o Mes son respuestas legales; para este ejemplo, vamos a darle los honores a la columna Mes, por todo esto, esta será la columna dinámica la cual debemos seleccionar en la interfaz de Power Query.

Figura 6. 33 – Tabla para Dinamizar Columna, con la Columna a Dinamizar Seleccionada

Ahora ponemos rumbo a la pestaña Transformar, grupo Cualquier Columna y pulsamos clic encima del comando Columna Dinámica.

Figura 6. 34 – Cuadro de Diálogo Columna Dinámica

www.excelfreeblog.com

El ADN de Power Query Nótese en la imagen preliminar que el cuadro de diálogo

331

NOTA

enseña la columna dinámica mediante el texto: Use los nombres La temática de tablas dinámicas la hemos tratado ampliamente en nuestro canal de YouTube, por lo que si necesitas un repaso he aquí el material.

de la columna “Mes” para crear columnas nuevas. — ¡Momento de decidir la columna de valores! Esta sería la «columna que arrastraríamos al área de valores en una tabla dinámica», a menudo una columna numérica, para nosotros coincide con la columna: Tx. Luego, mostramos las Opciones avanzadas, allí seleccionamos el tipo de agregado que queremos que se aplique en la

Otros recursos para alcanzar la maestría en una de las funcionalidades más importantes en el catálogo de un profesional son:

columna valor.

▪ El ADN de las Tablas Dinámicas (2019) ▪ El Arte Marcial de las Tablas Dinámicas (2018) ▪ Tablas Dinámicas La Quinta Dimensión (2015)

Seleccionamos la columna Tx.

Tipo de Agregado

Figura 6. 35 – Parametrización Completa para Columna Dinámica

¡Riesgo: Advertencia! Para el ejemplo previo, independientemente del tipo de agregado aplicado, el resultado de la operación proporcionará el mismo resultado, puesto que, la intersección fila-columna siempre tendrá un

único valor.

www.excelfreeblog.com

332

Capítulo 6 • Materialización Alterna de Tablas con Modos de Dinamización

Finalmente, oprimimos el botón Editar.

Figura 6. 36 – Tabla con Columna Dinámica Aplicada

Veamos el símil con Excel:

Figura 6. 37 – Símil Dinamización de Columna con Tabla Dinámica

¡Toma Nota! Podemos concluir que todos los campos restantes, es decir, exceptuando el campo de columna dinámica y el de valor, van directamente al área de filas de la tabla dinámica, situación que se cumple a cabalidad con la operación de columna dinámica.

www.excelfreeblog.com

El ADN de Power Query

333

Trazar una línea es adecuado, a causa de que la operación de columna dinámica hasta aquí es la versión simplificada, entre otras cosas porque sola hay tres campos. Esto nos lleva a profundizar, y si bien, la mecánica de la operación de columna dinámica debatida páginas atrás es buena, el funcionamiento lógico de la versión expandida explícitamente dará paso a claridad absoluta.

Mecánica de Columna Dinámica (V. Expandida) 1. Extrae la lista de elementos únicos y cada elemento lo convierte en encabezado de columna. 2. Para la(s) columna(s) no seleccionadas y excluyendo la columna de valor y la columna dinámica, deja la lista de

Funcionamiento: Lógico Versión Expandida

elementos únicos para esa columna o la combinación de elementos únicos de las columnas. 3. Para la Columna de valor: realiza el agregado señalado. a. Si para cada intersección fila(s)-columna(s) en lo que sería la tabla resultado existe un único elemento en la tabla inicial, entonces, se puede indicar: No agregado. b. Si al buscar en la tabla inicial según los parámetros de intersección de lo que sería la tabla resultado no encuentra ninguna fila que cumpla, entonces retorna null. www.excelfreeblog.com

334

Capítulo 6 • Materialización Alterna de Tablas con Modos de Dinamización

Versión: Expandida

Inicio

Proceso / Mecánica • C.D: Columna Dinámica • C.V: Columna de Valor

¡Atención! Los pasos se pueden leer con claridad en la página anterior.

Final Repasemos que este proceso de columna dinámica se asemeja a crear una “tabla dinámica” (su formato) en Power Query, donde en el área de columnas solo puede ir un campo (que sería la columna dinámica). Además, este formato no

incluye ni totales ni subtotales.

www.excelfreeblog.com

El ADN de Power Query

Pivot (Columna Dinámica): Versión Extendida Saltemos inmediatamente a la acción, para ello tengamos presente que el archivo a utilizar es el siguiente:

¡Let’s Play! ARCHIVO DE TRABAJO: En el compendio de archivos y carpeta

Capítulo 6 puedes encontrar el archivo de Excel con nombre: CAP6EJ4 – Columna Dinámica.xlsx.

1. En esta oportunidad utilicemos el mismo archivo de Power BI que ya hemos creado del ejercicio anterior. 2. En la interfaz de Power Query posicionamos el cursor de nuestro ratón en el extremo izquierdo encima del Panel de Consultas en un área “gris” y pulsamos clic derecho, luego se muestra un pequeño menú en donde vamos a seleccionar: Nueva consulta, para finalmente dar clic en Excel.

Figura 6. 38 – Nueva Consulta de Panel de Consultas

www.excelfreeblog.com

335

336

Capítulo 6 • Materialización Alterna de Tablas con Modos de Dinamización

3. Buscamos en nuestro computador el archivo CAP6EJ4 – Columna Dinámica.xlsx para seleccionarlo y cargarlo 4. Inmediatamente emerge el cuadro de diálogo

NOTA

Navegador, allí apreciaremos en su extremo izquierdo 4

Una característica clara de la consulta Ejemplo 22 que estamos trabajando se cimenta en que no hace falta la limpieza de datos inicial, porque no tiene filas ni columnas en blanco.

elementos, seleccionamos el elemento con nombre Ejemplo 22 y clic en el botón Aceptar.

Por el motivo descrito del paso 4 al 5 se va directamente a ejecutar el comando Columna Dinámica (Pivot) Figura 6. 39 – Elemento Ejemplo 22

5. Saltamos directamente a seleccionar la columna Mes para ir a la pestaña Transformar y pulsar clic en el comando Columna Dinámica que se ubica en el grupo Cualquier Columna. 6. Para el cuadro de diálogo Columna Dinámica no está de más cerciorarnos que el texto diga: Use los nombres de columnas “Mes” para crear columnas nuevas, en la siguiente instancia seleccionamos la columna «Tx» en la lista desplegable Columna de Valores. Mostramos las Opciones Avanzadas y en la lista desplegable correspondiente Función de Valor Agregado vamos a inclinarnos por No Agregar. www.excelfreeblog.com

El ADN de Power Query

Figura 6. 40 – Configuración del Cuadro de Diálogo Columna Dinámica

7. Como paso concluyente solo nos resta presionar encima del botón Aceptar. A lo mejor el resultado incite estupor, a consecuencia de los errores que se visualizan por doquier en las columnas Enero y Febrero.

Figura 6. 41 – Errores en Columna de Valores en la Operación Pivot

— ¿Puedes explicar por qué? Si es así excelente, en todo caso, acompáñanos en las siguientes páginas para esclarecer el porqué de los errores.

www.excelfreeblog.com

337

338

Capítulo 6 • Materialización Alterna de Tablas con Modos de Dinamización

Sigamos los tres pasos del esquema:

A: Columna Dinámica (C.D) B: Columna de Valor (C.V)

C.D: Extrae la lista de

elementos únicos y cada uno lo convierte en encabezado de columna.

Excluyendo C.D y C.V, deja la combinación de

elementos únicos de las columnas restantes

Figura 6. 42 – Esquema de Columna Dinámica con Ejemplo

www.excelfreeblog.com

El ADN de Power Query

339

Arribamos al quid de la cuestión, el paso número 3: Columna de valor: Realiza el agregado señalado. Si para cada intersección fila-columna en lo que sería la tabla resultado existe un único elemento en la tabla inicial, entonces, se puede indicar: No agregado. Si al buscar en la tabla inicial según los parámetros de intersección de lo que sería la tabla resultado no encuentra ninguna fila que cumpla, entonces retorna null En suma, nos hace falta analizar las partes de la tabla donde están los errores, de resto sabemos con exactitud como han surgido:

Elementos de Fila

Figura 6. 43 – Analizando Columnas de Error

En particular el análisis se hace casilla a casilla (de ahí la aparte

Sub Paso 1

de: para cada intersección fila-columna), por lo que vamos a tomar la encerrada en la figura anterior en (1). Luego empareja los elementos “sobrantes” de dicha fila, es decir, los que no provienen de la columna dinámica. www.excelfreeblog.com

340

Capítulo 6 • Materialización Alterna de Tablas con Modos de Dinamización

Y “filtra” la tabla de inicio con dichos elementos “sobrantes” en

Sub Paso 2

las columnas correspondientes Elaboremos un esquema de “zoom” de esta parte final por mor de una explicación más exquisita.

Empareja los elementos “sobrantes” de la fila y los utiliza como filtros en las columnas correspondientes.

El problema es que no puede mostrar una columna de valores en una sola casilla.

Ten presente que la columna de valor es «Tx» y el tipo de agregado que seleccionamos fue: «No Agregar».

Toma los elementos del campo de valor, que para la situación específica es «Tx» y lo retorna a la posición equivalente, que en este caso se corresponde con la casilla de análisis Figura 6. 44 – Zoom del Paso en la Mecánica de Columna Dinámica

www.excelfreeblog.com

El ADN de Power Query

Es claro ahora: el Error se presenta porque al indicarle No Agregado no tiene manera de presentar una columna en una sola casilla, de hecho, si pulsamos clic encima de unas de las palabras de Error…

Figura 6. 45 – Clic en el Error

La interfaz muestra un mensaje diciendo precisamente eso, con otras palabras, pero en conclusión señalando que son demasiados elementos.

Tabla 6. 2 – Error de Demasiados elementos

¡Toma Nota! Al presionar en la palabra Error se agrega un paso más en el Panel de Pasos Aplicados con el nombre de la columna, para el caso Enero, por lo tanto, si pretendemos realizar una nueva manipulación no olvidemos en eliminar este último.

— Será transparente ahora la parte que reza:

www.excelfreeblog.com

341

342

Capítulo 6 • Materialización Alterna de Tablas con Modos de Dinamización

Si para cada intersección fila-columna en lo que sería la tabla resultado existe un único elemento en la tabla inicial, entonces, se puede indicar: No agregado.

El Siguiente Paso Los lindes de las operaciones de Columna Dinámica y Anulación de Dinamización no concluyen aquí, es más, sus bondades e infinitas posibilidades demandan, aunque sea un par de capítulos adicionales, debido a que el poder que subyacen en ellas puede continuar en explotación, es por esto por lo que entre otras cosas el siguiente capítulo seguirá tratando el tema, pero con algo más de picante.