Summary
Highlights
The session begins with a recap of Power Query Editor, a powerful tool integrated within Power BI Desktop. The speaker reminds participants about the previous session's topics, including relational models, primary and foreign keys, and parameter configuration. The importance of practicing and exploring Power BI between sessions is highlighted, emphasizing that the current session will cover advanced topics and introduce DAX.
The concept of annexing queries is introduced as a method for consolidating two or more databases into a single query, treating them as if they belong to the same source. An example is provided using sales data from two different years, 2025 and 2026, stored in separate systems (Amazon Redshift and CRM Skill). The lecturer explains that annexing queries helps unify data while preserving original headers and handling discrepancies by adding 'null' values for missing columns. Practical demonstration of annexing three product data files (M, R, T) in TXT format into a single table is performed, highlighting the 'Append Queries' function and the option to create a new appended table.
The difference between duplicating and referencing queries is explained. Duplicating a query creates an independent copy that can be modified without affecting the original, while referencing creates a mirror image that reflects all changes made to the original query. A practical example is shown using a 'Ventas' table, demonstrating how changes in the original table are reflected in a referenced query but not in a duplicated one. This illustrates that referenced queries maintain a dynamic link to their source, making them useful for scenarios where dependent analysis is required without altering the original data source.
The session covers how to group data within a table to create aggregated insights, such as total sales by product code. Using the 'Agrupar por' (Group By) function, the speaker demonstrates how to aggregate sales data by 'Código de Producto' (Product Code) to calculate the 'Total de Ventas' (Total Sales) for each product. This process simplifies data for better visualization and analysis, allowing for quick insights into product performance.
This section delves into dynamic (Pivot) and undynamic (Unpivot) columns in Power BI, emphasizing their importance for data visualization. Dynamic columns transform row values into new columns, while undynamic columns transform columns into attribute-value pairs in rows. An example of transforming web data from Wikipedia on world population by continent and year is presented. The data cleaning process is meticulously demonstrated, including removing unnecessary columns, filtering out blank values, and handling hidden spaces/metadata using 'Recortar' (Trim), 'Limpiar' (Clean), and 'Reemplazar los valores' (Replace Values). Finally, the unpivoting of continent columns into 'Continentes' and 'Habitantes' columns is shown to prepare the data for creating a line chart visualization, illustrating population trends by continent over time.
The creation of new columns using conditional logic is demonstrated. This involves categorizing values in an existing column (e.g., 'Ingresos' or 'Income' in a customer table) into new categories based on specified conditions (e.g., 'Ingresos bajos' (Low Income), 'Ingresos promedio' (Average Income), 'Ingresos altos' (High Income)). This enhances data analysis by providing a structured classification that is easier to visualize and interpret, as shown with a pie chart displaying customer income distribution.
The importance of configuring data types according to regional settings is discussed, especially concerning decimal separators (comma vs. period). The 'Detectar tipo de datos' (Detect Data Type) feature is highlighted for automatically assigning appropriate data types. Additionally, the utility of 'Usar primera fila como encabezados' (Use First Row as Headers) is illustrated, showing how to promote the first row of data to serve as column headers when they are initially misrepresented, ensuring proper data structure for analysis.
Further data cleaning techniques, specifically 'Limpiar' (Clean) and 'Recortar' (Trim), are demonstrated using the 'Educación' (Education) column in the 'Clientes' (Customers) table. This involves standardizing text entries (e.g., 'Bachiller' (Bachelor's)) by converting them to proper case (first letter capitalized) and removing leading/trailing spaces and non-printable characters. This ensures data consistency and accuracy, facilitating correct grouping and analysis.
The 'Quitar duplicados' (Remove Duplicates) function is briefly mentioned as a way to ensure uniqueness within a column, for example, to get a list of unique customer IDs. This feature helps in maintaining data integrity and preparing data for accurate aggregation.
The session transitions to an in-depth introduction to DAX (Data Analysis Expressions), explaining it as a language for creating objects and querying tabular models. DAX functions across various Microsoft platforms like Power BI, Excel's Power Pivot, and SQL. Key characteristics include being a functional language, executing operations from inside-out, providing single coherent instructions, being case-insensitive (unlike Power Query), lacking a 'cell' concept (operations apply to whole columns/tables), using simple syntax, supporting nested functions, and handling implicit data type conversions. The importance of establishing relational models within Power BI is revisited, emphasizing primary and foreign keys, and the structure of fact and dimension tables, which is crucial for DAX calculations.
A practical demonstration of building a relational data model in Power BI Desktop is conducted. This involves importing multiple tables (Ventas, Clientes, Productos, Subcategorías, Categorías, Geografía, Fechas) and establishing relationships between them using primary and foreign keys. The challenges of displaying sales data from a 'Productos' table when the raw sales data is in a separate 'Ventas' table are highlighted, underscoring the need for relationships. The process of dragging and dropping fields to create relationships is shown, with a detailed explanation of 'Cardinalidad' (Cardinality) – particularly 'varios a uno' (many-to-one) – and its importance in defining how data flows and filters between tables. This enables insights like sales by product name, subcategory, and category.
The basic structure of a DAX formula is introduced, covering the measure's name, the equals sign, the function (e.g., SUM), and the specific table and column to be affected (e.g., 'Ventas'[Sales Amount]). The differences between 'Nueva medida' (New Measure), 'Medida rápida' (Quick Measure), and 'Nueva columna' (New Column) are clarified. Quick Measures are pre-built calculations that don't require writing DAX, New Measures use DAX but don't add a physical column to the table (only appear in visualizations), and New Columns add a calculated column directly to the table, performing row-by-row calculations. A practical example of calculating 'Costo/Venta' (Cost/Sales) percentage using each method demonstrates when to best use each option, emphasizing that measures are suitable for aggregated mathematical calculations.
The session concludes with a summary of the key differences between new measures, quick measures, and new columns regarding DAX usage, table appearance, and calculation specification. The lecturer encourages attendees to practice these concepts, emphasizing that the next session will be entirely hands-on with DAX exercises. The importance of foundational knowledge in data transformation and relational modeling for effective DAX application is reiterated. Attendees are invited to ask any final questions before the session ends.