I had the exact same problem as the OP. I am relatively new to SQL and don't fully understand why but I was able to get my dynamic query to work by selecting just the columns I wanted to pivot from the original source table, which included rows I was not interested in, into a temp table. I used the temp table as the source for the dynamic pivot and everything then worked as expected.

Perhaps someone who understands this better can expand on why it worked. Hope that helps someone.