Power Query: Filter columns by condition

Mer333

Board Regular
Joined
Jun 28, 2014
Messages
54
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.
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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.
 
Upvote 0
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.
 
Upvote 0
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]]
 
Upvote 0
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?
 
Upvote 0
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 :)
 
Upvote 0
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:
Column1Column2
Round
Apple
Flat
Orange

<tbody>
</tbody>

So here we need not just copy the second down value from Column2 but first non blank down value from Column2:
Column1Column2
RoundApple
Apple
FlatOrange
Orange

<tbody>
</tbody>
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top