Delete all the row when there is null in one columns of the table

[Recoba88]

Hi,

If I want to remove the whole row where is blank/null record on Inventory Item column. How can I do it? 

[acerNZ]

Hi experts,

I was searching for this issue, I have a question, I cannot filter as my rows are more than 1000 about 18000, so how can I filter the null as filter says Limit of 1000 values reached. Is there M language query to identify null & delete them

[Anonymous]

If the dataset is too large (>1000 rows), here is a shortcut.

In query editor Home tab:
'Remove Rows'
'Remove Blank Rows'

[MarcelBeug · Accepted solution]

Just use the column drop down and filter out nulls.

[Recoba88]

If I filter the data like you said

I  still will bring data to Power Query  that I will not use and will calculate it when file will be refreshed

is that right?

[MarcelBeug]

If I interpret your question correctly:

If you are importing data: yes.

But after running your queries the data won't be in the data model.

If you are using Direct Query: no (assuming that this will work with Direct Query in the first place, which I don't know).

[Recoba88]

So If I want to reduce the data that I am importing , how can I do it?

I thought about define a range in other workbook based on offset formula and thus it will be bring only rows withou null value

what do you think about it ?

[MarcelBeug]

Do you mean you have too many rows in your source data?

Because after running the import queries the null values are gone, so that should be OK, shouldn't it??

In other words: I'm totally confused about what you are trying to achieve,

[Recoba88]

In my source data I have 300 rows. actually I need only 100 rows if I filter the null rows.

When I refresh the data the power query brings me all the 300 rows even if I filter the null. No?

[MarcelBeug]

No. Only 100.

I thought it was about millions of rows that would be too much for import.

[Recoba88]

it is about a 100 rows per worksheet. I have 450 workbooks and 5 sheets 

450*5*5= 11250 rows if I consolidate them

[v-yuezhe-msft]

Hi @Recoba88,

After you filtered the null values in your table, Power Query will only refresh the remaining rows.

Thanks,
Lydia Zhang