How to use Microsoft Power Query

Introduction

This tutorial will guide you through using Microsoft Power Query, a powerful tool that helps you connect, extract, transform, and load data in both Microsoft Excel and Power BI. By the end of this guide, you'll be equipped with the knowledge to clean up data efficiently, making your data analysis tasks smoother and more effective.

Step 1: Obtain Power Query

• For Excel 2010 or 2013: Download Power Query from the Microsoft website.
• For Excel 2016 and later: Power Query is built-in. You can access it directly from the "Data" tab.

Step 2: Load Sample Data

• Navigate to the sample data provided by the tutorial. You can download it from the link in the video description.
• Open Excel and ensure the sample data file is accessible.

Step 3: Get Data into Power Query

• Go to the "Data" tab in Excel.
• Click on "Get Data" and choose the appropriate source based on your data location (Excel, CSV, web, etc.).
• Follow the prompts to connect to your data source and import the data into Power Query.

Step 4: Combine Data

• If you have multiple data sources that you want to combine:
  • Use the "Append Queries" option to stack data from different tables.
  • Use "Merge Queries" to join data based on a common column.

Step 5: Familiarize with Power Query Interface

• Explore the main components of the Power Query interface:
  • Query Pane: Displays all your queries.
  • Preview Pane: Shows a preview of the data currently selected.
  • Ribbon: Contains tools for transforming data.

Step 6: Transform Data

• Perform various transformations using the tools available:
  • Remove Columns: Select the columns you don’t need and click "Remove Columns."
  • Change Data Types: Click on the column header, select "Data Type," and choose the correct type.
  • Filter Rows: Use the filter drop-down in column headers to include or exclude specific rows.

Step 7: Review Applied Steps

• On the right side of the Power Query window, you’ll see the "Applied Steps" pane.
• This shows all the transformations applied to the data. You can click on any step to see how it affects your data.

Step 8: Close and Load Data

• Once you’ve completed your transformations, click on "Close & Load" to bring the data back into Excel.
• Choose whether to load the data as a table, pivot table, or only create a connection.

Step 9: Create Pivot Table and Chart

• After loading the data:
  • Select your data range.
  • Go to the "Insert" tab, choose "PivotTable," and follow the prompts to create an interactive report.
  • Use "Insert Chart" to visualize your data.

Step 10: Import Additional Data

• To import more data later:
  • Return to the "Data" tab, select "Get Data," and repeat the import process.
  • You can also append this new data to existing queries.

Conclusion

Microsoft Power Query is an invaluable tool for anyone working with data in Excel or Power BI. By following these steps, you can efficiently transform and analyze data, making your workflow much more productive. As a next step, consider exploring more complex transformations and integrating additional data sources to enhance your analysis capabilities. Happy data crunching!