HomeChallengesUsing Power Query for removing rows
learnbook poster
Using Power Query for removing rowsLast updated at Wed Jan 05 2022Skills
data-wrangling
Tools
power-query

Learning Outcomes

  • Learn how to remove unwanted rows from data ingested from Excel sheet using Power Query interface
  • What's new? You'd see how Power Query M helps handle a special case where you wish to remove rows from any row number

Problem Statement


You have just received an Excel sheet with tabular data which looks like this :

p01.png

As we see the above image, in order to do any meaningful data operations, the following issues need to be handled :

  1. Remove the header title row
  2. Remove the 2 blank rows following it
  3. Remove the row having Totals and below

Other than the above, you were also asked to remove rows from 11 to 15 i.e from Backbay, Mumbai to Noida.

How would you go about solving this case?

My Solution


With a simple search on Google, I quite easily figured out the Power Query feature which would help me solve the above case partially. Have a look at this crisp article.

However, It took some research to figure out how to solve the case where we are asked to remove rows from 11 to 15 row numbers.

Well, Power Query M came as my savior (It'll be for you the moment you find that the GUI options are no longer helpful)

Have a look at the below 5 min video to see my attempt :

Wish to discuss your solution and solve more interesting cases?

Join us on Slack today!


Created with 💙 by
author avatar
Amit Choudhary
Data Practitioner,Mentorskool
mentorskool logo
Mentee Today, Mentor Tomorrow
No 206, A Block, Sonesta Silver Oak,Varthur, Bangalore 560066,Karnataka
Copyright - 2022 © Mentorskool - All rights reserved.