Power BI Beginner to Pro Part 2: Data Cleansing and Shaping

In a recent training session hosted by Pragmatic Works, Angelica Choo Quan provided an in-depth walkthrough on the crucial process of data cleansing and shaping within Power BI. This session aimed to emphasize the importance of properly preparing data before moving into report development, a step that is often overlooked but critical to ensuring the accuracy and effectiveness of Power BI reports.

The Importance of Data Cleansing

Angelica began by highlighting the significance of data cleansing in the report development lifecycle. She shared a real-world example where a single incorrect data point derailed a presentation, underscoring how critical it is to ensure that data is accurate before creating visualizations. While Power BI is often recognized as a powerful data visualization tool, its data preparation capabilities are equally vital.

Using Power Query Editor for Data Shaping

The session then shifted focus to the Power Query Editor, a tool embedded within Power BI Desktop, essential for data cleansing and shaping. This tool also exists in Excel, making it versatile across multiple Microsoft products. Power Query Editor allows users to connect to various data sources and perform transformations, ensuring the data is in optimal format for reporting.

Step-by-Step Data Preparation Process

1. Connecting to Data Sources: Angelica demonstrated how to connect to a data source using Power BI’s “Get Data” feature, specifically using a web connector to bring in data from GitHub.
2. Loading vs. Transforming Data: She explained the difference between loading data directly into Power BI and transforming data using the Power Query Editor. Transforming data is crucial when the data requires cleansing or modifications.
3. Understanding Power Query Editor Interface: The interface includes key elements such as the Queries pane, data preview, and query settings. Angelica emphasized the importance of the Applied Steps pane, where users can manage transformation steps, including undoing or modifying them if needed.
4. Replacing Blank Values: Angelica showed how to replace blank values with nulls, a necessary step for using the “Fill Down” transformation to propagate values correctly across columns, ensuring consistency in the data.
5. Filling Down Values: Using the “Fill Down” feature, she demonstrated how to fill down values in a column to handle ragged hierarchies, a common issue in data sets where subsequent rows may lack values present in the top row.
6. Creating a City-State Column: To ensure accurate geographic data representation, she used the “Column from Examples” feature to merge city and state columns into a single column, which is essential for mapping and data categorization.
7. Adjusting Data Types: Angelica discussed the importance of setting appropriate data types for columns, which can impact the accuracy of the data and the visualizations built on it.

Building Visualizations with Clean Data

Once the data was prepared, Angelica guided participants through creating a simple stacked bar chart in Power BI to visualize average reading scores across school districts. She demonstrated how to change the summarization from sum to average, ensuring that the visual accurately reflected the data’s intent.

Final Thoughts and Next Steps

Angelica wrapped up the session by encouraging participants to save their work and highlighted the importance of data modeling, which will be covered in the next section of the training. She also recommended related courses offered by Pragmatic Works, such as the Beginner Power BI course and the Advanced Data Shaping course, for those looking to deepen their understanding of data preparation in Power BI.

This session provided valuable insights into the often-underappreciated process of data cleansing and shaping, equipping Power BI users with the knowledge to ensure their data is clean, consistent, and ready for insightful reporting.

Don't forget to check out the Pragmatic Works' on-demand learning platform for more insightful content and training sessions on Power BI and other Microsoft applications. Be sure to subscribe to the Pragmatic Works YouTube channel to stay up-to-date on the latest tips and tricks.