Query Remove Rows - Null

[Sayth]

How can I remove rows based on cell contents being 'null'.

I could manually do it in excel by using special to delete them but would like it as part of the query.

My column which appears about halfway across my sheet is this.

edit: number4 is the column header.

[TABLE="width: 79"]
<tbody>[TR]
[TD]number4[/TD]
[/TR]
[TR]
[TD]null[/TD]
[/TR]
[TR]
[TD]null[/TD]
[/TR]
[TR]
[TD]null[/TD]
[/TR]
[TR]
[TD]null[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD]null[/TD]
[/TR]
[TR]
[TD]null[/TD]
[/TR]
[TR]
[TD]null[/TD]
[/TR]
[TR]
[TD]null[/TD]
[/TR]
[TR]
[TD]null[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]

 

[scottsen]

I'm actually getting a bit of a vibe this isn't a Power Pivot question -- if my intuition is correct, you will probably have better luck in the "main" forum.

If I'm crazy... Pivot tables will by default filter out any row where ALL values are blank in the row.

 

[Sayth]

Hi Thanks no this isn't a pivot table question Lol. In building my query in power query you can in reduce data choose options to remove rows but not to my knowledge based on any specified criteria.

 

[ImkeF]

In the column Header you'll find the Dropdown menu: click & choose "number filter" - "Does not equal" - enter "null"

 

[Sayth]

In the column Header you'll find the Dropdown menu: click & choose "number filter" - "Does not equal" - enter "null"

Ok so that is cool to use that way and choose close and load and then if I import more sheets that will hold and apply to all subsequent imports.

 

[TimRodman]

Are you re-pointing Power Query to a different file for subsequent imports or you just want current data from the original file?

 

[Sayth]

Are you re-pointing Power Query to a different file for subsequent imports or you just want current data from the original file?

I want to be able to reuse it continuously on new imports(xml) data that I will be cleaning and saving.

 

[TimRodman]

Yes, it will apply to subsequent imports. If you want Power Query to go grab all the files in a directory and import them one by one then you'll have to do a little more work in the Power Query code, but it doesn't look like you need to get that fancy.