Let's say that you have created the ultimate powerful character to be your protagonist or to help them along their journey. A character who is a functionally a god, invulnerable to all attacks, with a power to fit any occasion. They have super strength and super speed. Perhaps they can even fly, or have magical powers that can warp reality. There can be any number of reasons why this character has appeared in your story, but what is important for this guide is the problem this character creates upon their entrance onto the scene:
As soon as they show up, any narrative tension disappears. There is literally nothing that can even inconvenience this character in a meaningful way.
The exemplar of this type of character is Superman. Notably, Superman did not start out this way - when he first appeared in the 1930s his powers were far more limited. He could leap tall buildings in a single bound, not fly over them. However, as the years went on, more and more powers were added to his character, until he reached the point where he lives in a "world of cardboard."
This represents a major storytelling problem. For a story to have narrative tension, what Robert McKee calls "the forces of antagonism" must be at least equal to, if not greater than, the ability of the protagonist to overcome them. On its face, a Superman-type character short circuits this. However, this is not a problem without solutions, and a character with this level of power can even become one of the more compelling characters in your story.
So, how do we do this? How do we ensure narrative tension and a compelling character when the character in question is functionally a god? There are a few ways we can go about this, all of which have both advantages and disadvantages:
Make the forces of antagonism greater than the character. This can mean forcing the character to face an antagonist who is equal to or greater in power (a massive natural disaster, or a supervillain with even more godlike power). The benefit is that there is narrative tension. The bad news is that this can strain suspension of disbelief in the reader, particularly if it undergoes repeated use. There's only so many times that a character can save the entire world and have the narrative be credible.
Reduce the character's powers for the story. Superman has his kryptonite, in Re:Apotheosis Captain Infinite has his Ultratonium, and just about any Western superhero has some weakness that short circuits their powers. This restores narrative tension without the character having to face off against some potentially world-ending threat, but it also suffers from repeated use. A character with godlike powers becomes laughable once it starts to feel like everybody and their grandmother in-universe is packing kryptonite.
Make their powers irrelevant to the problem to be solved. The character may have all the superpowers, but the forces of antagonism can't be conquered using them, leaving the character to find another way. This is one of the better mechanisms for dealing with this problem, but it is also one of the hardest to use. If you're not careful using this technique, you can accidentally cut the character out of narrative, turning them into a spectator in the story.
Force them to choose. They can use their powers to save one person or another, but not both. This variant of the "trolley problem" (you can save five people from being killed by a runaway trolley, but only by diverting it so that it kills one person) is one of the best techniques to use, as it forces the character to make a difficult choice with meaningful consequences, causing character growth along the way. What is important is to limit the use of "finding a third option" - while this can make for an appealing ending, it undercuts the impact of the choice, and repeated use makes any future trolley problem impossible for the reader to take seriously.
Turn the conflict inward. Have the main antagonist that the character must face not some external force, but an internal one. This makes for some of the most rewarding and compelling character development (in Re:Apotheosis, for example, Captain Infinite is an expy of Superman where time has been allowed to pass in his story, with the result that his life has been hollowed out as his powers grow and the people he cares about die off - his entire character arc is about trying to connect with other people and regain the life that time and power creep has taken away from him). If your character has sworn off using their powers because they once accidentally killed somebody, making the decision to use them again becomes compelling drama.
All of these are workable solutions to the problem of regaining narrative tension, but what should be noted is that in any series, repeated use will always cause diminishing returns. The first time a character has to "find themself" is compelling - the fourth time strains credibility. The first trolley problem is a gut-wrenching dilemma - the tenth will have become a formula that the reader will shrug off.
But, all of this having been said, the most important key to making a super-powered character compelling is and remains making them a fully developed and realized character in the first place. They should have agency and desires, and work towards those desires. Their actions should have consequences that they must face. They should have internal struggles between competing goals. They should, at their core, be recognizably human.
And that's really all there is to it...