List.RemoveMatchingItems function doesn't work

[olaszkar]

Hello ladies & Gents!

I have the following problem. I have a Power BI visualization where 2 excel tables are connected with 1:n connection. In order to make it work I need to eliminate all rows which are empty or cointains "null" information. There is a function in Power Query whcih can delete all empty rows. 

= Table.SelectRows(#"Első sorok megtartva", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})))

But it doesn't work.

My temporary solution works but it is definetely not the most professional way namely keeping the specific nunbers of rows from the table: = Table.FirstN(#"Típus módosítva",73) 

Here is the 1:n connection

When I want to remove empty rows with classic way, the Power Query does nothing and “null” values rows remain  - see the last row in this case - and corrupt my 1:n connection. So when I add one or more rows to the table I need to change the number of rows in Power Query equal also which is not the best way 

Can you offer me a solution which would work without any manual intervention? 

I'd appreciate that

Br: Karoly

[v-stephen-msft · Accepted solution]

Hi @olaszkar ,

We often remove nulls in Power Query by the following ways:

1.Apply a filter to the column so that it does not include nulls.

2.Regardless of the column, you remove rows that are blank. Start menu. Remove Rows option, Remove Blank Rows option.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[olaszkar]

Hello Stephen!

Yes, your solution worked. Can you please tell me the logic behind? (Why did it worked? Why does the filtering necessary?)

I'd appreciate it!

Br

Karoly 

[PwerQueryKees]

I don't fully understand what you are trying to achieve.

The use of the Table.Isempty combined with a Table.Selectrows comes to mind.

Or even simpler, a mergejoin with a left or richt anti.

But you may have tried that...

[olaszkar]

Hello!

All I want is to remove the "" or "null" rows from the bottom because it corrupts my 1:n connection. Unfortunately the standard solution (DAX is generated by Power Query): "Table.SelectRows(#"Típus módosítva", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})))" doesn't work.

I have a temporary solution, but it is not nice

[Ahmedx]

or

= Table.RemoveLastN(#"Első sorok megtartva", (x)=> List.IsEmpty( List.RemoveMatchingItems( Record.ToList(x),{"",null})))

[olaszkar]

Unfortunately this is the message I got back: the name Sorted rows1 can't be recognized.

I tried to modify the script: with integration of your proposal. Although there is no syntax error now, but as you cas see, the command is uneffective 😞

[Ahmedx]

most likely your date column is not empty or there is another one that is not empty

[Ahmedx]

pls try this

 = Table.SelectRows( #"Első sorok megtartva",
     (x)=> not List.IsEmpty( List.Select( Record.ToList(x),(x)=> x<>"" and x <> null) ))

[olaszkar]

Hello Ahmedx!

Firstly thank you for your support. Unfortunately a syntax error popped up: 

"Első sorok megtartva"

can't be recognized by PBI. Honestly I don't know why. For me it is clear. When I tried to integrate your script after comma, there was no syntax error, but the command was as unneffective as the other solution