Microsoft Power Query

In the world of business, data analysis and interpretation are essential to creating effective, well-informed decisions. However, messy and unorganized data can create serious roadblocks in this process, leading to inefficiencies, and lost opportunities. Fortunately, Microsoft Power Query is a powerful tool designed to eliminate the headaches and tedious tasks related to creating clean and accurate data. The first step is to organize your data.

What is Microsoft Power Query?

Microsoft Power Query is in Excel under the data tab under the “Get and Transform Data” group option. It is designed for data transformation that can also be used for other Microsoft applications such as Power BI and Power Automate. The tool allows users to connect to various data sources, transform data into the desired format, and load it into their preferred destination in ways that native Excel functions such as formulas and pivot tables cannot. 

Benefits of Using Microsoft Power Query

Data from Multiple Sources: Power Query acts as a one-stop shop for you to connect to a wide range of data sources, including databases, spreadsheets, web services, and more. This means you can gather data from multiple sources in one place for analysis.

Data Transformation: With Power Query’s intuitive interface, you can easily clean, reshape, and transform data without writing complex code. It offers a range of functions and transformations to suit your needs.

Automation: Power Query enables automation of data extraction and transformation processes. This reduces manual errors and saves time, especially when working with large datasets.

Reusable Queries and Data Volume: The tool saves a history of previous queries for future use. This allows users to reuse processes for similar datasets, saving time and effort. Additionally, queries can import copious records of data that would not fit into a standard Excel worksheet.  

Data Connection: Updates from the source data are seamlessly integrated into your Power Query process. This ensures that your analysis is always based on the most up-to-date information.

Data Organization with Power Query

1. Data Connection

To start organizing and cleaning data using Power Query, you need to establish a connection to your data source. In this example, the data is in an Excel worksheet as a table. 

Loading the table can be done via the “From Table/Range” option in Excel or the relevant data source connector in other Microsoft applications.

2. Data Import and Preview

Once your data source is connected, Power Query displays a preview of your data. This step allows you to review the data’s structure and make initial decisions about what needs to be cleaned or transformed.

3. Removing Duplicates

Duplicate data can skew your analysis. Power Query provides a simple way to remove duplicate rows, ensuring that your data is clean and accurate. To do this, highlight the desired columns and select “Remove Rows” and the “Remove Duplicates” option.

4. Filtering Data

You can filter data based on specific criteria. This can be helpful when you need to focus on a particular subset of your data. Find the drop-down menu above each column header to easily filter your data.

5. Data Transformation

Power Query provides a wide range of transformation options. You can perform tasks like splitting columns, merging data, renaming columns, and more. These transformations help you reshape the data to fit your analysis needs. To rename, simply double-click the column header and type the desired name.

6. Reporting

After making the desired changes to your data, return the data back to excel by selecting the “Close & Load” option and selecting “Close & Load To.” Then select the desired output in the following window. 

Additionally, Pivot tables can be used to retrieve data directly from Power Query. This can be helpful when the data set does not fit in a native Excel spreadsheet. To do this, select “Close & Load” in Power Query, and select “Close & Load To.” From the following dialogue box, select “Only Create Connection.” Now you can insert a Pivot Table using an external data source and select your query.

Microsoft Power Query is an indispensable tool for professionals working with data. Whether you’re dealing with financial records, customer information, or any other dataset, Power Query simplifies the process of data organization and cleansing, saving you time and ensuring data accuracy. 

Check back soon for our next blog post on Microsoft Power Query. We’ll share tips on using Microsoft Power Query to cleanse your data.

Revelwood is an award-winning, Platinum Workday Adaptive Planning partner. We build solutions for the Office of Finance that minimize your risk by seamlessly incorporating business analytics into your everyday thinking. Combining the software with our best practices and out-of-the-box applications, we help businesses achieve their full potential with Workday Adaptive Planning.

Read more Workday Adaptive Planning Tips & Tricks:

Limit the Drill Down List on a Workday Adaptive Planning Report

Workday Adaptive Planning Tips & Tricks: Restrictions on Cube Sheets

Workday Adaptive Planning Tips & Tricks: Metadata Loaders