# Power Query: Filter columns by condition



## Mer333 (Dec 8, 2014)

Hi all!

Does anyone know is there any way to filter columns by condition like... at least one of the first 4 rows in a column is not blank (null)?

I'm searching an Internet for this case for almost two days but without any success yet.


----------



## miguel.escobar (Dec 9, 2014)

could you elaborate and provide an example? I'm not 100% clear on what you need yet


----------



## Mer333 (Dec 10, 2014)

miguel.escobar said:


> could you elaborate and provide an example? I'm not 100% clear on what you need yet



Hi Miguel!

Yesterday I saw your post on excelguru and it was amazing! (Transforming Data with Power QueryThe Ken Puls (Excelguru) Blog)

If you won't find an answer so it doesn't exist yet I suppose. =)

So task is very simple on a screen, but difficult to deal with by using M language. Or I just don't know it on the proper level yet.

Image that we have a huge table like:

*column1*
*column2*
*column3*
*column4*
*column5*
*column6*
*column7*
*column8*
*column9*
*column10*
*N*
qqqq
wqwq
qwe
N
sdfwe
ert
N
FFF
aaa
N
1
84
979
2
5699
888
999
978
gfssa
N
N
N
N
N
N
N
N
N
N
N
N


<tbody>

</tbody>
So is it possible to Fill Down only columns that do not have BLANK at least in one of the first two rows (in this example it is 1,5,6,8,10)? Or any other function. 

One thing that I didn't find in M language is a functions by condition...

Another example:

*Column1*
*Column2*
Round
Apple
Flat
Orange


<tbody>

</tbody>
What I want to do here is check whether Column 1 is not BLANK and copy a text to the same Column2 row from next down Column2 row if it is true.

Like:

*Column1*
*Column2*
Round
Apple
Apple
Flat
Orange
Orange


<tbody>

</tbody>

To be honest, I left this tasks for a while but it is still useful to understand how to solve problems like this.


----------



## miguel.escobar (Dec 10, 2014)

it can be done but the hard part is making it completely dynamic so you don't have any issues adding new columns to it. I'll try and work on something and post something out this weekend.


----------



## miguel.escobar (Dec 10, 2014)

Here's the answer for the first one. It could probably be optimized but this is pretty optimized already. 

https://dl.dropboxusercontent.com/u/54063091/MrExcelPQ Question.xlsx

Let me know if this is what you were looking for.

Best,
Miguel


----------



## miguel.escobar (Dec 10, 2014)

I added the answer to the 2nd one as well to the same file. Hope that helps too.


----------



## Mer333 (Dec 11, 2014)

WOW!!!  That's superb! Simple and elegant!  

But I spent 10 minutes and still can't figure out how the second part works. :D 

This one - each try Source{[Index]+1}[[Column2]]


----------



## Mer333 (Dec 11, 2014)

Finally I figured out how it was!!!

It was a solution from that Polish guy on youtube... 

Why didn't you wrote just - each try #"Added Index"{[Index]+1}[Column2]?

So that way you don't need expanding column. And not a previous step as a source like he did?


----------



## miguel.escobar (Dec 11, 2014)

Her Mer,Sorry. I saved that query on the wrong file. The actual solution for the 2nd one is just a "Fill up". I was testing some stuff from Bill (the polish guy on youtube) and still going like you and trying to understand those concepts


----------



## Mer333 (Dec 12, 2014)

What do you mean by just 'Fill up'? I'm not sure that it will work as expected... 

Honestly that solution with index is perfect for that task.

But what if it is more complicated...

Like:

*Column1**Column2*RoundAppleFlatOrange

<tbody>

</tbody>
So here we need not just copy the second down value from Column2 but first non blank down value from Column2:

*Column1**Column2*RoundAppleAppleFlatOrangeOrange

<tbody>

</tbody>


----------



## Mer333 (Dec 8, 2014)

Hi all!

Does anyone know is there any way to filter columns by condition like... at least one of the first 4 rows in a column is not blank (null)?

I'm searching an Internet for this case for almost two days but without any success yet.


----------



## miguel.escobar (Dec 12, 2014)

Sometimes, the easiest solution can surprise us  

let
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    #"Filled Up" = Table.FillUp(Source,{"Column2"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Up", each ([Column1] <> null))
in
    #"Filtered Rows"

the filled up will work if we have the 2nd patter that you sent me. The last one that you provided is not clear to me yet.


----------

