Problem with Query Filters on Excel

[garyj]

Hello to all in Power Query world...

I developed a table, selected Data/ "Queries and Connections" and then "From Table/Range". I want to query the table by "null" values in either of 2 columns. In other words if either field has a null value in one record, I want that record to show. I estimate it to be something like this...

[CODE]= Table.SelectRows(#"Changed Type", each ([Field1] = null or [Field2] = null))

Then I close and load. I take that query and move it into the form where I want it to show. In the first field is the individual ID. The user selects the ID they want to view and it now needs to refresh with all records for that individual that have either Field1 or Field2 as null. But it is also loading records from other individuals that are null. See picture attached. (The first queries skipped row 1, which makes sense, no nulls. It for some reason ignored row 2 - not sure why. It then took row 3 which means the either or is working at least for that field. It then took row 4 which could be for the ID or for the nulls or both?

Any idea how to fix it? I need to have the ID selection made by user, and the other to always be there.

Thanks

Untitled.jpg

Gary

[georgiboy]

What version of Excel do you use?

This could also be done with the FILTER function where there would be no need to refresh.

[garyj]

Microsoft 365, Version 2307

Do tell.. how do I do this Filter function?

[garyj]

I just removed the Refresh from the VBA code and also changed the auto-refresh so that it only refreshes when requested... and somehow that did the trick for the one table as shown above. I have two tables. Both are given queries in Power Query, and both then loaded to Excel where I use them on the first page. The user enters the person they want to view records for, and the VBA code grabs the ID from the selection and sets it into both tables.

The second is messing up by showing rows that shouldn't be shown, or missing rows that should be shown. I figured out why... [sheepish grin].

I had the tables side by side, for spatial reasons, forgetting that the table filters by hiding or showing rows, meaning that the second table to load will undo the work of the first one by hiding a row it needs, or by showing a row that was hidden.

So I guess it is solved. I moved the table down a few rows and it works fine.

Thanks for the response.
Gary

[georgiboy]

Maybe something like the below formula, also in the attached:

=LET(
d,FILTER(Table1,(Table1[ID]=$B$10)),
f,FILTER(d,(INDEX(d,,7)=0)+(INDEX(d,,9)=0)),
IF(f=0,"",f)
)