Category Archives: cleanup

Data Split into Two rows – Cleanup using Power Query

This is the worst type of input data to get. Each row has been split into two (or sometimes more) rows. We want those to be combined into a single row. In an earlier article, I have shown one method of doing this.

Now here is a simpler, faster and more powerful method using Power Query.
(Estimated reading time 12 min)

image

Continue reading Data Split into Two rows – Cleanup using Power Query

Remove Duplicate Rows Instantly

Just one button to press. Data – Remove Duplicate.

Data should be a table (or like a table – column headings with data below). If it is a table choose Table Tools – Remove Duplicates.
By default, duplication is checked for ENTIRE ROW.
Remove checkboxes to check for duplicates for specific fields only.

Remove Duplicate

Be careful. Duplicate rows are DELETED.

This is unlike Advanced Filter – Unique Rows only – where duplicates are HIDDEN.

For large data – Power Query – Remove Duplicates is infinitely more powerful and faster. We will cover it in a separate article.

***

Miracle: Instant Report Clean Up using Power Query

Reports generated from business applications often become inputs to new reports.

You end up spending too much time cleaning them up. Here is a new and unimaginably faster way of doing it – using Power Query.

Report clean up using Power Query

Estimated reading time 15 min
Estimated time saving huge!

 

Continue reading Miracle: Instant Report Clean Up using Power Query

Worst Practice: Using formatting as a substitute to information

Experts tell us the best practices. But we do lot of mistakes before we can find the best way. Documenting the mistakes – which is what I call “Worst Practices” is also important.

This worst practice is very common. I have found it across the world and across industries. Read on and confirm that you are not making this mistake.

image

Continue reading Worst Practice: Using formatting as a substitute to information

Data Clean-up: System Reports

Data Clean-up: One of the worst formats to get raw data is the system reports. These are reports you generate from business automation software. Export to Excel / CSV option dumps the report into a file instead of a printer. But the report is originally designed for printing. Now you waste too much time removing unwanted headers and footers.

This article shows a quick method, and more importantly, a concept which can be used in various clean-up scenarios as well.

Data Clean-up

Continue reading Data Clean-up: System Reports

Outlook: How to delete all old appointments in Calendar

Calendar view shows only Day, Week, Work Week and Month views. You can only select all appoints within a month. Even in the month view, CTRL A does NOT select all appointments.

In short, any bulk operation you want to do for appointments is a pain.

Solution is simple. Just change the view in Outlook.

Outlook delete all old appointments

Now all the appointments and meetings are shows as a simple list – like Inbox or Tasks..

Now you can sort, filter, multi-select any number of items and perform any operation you like – for example, Delete 0ld appointments or archive to another folder or PST.

AutoArchive does this automatically

Most of us don’t want old appointments to waste space in the mailbox – because the space is limited. The smartest way to clean this up is to run AutoArchive. We will cover this in the next article.