Overcoming Excel’s Limitations with Power Pivot and Power Query

2. Transform Data Using Features in Power Query

Power Query Editor:

• After selecting Transform Data, you’ll be taken to the Power Query editor. Here, you can make various changes such as:
• Delete Columns: Select the columns you don’t need and right-click to delete them.
• Replacing Values: Use the “Replace Values” feature to replace certain values with others.
• Merge Tables: If you have multiple tables that you want to merge, use the “Merge Queries” option to merge by key columns.

Implementing the Change:

Each step of the change you make will be recorded in the right panel. You can add a new step or edit an existing step at any time.

3. Example: Changing Date Format and Cleaning Data

Changing the Date Format:

• If you have a date column in text and want to change it to the correct date format:
• Select the date column in the Power Query editor.
• On the Transform tab, select Data Type, and then select Date. This will convert the column to the appropriate date format.

Cleaning Data:

• To remove an empty or duplicate value from the data:
• Use the “Remove Rows” option to remove the blank rows.
• To remove duplicates, select the required columns and click on the “Remove Duplicates” option.

Integration Between Power Pivot and Power Query

Power Pivot and Power Query are two tools that support each other in Microsoft Excel. When used together, they provide the ability to manage big data, clean data, and perform in-depth analysis with high efficiency. Power Query serves to prepare data, while Power Pivot is used to build more complex relational and analytical data models.

1. Using Power Query to Prepare and Clean Data

The first step is to import and transform the data using Power Query. This includes processes such as cleaning data, merging tables, and changing data formats.

Once the data is ready, the results from Power Query are fed into the Data Model used by Power Pivot.

Steps:

• Click the Data tab, and then select Get Data to open the Power Query Editor.
• Pull data from multiple sources (Excel files, databases, or APIs).
• Clean the data as needed (for example: remove duplicates, and change data types).
• When you’re done, click Close & Load To, then select the Add this data to the Data Model option. This will save the results of the transformation into a Data Model that can be accessed in Power Pivot.

2. Using Power Pivot to Build Relational Data Models

Once the data has been entered into the Data Model, you can use the Power Pivot to create relationships between tables, add calculated columns, and perform advanced analysis with DAX (Data Analysis Expressions).

Steps:

• Click the Power Pivot tab and select Manage.
• You’ll see the data that has already been loaded from Power Query to the Data Model.
• If there is more than one table, use Diagram View to connect tables based on key columns (such as Product ID or Customer ID).
• Use DAX to add new columns or measures.