Why does Cantor's diagonal argument not work for rational numbers?

It applies, in the sense that you can carry it out. However, the number you obtain through the process is not a rational (it does not have a periodic decimal expansion).

To be precise, the procedure does not let you guarantee that the number you obtain has a periodic decimal expansion (that is, that it is a rational number), and so you are unable to show that the "diagonal number" is a rational that was not in the original list. In fact, if your original list is given explicitly by some bijection, then one is able to show just as explicitly that the number you obtain is not a rational.