All courses REMOTE until June 30 | Limited in-class sessions in June - Call Us | From July 1 all courses available in-class and remotely

Data visualisation on laptop

Power BI - Remove Rows By Filtering Data

September 26, 2017

Your Power BI Guide

Follow our tutorial below and learn how to remove rows by filtering data in Power BI.

Excel files imported into Power BI need to be consistent in their format. Especially their structure for foolproof query refreshing and data workflow. In most cases that is indeed the case but you might have situations where the format is not consistent.

You might have a situation where the Excel file is exported from another source system and the actual data starts at different rows for every export. In such cases standard ‘Remove Rows’ transformation steps are not useful as the row number relating to the actual data changes between refreshes. This article will describe a way around the problem to handle such a scenario.

Consider the following sheet from a spreadsheet which is exported weekly from an ERP system.



 

 

In this export, the actual data starts from row 10 which are the row headers. However, in the next export the data might start from row 8 and in the following export the data might start from row 15, and so on. The row number pertaining to the start of the actual data is random and cannot be predicted across different exports. In the export, the objective is to remove the top 9 rows from the sheet. The M code looks like this:

 



 

 

Let's see what the data looks like at each of the applied steps

 



 







 



 

We have demonstrated how to extract and filter data from an Excel spreadsheet when the data can start at any row in the spreadsheet. Let us know if you put this to use!

View Power BI Courses

What is Remote Training?

Valued by Individuals

 

4.8/5
Over 20822 Reviews
Great Explanations, Very Helpful, Highly recommend. -Excel Intermediate Perth
Luke - Excel Intermediate, .
Jagg was really great, good amount of detail and a good pace, made it easy to keep up and really learn everything. Thank you -Excel Intermediate Sydney
Madelene - Excel Intermediate, .
Great communication skills -Excel Intermediate Perth
Anthony - Excel Intermediate, .
. -Office365 Intermediate Sydney
James - Office365 Intermediate, .
Helpful and well explained -Office365 Intermediate Sydney
Monika - Office365 Intermediate, .
John was really good at power BI and some of the concepts were great. I will keep all these details close by and even reach out to John if I have further questions. Thanks for the day! -Power BI Advanced Canberra
Matthew - Power BI Advanced, .
Thanks -Power BI Advanced Canberra
Gabriela - Power BI Advanced, .
The training was really great. Jag kept us engaged for the whole day which is great. I learnt a lot and can't wait o put the tips and tricks in action. Thanks -Excel Intermediate Sydney
Rosalind - Excel Intermediate, .
Jag is so very good! Was very engaging and actually very fun! -Excel Intermediate Sydney
Michelle - Excel Intermediate, .
Good overall -Power BI Advanced Canberra
YuinAi - Power BI Advanced, .
Michael was great - he had a good pace and checked in with us to see if we had any questions. He is clearly very passionate about Power BI which enhanced the whole experience -Power BI Beginner Sydney
Ally - Power BI Beginner, .
It was very informative and Michael was very helpful and detailed. -Power BI Beginner Sydney
Alan - Power BI Beginner, .
Very good introduction to PowerBI. Concepts very well explained. -Power BI Beginner Sydney
Eric - Power BI Beginner, .
Thanks Ken, very informative. -PowerPoint Level 2 Sydney
David - PowerPoint Level 2, .
Thank you was a great session. -PowerPoint Level 2 Sydney
Nikita - PowerPoint Level 2, .
Really useful tips and shortcuts -PowerPoint Level 2 Sydney
Devlin - PowerPoint Level 2, .
Thanks- great course! -PowerPoint Level 2 Sydney
Gabrielle - PowerPoint Level 2, .
Helpful. Thank you very much. -PowerPoint Level 2 Sydney
Guida - PowerPoint Level 2, .
Wonderful course. Enjoyed it very much and learnt many new features -Power BI Beginner Sydney
Victor - Power BI Beginner, .
. -Power BI Beginner Sydney
Steve - Power BI Beginner, .

Trusted by Business

Procured by Government

Map of Australia