In our last blog post on using Microsoft Power Query, we talked about using the tool to organize your data. Today we’ll look at using it for data cleansing.
Data Cleansing with Microsoft Power Query
1. Handling Missing Values
Missing or incomplete data can lead to poor analysis and translation of data. Power Query lets you replace, remove, or fill in missing values according to your data quality requirements. To do this, select the column with missing data and right-click. In the new window, select fill and the desired fill direction.
2. Standardizing Data
Inconsistent data formats can create confusion. Power Query helps you standardize data by applying formatting rules, such as converting text to lowercase or uppercase. Right-click after selecting a column and select transform in the new window, choose a desired output.
3. Error Handling
Power Query allows you to identify and handle errors in your data. You can replace errors with specific values or filter them out entirely. To remove error simply highlight the column with the error and select the “Remove Rows” option on the home tab. From there select “Remove Errors.”
4. Data Type Conversion
Ensuring that data types are consistent is crucial. Power Query automatically helps you by converting data types to match your analysis needs.
By harnessing the power of Power Query, you can unlock valuable insights from your data and make data-driven decisions with confidence. So, start using Power Query today, and take your data preparation skills to the next level!
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
