To do that, we need to add an index column, and filter to the specific header that we’re after. In order to remove any rows above the header row, we need to know which row the header resides. I then loaded it into Power Query by creating a new query –> From Table Determine the Header Row I can then select the name from the Name box to select my data: So I defined a new range to cover the data The key is that the header row is not the first row. Now, I assume this data is loaded from an external file, but it doesn’t really matter, I’ll just load this from a range, as it’s just a data source. In addition, I wanted to have some garbage data above, as I didn’t want to give the impression we can just filter out blank rows. The components I was after here was the ID Number header and an extra column of some kind. I didn’t have Rudi’s exact data, so I knocked up a little sample with an ID Number and Amount column starting in row 3, which you can download here.
#HOW TO CONDITIONALLY DELETE ROWS IN EXCEL FULL#
While the question was answered in the initial post, I still though it would be interesting to do a full post on this for others who might need to create similar functionality. I cannot use the delete top rows as its not always 5 rows, some import the headings start in row 10, others in row 3.but the label I am looking for is always "ID Number". How do I determine an applied step to delete all rows above "ID Number". I know that the first heading in column A is called "ID Number", but each import has this heading in a different row. I could not find a solution and its been a burning question till now.įor example: If I import a csv file containing columnar info, but the headings for the list are in different rows for each import. The other day I was asked if Power Query could delete all top rows up to a found value. A couple of weeks ago, Rudi asked how you would go about setting up a query to remove all rows up to a specific value.
0 kommentar(er)
