Prepare the Data

1.1 Connecting to Data

·       Discover and connect to data sources
 Learn how to connect Power BI to different data sources or to an existing shared semantic model.

·       Adjust data source settings
 Modify settings such as credentials and privacy levels to ensure secure and appropriate access.

·       Select the right storage mode
 Decide between using Import or DirectQuery mode, depending on your data and performance needs.

·       Use parameters for flexibility
 Create and manage parameters to make your reports more dynamic and easier to update.

1.2 Profiling and Cleaning Data

·       Review data properties and statistics
 Explore the data in Power Query Editor by checking statistics and column details before loading it.

·       Fix inconsistencies and quality issues
 Address problems like mismatched data types, unexpected or missing values, and other data integrity concerns.

·       Handle errors during import
 Resolve any errors that occur while bringing data into Power BI to ensure smooth processing.

🔍 Key points to remember:

  1. Always examine and verify data types in Power Query Editor before loading; incorrect types can block relationships or calculations later.
  2. You can adjust data types either in Power Query Editor or directly within the Power BI Desktop’s Report View.
  3. Any changes you make — like altering data types — are saved as "Applied Steps," making it easy to track or undo transformations.

Power Query Editor Tips for Cleaning Data

·       Rename queries to make them clearer and easier to manage.

·       Replace unexpected values to standardize data.

·       Fill in or replace null values where appropriate.

·       Remove duplicate rows to ensure data uniqueness.

·

1.3 Transforming and Loading Data

·       Assign the right data types to each column for accurate calculations and relationships.

·       Create or modify columns using custom formulas and transformations to prepare your data for analysis.

·       Group and aggregate data to summarize details, such as computing totals or averages by category.

·       Reshape your data by pivoting, unpivoting, or transposing tables to match your reporting needs.

·       Convert semi-structured data (like JSON or XML) into tables that Power BI can analyze.

·       Build fact and dimension tables to support a robust data model.

·       Know when to use reference vs duplicate queries — reference queries reuse logic without copying data, while duplicates make a full copy, which can impact performance.

·       Merge and append queries:

    • Append adds rows from one table to another, best when columns match. It doesn't automatically remove duplicates.
    • Merge combines columns from two tables into a single one, based on a common key column.

·       Define keys and relationships by identifying the correct columns to link tables together.

·       Configure how queries load into your data model to optimize performance and control what gets loaded into Power BI.