Make your New Year's resolution of orderliness a reality with a digital data cleanse; Marie Kondo-style. With the PowerQuery feature in Excel, you can easily clean and organize large amounts of data, without losing the connection to the original data.

Getting the cleaning party started: Creating data order

The PowerQuery data transformation tool (a.k.a. cleansing wheels) enables you to bring order to huge amounts of data in a simple and flexible way before importing the data into Excel.

You can import data from all kinds of sources: databases, text files, Excel, websites or cloud services like Azure SQL Database and SharePoint.

Shape the data to your liking. Feel free to delete columns, filter rows, change data types and add columns. Want to try more advanced data processing? Try making changes to the formulas, using the functional language M. You can always undo your changes - dare to experiment!

The world is changing - and so is your data! Since your cleaned data is linked to the original data, you can always ensure that your data is up to date. Schedule updates or update the data in your Excel file manually, when needed.

1.   Open a new workbook in Excel
2.   Click on Data
3.   And then click on Get data
4.   Select From file
5.   Then select From Excel workbook/From Excel workbook
6.   Select the file containing the data you want to compile and check. Finish by clicking Import/Import.
7.   Select your data and then click on Transform data.
8.   In this window you can make lots of changes to your data, without affecting the data in the original file. In this example, we select and delete a number of columns
9.   Also try clicking on Add column.
10.   Select Custom column
11.   Select a number of columns and put a plus sign between them to create a summary column. Finish by clicking Ok
12.   When you are satisfied with your data compilation, click on Home/Start in the menu.
13.   And finishes by selecting Close & load.
14.   The data is now inserted in your workbook.
15.   As your summarized data is linked to the data in the original file, you can click on Refresh to easily update the data according to the data in the original file.
16. Enjoy chaos-free data!

Clearing data from a website

1.   You can also insert data from sources other than Excel files. Click on Home/Start
2.   Click on Get data
3.   Select From other sources
4.   Then select From web
5.   Paste a link to the website whose data you want to compile and check.
6.   Click Ok
7.   The data from the web is inserted in your workbook. When you want to refresh the data, click on Refresh.