Merge Sheets using Power Query

Question

Hi,

I'm merging 2 Excel Sheets using Power Query.  Everything merges fine, but I'd like to fine-tune the output to only show rows where 2 of the columns are null.

i.e. If the sheet had the following fields, I only want to see the Rows where BOTH Date 1 and Date2 are empty.  I still want to see Rows where only 1 is empty.

ID  |  Name   |  City   |  State   |   Zip   |   Date 1   |  Date 2

Any help would be appreciated. 

Thanks for your time!

Answer 1

In the Power Query window, filter on the two columns individually.

Answer 2

Hi,

In the Power Query window itself, you can filter the two columns on blanks.

Thanks for the reply.  Can you elaborate on how to do it?  I've tried filtering within the Power Query window, but can't figure out how to filter on both columns.

Thanks!

Answer 3

You should be able to filter this in table in Excel if your output goes to worksheet instead of data model. You might have further filtering requirements in future, so don't lock your query into a specific mode.

Thanks for the reply! That was my initial idea - using the SumProduct function to seek out only rows that had both columns blank, but I'm ultimately going to use the filtered list in a PivotTable, so that didn't work too well.  It also needs to be easily added to.

Answer 4

Hi,

In the Power Query window itself, you can filter the two columns on blanks.

Answer 5

You should be able to filter this in table in Excel if your output goes to worksheet instead of data model. You might have further filtering requirements in future, so don't lock your query into a specific mode.