Summary
Highlights
The session begins by reviewing the previous class, which covered Power BI Desktop and creating a basic dashboard. The main focus for this session is on the 'T' in ETL (Extract, Transform, Load), specifically delving into data transformation using the Power Query Editor. The instructor emphasizes that while many Power BI users are familiar with basic dashboard creation, a deeper understanding of Power Query and DAX (Data Analysis Expressions) is crucial for handling complex data and is a highly sought-after skill.
Power Query is introduced as the 'kitchen' where data preparation and transformation occur. It's an internal tool within Power BI that allows users to standardize and clean data. The instructor explains that all transformations in Power Query are automatically recorded in a scripting language called M (Power Query M formula language), which can be viewed in the advanced editor. This history of applied steps allows for tracking and understanding every modification made to the data.
A significant portion (around 80%) of a Power BI developer's time is spent on data preparation within the Power Query Editor. This is because raw data often requires extensive cleaning, structuring, and transformation to be suitable for analysis and visualization. The instructor shares a real-world example of how Power BI revolutionized a company's reporting process, shifting analysts' focus from manual data manipulation to actual data analysis and decision-making.
The instructor demonstrates how to access the Power Query Editor from Power BI Desktop. It's highlighted that Power Query is an integrated, free feature within Power BI. The interface is explained, including the 'Queries' panel (tables), the main data preview area, and the 'Applied Steps' panel, which meticulously records every data transformation. The default initial steps (Source, Navigation, Changed Type) are explained, detailing how Power BI automatically connects to data, navigates to tables, and applies data types.
A crucial part of data exploration within Power Query is the 'View' tab. Tools like 'Column distribution' help visualize the frequency of values in a column, 'Column profile' provides detailed statistics (distinct values, unique values, errors, empty cells, min/max for numerical data), and 'Column quality' quickly shows the percentage of valid, error, and empty values. The 'Show whitespace' option helps identify hidden leading or trailing spaces in text data.
The first practical exercise involves combining the 'Line' and 'Model' columns from the 'Product' table into a new column called 'Line + Model'. The process involves selecting both columns using the 'Ctrl' key, going to 'Transform' -> 'Merge Columns', choosing a custom delimiter (comma and space), and naming the new column. The instructor also demonstrates how to duplicate original columns before merging to retain the separate 'Line' and 'Model' columns. Conversely, the 'Size Range' column is split into two based on a delimiter (space) to separate size indicators from measurements.
This section introduces parameters as a powerful tool to manage data source paths, especially when sharing Power BI files. The instructor explains that a parameter represents the folder path where data files are stored, separate from the actual file name. This allows other users to easily update the data source by simply changing the parameter value to their local file path, without manually remapping each table. The creation of a new parameter ('centro skill') and its application to the data source settings (using 'Advanced Editor' to combine the parameter with the file name) is demonstrated with multiple examples of updating the parameter route.
The session explains relational models, emphasizing how tables relate to each other through common fields, acting as 'dictionaries' for specific codes. Key concepts are primary keys and foreign keys. A primary key uniquely identifies each record in a table (e.g., a customer ID in a customer table), being an irreplaceable and non-repeating identifier. A foreign key, found in another table (e.g., customer ID in a sales table), refers to the primary key of the 'dictionary' table, establishing a link and allowing for repetition (a customer can make multiple purchases). Examples with 'users' and 'orders' tables, and 'students' and 'marks' tables, illustrate these concepts clearly.
Building on relational models, the instructor introduces dimension tables and fact tables. Dimension tables provide descriptive attributes and context (e.g., customer details, product specifications). These tables help 'dimension' or describe entities. Fact tables, on the other hand, contain measurable, quantifiable data points that represent events or 'facts' over time (e.g., sales transactions, student scores). Fact tables typically contain foreign keys linking them to dimension tables to provide context for the measured facts.
This practical exercise demonstrates how to combine queries to enrich tables. The goal is to add the subcategory name to the 'Products' table, which currently only has a subcategory ID. By selecting the 'Products' table and using 'Combine Queries as New' (to avoid overwriting the original), the 'Products' table is joined with the 'Subcategories' table. The common fields 'Subcategory Code' (foreign key in Products) and 'Subcategory ID' (primary key in Subcategories) are used for the join. The result is a new combined table that includes all product information plus the corresponding subcategory name, enabling more detailed analysis and visualization.
The final part of the session covers importing multiple data files from a folder. The instructor demonstrates connecting to a folder containing several '.txt' files, which are comma-delimited. Power BI automatically detects the format (comma delimiter) and structure of these files. By selecting 'Combine & Transform Data', Power BI intelligently merges all files from the folder into a single table. It even identifies the original filename for each record, providing flexibility for filtering specific data sources. This streamlines the process of consolidating data from numerous similar files.