Perhaps my thinking is influenced by fiction but it seems incredible to me that a line of kings who claimed descent from a literal god (Merovech) could become so powerless as the Merovingians became. Could anyone shed some light on this?
So, here's the thing, we tend to think of kings as some sort of all powerful monarch whose very word was law, when honestly absolute monarchy was not particularly common in medieval Europe. There's some debate, but most put the era of absolute monarchs as not really starting until the 1500s at the earliest.
In any case, the Merovingian kings were not absolute monarchs. On the contrary, the Merovingians power waxed and waned largely based on military might and political acumen. Take Clovis himself, at the start of his career he was the young ruler of a reasonably sized group of warriors, but he still needed allies from other warbands to make any real progress in gaining wider control of the area. Of these king-chiefs (in the scraps of writing we have about the time they are called regulus or petty kings instead of rex) all of the ones which we have lineages to cite some legendary godlike ancestor. That was part of the complex web by which they justified their authority. So Clovis being the descendant of Merovech was probably not unique. In fact, both of his major Frankish rival kings Ragnachar and Chararic, may have been relatives of Clovis. I believe the jury is still out on Chararic.
So, from the Gesta Episcoporum Cameracensium (which for the record is written some 300 years after the events taking place) we can get a view of what was going on, though because of the time gap may be a bit flawed. We see Clovis realizing that even with his military might he could not control the population without making some concessions to the religion of the land he ruled: Christianity. He took a Christian wife, and later converted. This shows another tension within the Merovingian power structure. The king taking these steps in some ways shows that the church has at least some power over him. The legitimacy that was once granted by the semi-legendary relative is now in the hands of the church.
But this caused a ripple out effect among his warriors. Many did not like this Christianization and sided with other more traditionally pagan kings. One of which was Ragnachar. His fate reveals another intricacy of the Merovingian power structures. The kings ruled with their warriors so long as they could control their warriors. And to control warriors you must give them things to fight, prove that you are an adept commander, and distribute wealth to your followers accordingly.
Now the records have a fun story about Ragnachar being miserly with his gifts and Clovis bribing his warriors to abandon him with armbands that he claimed were gold, but were really gold-plated bronze. In any case there was a battle, Clovis decisively won and Ragnachar was killed. There's a different probably more accurate account of Ragnachar's defeat without the whole fake gold armband trickery. But after his defeat, Ragnachar's own warriors offered him up to Clovis.
In either case, the important points to note are that the king must appease his warriors. Their loyalty is fluid, if they feel unrewarded they will abandon their king. But the king must also show strength, and deal with these unruly warriors harshly should the need require it. It was a delicate balancing act and Clovis handled it marvelously.
Enough about Clovis himself, let's look at after he died. Well his kingdom was split between his sons. This, as one might expect further weakened the position of the king and the brothers made war against each other and vied for power. So the idea of making war against the royal family was not really outside of anyone experience.
Now let's get to the last of Clovis' line. Childeric III was famously deposed by his majordomo Pepin the Short. How did Pepin grow so powerful? Well, for the starters, being the son of a great commander does not necessarily make one a great commander. Many of Clovis' descendants were not up to the task of fighting battles and navigating the politics of ruling under the power of the church while appeasing your warrior class. The position of mayor of the house was originally something of a managerial position for lands the king directly held. But from the turmoil of the Merovingian rulers wars, the position became hereditary and gained increasing power over the distribution of wealth and for particularly weak kings even commanded battles.
Then we get to Pepin's father, Charles Martel. Martel was one of those warriors unsatisfied with the rule of the royal family, and fought several wars against them until the king was forced to give him the title of mayor of the house. Charles then set about the long work of doing everything a king was supposed to do. He won victories, he defended the church, he distributed wealth to the warrior classes. When his king died that didn't really stop. Charles was so powerful he got to appoint the king he wanted. And at the end of his life he didn't even bother to do that. There was no king for four years, with Charles mostly just running things as though nothing had happened.
By the time Charles died, his son Peppin the Short was ready to take up the reins from his father. Peppin was clever, he distributed the wealth, he ran the battles, he made political ties to the church. Another king does get appointed, finally, but only because the sons of Charles were fighting for control and Peppin wanted a real king to add legitimacy to his position.
Once the king served his purpose, and Peppin defeated his brother there wasn't much a point for him anymore. Peppin spoke with the pope, who really needed the assistance of a powerful military leader at the time, to secure the alliance with the Church to depose the Merovingians. Then he got himself elected by his own soldiers who he paid. Then he continued to lead these men in battle and distributed their loot accordingly. And so the transition is completed.
The power of the Merovingian throne rested far more in the political leaders ability to balance the keys to the throne rather than things like lineage. Canny mayors of the palace realized this and over the years took control of those important keys and in turn made the king irrelevant.