Power Query: Filter columns by condition

[Mer333]

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]

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

 

[Mer333]

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:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]column1
[/TD]
[TD]column2
[/TD]
[TD]column3
[/TD]
[TD]column4
[/TD]
[TD]column5
[/TD]
[TD]column6
[/TD]
[TD]column7
[/TD]
[TD]column8
[/TD]
[TD]column9
[/TD]
[TD]column10
[/TD]
[TD]N
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]qqqq
[/TD]
[TD][/TD]
[TD]wqwq
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]qwe
[/TD]
[TD][/TD]
[TD]N
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]sdfwe
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]ert
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]N
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]FFF
[/TD]
[TD]aaa
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]N
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]84
[/TD]
[TD]979
[/TD]
[TD]2
[/TD]
[TD]5699
[/TD]
[TD]888
[/TD]
[TD]999
[/TD]
[TD]978
[/TD]
[TD][/TD]
[TD]gfssa
[/TD]
[TD]N
[/TD]
[/TR]
[TR]
[TD]N
[/TD]
[TD]N
[/TD]
[TD]N
[/TD]
[TD]N
[/TD]
[TD]N
[/TD]
[TD]N
[/TD]
[TD]N
[/TD]
[TD]N
[/TD]
[TD]N
[/TD]
[TD]N
[/TD]
[TD]N
[/TD]
[/TR]
</tbody>[/TABLE]

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:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Column1
[/TD]
[TD]Column2
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Round
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Apple
[/TD]
[/TR]
[TR]
[TD]Flat
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Orange
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Column1
[/TD]
[TD]Column2
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Round
[/TD]
[TD]Apple
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Apple
[/TD]
[/TR]
[TR]
[TD]Flat
[/TD]
[TD]Orange
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Orange
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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]

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]

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]

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

 

[Mer333]

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]

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]

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]

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:
[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD]Column1[/TD]
[TD]Column2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Round[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Apple[/TD]
[/TR]
[TR]
[TD]Flat[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Orange[/TD]
[/TR]
</tbody>[/TABLE]

So here we need not just copy the second down value from Column2 but first non blank down value from Column2:
[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD]Column1[/TD]
[TD]Column2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Round[/TD]
[TD]Apple[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Apple[/TD]
[/TR]
[TR]
[TD]Flat[/TD]
[TD]Orange[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Orange[/TD]
[/TR]
</tbody>[/TABLE]