Tag Archives: data cleanup

Power BI

Split column into rows

2 Comments

A brilliant new feature is now available in Power BI – Split column into rows. To understand why we need it, you must go and read the article  – Analyzing badly captured Survey data or feedback forms. This method used Power Query concepts of Split and Unpivot. Now these have been combined into a single, intelligent command called Split columns into rows.  It sounds confusing at first. But soon you will realize that it is an amazing tool.  Learn it just 4 minutes.

Raw data looks like this

Split column into rows - raw data

And you get a report like this. No need to use formulas or do any manual work.

Split column into rows - Final Charts

You must have the May 2017 update for Power BI Desktop installed.

Continue reading Split column into rows →

Excel

How to Really Remove Duplicate Rows in Excel

1 Comment

Watch this five minute video to understand what we “really” mean by Remove Duplicate Rows in Excel. The default option removes the duplicate rows automatically. Most of us want to “see” the duplication. That is what leads to the confusion. Not to worry. Learn the right method now.

Remove duplicates QRScan this code and watch the video on your mobile phone and try it out on your laptop.
Download the sample file if you want to try it out yourself.
Continue reading How to Really Remove Duplicate Rows in Excel →

Power BI

When to use Remove Top and Bottom Rows options in Power Query

Leave a comment

Here is a quick post to show the practical scenario when the Remove Top and Bottom Rows options are useful while cleaning up data using Power Query.

Remove Top and Bottom Rows option in Power Query

Remove Top Rows

Often we get CSV files which have tabular data. Sometimes, there is a title, blank rows and some other related but unwanted data just above the useful tabular data. In such cases, we use the Remove Top Rows option.

Remove Bottom Rows

If you get data from web pages (HTLM tables), the header row is repeated at the bottom. This option helps you remove the unwanted row.

Another common issue is that many reports dump data with a grand total at the bottom. Remove Bottom Rows option is also usefu in these cases.

Best Practice

It is quite possible that we focus on the top few rows while cleaning up data. It is equally important to import the data and look at the bottom rows. The bottom part of the data can have many issues which often go unnoticed and may even generate inaccurate results.

cleanup, Excel, Power Query

Data Split into Two rows – Cleanup using Power Query

3 Comments

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 →

Analytics, Excel, Power Query

Reversing row order in Excel data

5 Comments

There are two simple approaches in reversing row order. One is to add an extra column with serial numbers and then sort on that column in descending order. This works for small amount of data. For large data it is best to import it in Power Query and choose Transform tab – Reverse Rows.

reversing row order in Excel data

Benefit of Power Query? It works on a smaller sample of data and then applies the transformation when you choose Save and Load option. This is much faster than getting all the data and then trying to sort it (which is the first method).

Why is this required? Usually required with logs where the first transactions or rows are at the bottom. So the data is received in reverse chronological order. Twitter feeds, Timeline Updates, Live blogs – all follow this pattern.

This method works independent of the time-stamp column. What is wrong with timestamp? It may be in different time formats, some rows may have same timestamp and some rows may have no timestamp at all.

cleanup, Excel

Remove Duplicate Rows Instantly

Leave a comment

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.

***