Tratamiento de datos con Power Query
Autor/a : Ana María Bisbé York
'Tratamiento de datos con Power Query' comienza introduciendo el entorno de trabajo en Power Query y su capacidad para conectar a diferentes orígenes externos.
Índice
Prólogo de Miguel Llopis Cómo usar este libro 1. Introducción a la transformación y limpieza de datos Los datos en el mundo de hoy Desafíos que abordar Conocer los datos y el objeto de estudio Comprobar de la calidad del dato Afrontar las diferentes formas de presentación original de los datos Comprender la distribución de la muestra Valorar la necesidad de segmentación Dar tratamiento de valores atípicos Identificar escenarios de cuantificación Optimizar los tiempos de carga y actualización de datos Preparar datos para el análisis Conclusiones 2. Introducción a Power Query ¿Qué es Power Query? ¿Para qué sirve Power Query? Tipo de tareas que se realizan en Power Query ¿Dónde se encuentra Power Query? Microsoft Excel Power BI Desktop Power BI DataFlows Otras herramientas y entornos ¿Qué elementos componen Power Query? Estructura Interfaz de usuario Tipos de datos Tipos de objetos Tipos de consultas El lenguaje M como motor Conclusiones 3. Introducción al modelo tabular ¿Qué es el modelo tabular? Modelo tabular en Power Pivot de Microsoft Excel Modelo tabular en Power BI Desktop Tipos de tablas en el modelo y sus características Otros conceptos del modelo tabular El perfil de distribución y el modelo tabular Papel de las relaciones del modelo tabular ¿Qué tipos de esquemas existen en el modelo tabular? Tabla única Esquema copo de nieve Esquema estrella Solución a problemas y escenarios de modelado ¿Qué son las entidades y los atributos del modelo? Entidad Atributo ¿Cómo se puede examinar el modelo tabular? Herramientas externas Tabular Editor Conclusiones 4. Obtener datos externos de archivos Tareas de extracción Conectar a archivos planos Configuración regional y configuración de Windows Diferenciar entre archivos delimitados y no delimitados Conectar a libros Excel Formatos Desde Excel a Power BI y de Power a Excel Importar desde Excel a Power BI Conectar a PDF Importar desde archivo JSON Importar desde XML Importar desde carpetas Conclusiones 5. Obtener datos externos de bases de datos Conectar a base de datos SQL Server desde Power Query Plegado de consultas Modos de conexión Importar DirectQuery Modo de almacenamiento Conclusiones 6. Obtener datos externos de web Formas de acceso a datos en orígenes externos vía web Retos al consumir datos de la web Estructura del dato Autoría de contenidos Importar datos desde un origen web a Power Query Navegador para el conector web Privacidad en conexión web Consulta de conexión web Crear tablas a partir del ejemplo Ejemplo 1. Todos los datos: dos columnas País y Sitio Ejemplo 2. Todos los países y solo el primer sitio de cada uno Importar un archivo desde web Descargar y luego conectar Acceder a información web utilizando API ¿Qué es API? ¿Cómo se obtienen las claves? Ejemplo de uso Acceder a Google Sheets Obtener el conector Iniciar sesión Conectar con datos abiertos OData Vista en un navegador web Conexión con Power Query Ajustar la consulta aplicando filtros Conectar a datos con formato Parquet Conectar con contenido en Sharepoint Power Query en Excel y conectores web Conclusiones 7. Otros orígenes de datos El Servicio Power BI como origen de datos Conjunto de datos en Power BI Power BI DataFlows Power BI Datamarts Azure Azure Analysis Services Microsoft Exchange Conclusiones 8. Configurar opciones de acceso a datos Especificar datos en consultas desconectadas Reutilizar conexiones existentes Configurar propiedades de orígenes de datos Paso Origen en la consulta Barra de fórmulas Configuración de origen de datos Trabajar con parámetros de ruta y propiedades de conexión Parámetros de ruta Parámetros de filtro o validación Parámetros de entorno vs. parámetros de consulta Editar valores de parámetros desde Power BI Desktop Editar valores de parámetros desde Servicio Power BI Examinar dependencias de consultas para distinguir rutas de acceso Agrupar elementos en el editor de consultas Power Query Describir los procesos con comentarios y modificar los nombres de los pasos Puertas de enlace, el puente a los datos locales ¿Qué es una puerta de enlace? ¿Qué tipos de puertas de enlace existen? ¿Cómo se instala una puerta de enlace? Conclusiones 9. Explorar contenido de las columnas Significado de los colores en el perfil de calidad de datos Identificar y asignar tipos de datos Identificar el momento adecuado para cambiar el tipo de datos Distinguir el contenido de la muestra con el perfil de distribución Detectar detalles en los datos activando el perfil de columnas Más sobre los perfiles de datos Obtener perfil de datos con información básica Obtener perfil de datos con información adicional Explorar las acciones de filtro por filas Filtros y expresiones en lenguaje M Filtrar múltiples columnas Filtrar con Uso avanzado Comprobar eficacia de nombres de columnas y tablas Conclusiones 10. Comprobar y garantizar la calidad de los datos Importancia de velar por la calidad de datos Importancia de velar por la calidad del modelo Sobre las transformaciones Limpiar cadenas indicando mayúsculas o minúsculas Limpiar eliminando filas y columnas Eliminar filas con valores en blanco o nulos Eliminar columnas innecesarias Eliminar espacios para mejorar la calidad Null vs. null vs. vacío Reemplazar vs. columna condicional Eliminar valores nulos rellenando por columnas Quitar duplicados para garantizar la sostenibilidad del dato Cambiar tipo de datos con configuración local Conclusiones 11. Transformar y limpiar columnas de texto Extraer parte de una cadena de texto Extraer contenido entre delimitadores Extraer vs. reemplazar elementos Diferencias entre Recortar y Limpiar Función Recortar Función Limpiar Extraer números de textos Posibles errores en columnas de texto Agrupar por columna con errores Filtrar desde columna con errores Calidad de datos y perfil de distribución Evaluar condiciones sobre cadenas de texto sin limpiar Conclusiones 12. Transformar y limpiar columnas tipo Fecha Escenarios de conversión de datos tipo Fecha Problemas de conversión según la configuración regional Formatos que se convierten automáticamente Formatos que no se convierten y hay que transformar Crear tabla de fechas o calendario en Power Query Especificar datos para crear tabla de fechas Crear tabla fechas con expresiones en lenguaje M Agregar atributos desde el menú Agregar atributos desde la ayuda más allá de la interfaz Crear una consulta con las referencias del lenguaje M Crear una función personalizada a partir de la referencia integrada Utilizar una función personalizada para crear nuevos atributos a la tabla calendario Atributos de calendario y el modelo de datos Conclusiones 13. Transformaciones para cambiar la estructura de consultas Combinar columnas Desventajas de combinar columnas Insertar columna de Índice en lugar de combinar Dividir columnas Dividir columnas para estructurar mejor su contenido Dividir columnas controlando el uso de caracteres y delimitadores Dividir texto en filas en Power Query Acción adicional si el delimitador es un espacio Dividir en filas y combinar columnas Transponer filas y columnas Transponer para rellenar Agrupar filas Resultado tras agrupar filas en una consulta Agrupar y ver el detalle Agrupar más allá de la propuesta de la interfaz Agrupar y concatenar el resultado Dinamizar columnas en Power Query Dinamizar columnas y crear agregados Caso de error al dinamizar columnas Solución al error al dinamizar columnas Dinamizar columnas agregando un índice Anular dinamización de columnas o Unpivot Anular dinamización de columnas y celdas vacías Conclusiones 14. Enriquecer modelo con nuevas columnas Agregar columnas necesarias para un informe analítico Diferenciar casos para transformar o agregar columna Evaluar la composición, calidad y distribución de los valores en una columna Trabajar con columnas condicionales Comparar valores y crear una columna condicional Los nulos y la evaluación en columnas condicionales Al preguntar por valores nulos el orden importa Sustituir valores de una columna para eliminar valores innecesarios Las columnas condicionales y el orden de las evaluaciones Obtener nuevos atributos Crear nuevas columnas asignando valores numéricos Para definir el orden de visualización de las columnas Para cuantificar las cadenas de texto Aprovechar los recursos de la opción Columnas desde el ejemplo Mostrar segmentación en rangos equidistantes Combinar columnas, empleando todo o parte del contenido Cuando parece que se ha interpretado bien, pero no es así Conclusiones 15. Combinar consultas Referenciar y duplicar para obtener nuevas consultas Referenciar Duplicar Otras acciones vs. Duplicar y referenciar Dependencias de consultas Bases para combinar consultas Combinar consultas y el origen de datos relacional Elementos que componen la combinación de consultas Tipos de uniones en Power Query Desafíos en el tratamiento de textos y la combinación de consultas Combinación con coincidencia aproximada (fuzzy) para columnas de texto Combinación aproximada con tabla de equivalencias Definir el umbral de similitud La combinación de consultas y los niveles de privacidad Ver la configuración de los permisos Otros ejemplos de uso Detectar cambios en los datos comparando consultas Obtener el valor real a partir de valores acumulados Conclusiones 16. El modelo tabular y el rol de Power Query Pasar de tabla única a modelo en estrella Pasar de copo de nieve a modelo en estrella Definir entidades Definir atributos del modelo Desde tabla cruzada o columna apilada Cuando no existen los atributos Eliminar complejidad creando entidades misceláneas Crear consulta para Misceláneas desde tabla única Crear consulta para Misceláneas desde producto cartesiano entre tablas no relacionadas Crear entidad única anexando consultas Anexar datos desde orígenes desconectados o archivos planos Anexar datos desde hojas de Excel Modelar escenarios de entidades con múltiples roles Varias dimensiones diferenciadas por roles o funciones Dimensión única realizadora de múltiples funciones (RPD) Crear entidades para uso compartido Dos tablas de hechos vs. tabla única Dimensiones compartidas y cardinalidad de la relación Dimensión compartida entre dimensiones El modelado de tabla de tiempos dentro y fuera de Power Query Configurar Inteligencia de tiempo en Power BI Desktop Tablas de fechas ocultas en el modelo de datos Contenido de las tablas ocultas Eliminar las tablas de fecha ocultas en el modelo Agrupar y agregar vs. mantener detalle, pros y contras Agrupar y agregar en escenarios de cabecera y detalle Cuando se detallan fechas, horas y minutos Proteger la estructura de la tabla de hechos Solución para tabla de hechos Conclusiones 17. Solucionar escenarios de modelado de relaciones con Power Query Evitar relación 1 a 1 entre dos tablas de hechos Evitar relación 1 a 1 entre dimensiones Evitar relación M a M entre dos tablas quitando duplicados Evitar relación M a M entre dos tablas quitando nulos Evitar relación M a M entre dos tablas con una tabla puente entre hechos Evitar relación M a M entre dos tablas con una tabla puente entre hechos y dimensiones Dar solución a escenarios con relación variable entre columnas Evitar filas huérfanas en los hechos por errores en la integridad referencial Conclusiones 18. Distinguir y controlar errores en Power Query Prevención de errores Tipos de errores detectados en consultas de Power Query Error de nivel de paso Error a nivel de celda o fila El perfil de calidad de columna y los errores Errores y tipos de datos Errores de operación Evaluar una condición con el tipo de datos erróneo Evitar error de navegación al combinar archivos Control de errores Función TRY Función TRY sin argumentos adicionales Función TRY y sus cláusulas otherwise y catch Errores en la carga de datos Ignorar el mensaje de error en la carga Ver las filas con error en Power Query Eliminar los errores antes de cargar Conclusiones 19. Buenas prácticas en la creación de consultas con Power Query Buenas prácticas No ordenar por columnas No ordenar por filas Elegir cuándo definir el tipo de datos Aplicar mejoras a un grupo de columnas en lugar de ir transformando una a una Ajustar tipo de datos directamente con lenguaje M Deshabilitar la carga de una consulta Trabajar con carpetas o grupos para organizar el Navegador de consultas Asignar propiedades y comentarios a los pasos Implementar los cambios o cargar los datos al modelo tabular desde Power Query Conclusiones 20. Introducción a lenguaje M El código M y la barra de fórmulas Presentación del Editor avanzado de Power Query Las palabras reservadas let e in El código M y los comentarios Crear consultas en blanco en Power Query Trabajar con la ayuda de referencias del lenguaje M integrada en Power Query Trabajar con funciones Función if Funciones de conversión de datos Crear funciones personalizadas Uso de función personalizada para limpiar cabeceras de columnas Crear funciones propias desde la referencia del lenguaje y sin programar Distinguir las secciones en el editor de consultas Más allá de #shared y #sections Estructuras en Power Query Las listas como objetos en el lenguaje M Crear lista desde el menú Listas y funciones Lista consecutiva con rango Los registros como objetos del lenguaje M Utilizar o no la capacidad Intellisense o Autocompletar Obtener un registro navegando por una "condición" Crear un registro para cada fila de una tabla La combinación de registros y los nombres de columnas Las tablas como objetos del lenguaje M Acceso a elementos de tablas ¿Qué es la proyección? Casos de uso en escenarios puntuales Eliminar columnas en dependencia de su posición Prevenir errores con parámetros en funciones M Calcular la edad desde la interfaz o en un único paso desde el código Conclusiones 21. Optimización de consultas con Power Query Herramienta de medición para consultas Importancia de la herramienta Configurar la herramienta Iniciar el diagnóstico Explorar el resultado del diagnóstico en Power Query Realizar el diagnóstico a la consulta eficiente para comparar los resultados Combinar consultas para analizar los resultados en Power Query Explorar el resultado del diagnóstico en informes de Power BI Medición de rendimiento que depende del plegado de consulta Validar el plegado de consulta con M Evaluación diferida o perezosa Medir el rendimiento en origen con SQL Server Profiler Evitar el uso del plegado de consulta Medir el efecto del plegado de consultas Otras herramientas para explorar el modelo de datos Conclusiones 22. Los flujos de datos o Power Query en la nube Ventajas de uso de los flujos de datos ¿Cómo acceder a los flujos de datos? Conectar a datos externos desde flujos de datos Las puertas de enlace en los flujos de datos Vías para conectar a orígenes externos Entorno de Power Query en el Servicio Power BI Vista de esquema Vista de diagrama Plan de consulta Marcador visual del tipo de combinación Contador de ejecución Script de la consulta Control visual del plegado de consultas Perfiles de columna Modificar un flujo de datos Otras características y novedades de los flujos de datos Opciones del proyecto Transformaciones en Power Query Marcar columna como clave Sobre los tipos de datos Cualquiera y Binario Tablas calculadas en flujos de datos Flujos de datos Gen2 Comenzar a trabajar con flujos de datos Gen2 Abrir Power Query en Data Factory Entorno de trabajo de Power Query para flujos de datos Gen2 Resumen de conceptos y componentes Conclusiones Índice alfabético
