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

Remove Duplicate Rows Instantly

Just one button to press. Data – Remove Duplicates.

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.

image

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

image 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.

Estimated reading time 15 min
Estimated time saving huge!

Continue reading

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