Merging two queries, error when one query has no data

[Will85]

I have two tables in excel that I have connected too to create queries A & B. I have (left) joined queries A & B to create query C.

I have a pivot table that is connected to query C.

99% of the time both query A & B will always have data, but there are instances where, based on user selectable dynamic filters, query B might not have any data at all.

In these instances when I refresh the data, I get an error, and in power query, query C is showing one cell with an error, because there is no data in query B.

How can I force query C to still work, and perhpas display zeros for the all columns that were coming from query B?

Or, can I add some type of helper column to query B and hardcode some random value to always ensure there is data? ( I cannot add to the excel table itself)

 

[Matt Allington]

There’s 2 general approaches. You can manually edit the M code using the try statement
try <your current code> otherwise <new code that doesn’t cause an error>

The other approach is to try to make it always work. What you do depends on the specifics, but you could try appending a single record to the table before the merge, then filter that record out after the merge. Just a guess.