Dimensional analysis is used when you're trying to figure out how a certain set of parameters (your "inputs") can be combined to yield a quantity with a particular set of units (your "output"). It only works under the following assumptions:
You know what all of your inputs are, and the list of inputs is finite;
There are no redundancies in your list of inputs (i.e. each input has different units), or, if there are redundancies, there must be additional information that constrains the behavior of the redundant inputs (e.g. "the Coulomb force must involve both $Q_1$ and $Q_2$");
Each of these quantities are expressed in units that are compatible with each other and with your output (in practice, this means that each of them should be expressible in only SI base units);
Any constant factors are assumed to be pure numbers, and
The number of additive terms is constrained to be finite.
If these assumptions are satisfied, dimensional analysis will yield a set of possible combinations of inputs ("formulas") that will have the same units as your output. If you're lucky, there will only be one; if you're not, there will be a few, which serve as arguments for an arbitrary function (this follows from the Buckingham Pi theorem).
Let's try this procedure on your two examples. In the first one, we have a pendulum. Our output is the period, which has units of time ($T$). The inputs are:
The length of the pendulum rod, which has units of length ($L$);
The local gravitational acceleration, which has units $\frac{L}{T^2}$, and
The mass of the pendulum, which has units of mass ($M$) (since we don't know a priori that it can't be an input, we include it as a possible input).
Using dimensional analysis, we can constrain the possible forms for the formula using the following equation and solving for the powers $a$, $b$, and $c$:
$$T=L^a\left(\frac{L}{T^2}\right)^bM^c$$
where $k$ is some unitless proportionality constant. Equating the powers of $L$, $T$, and $M$ on the left-hand side with their powers on the right-hand side:
$$0=a+b\quad\quad\quad 1=-2b\quad\quad\quad 0=c$$
Solving this system gives you $a=\frac{1}{2}$, $b=-\frac{1}{2}$, $c=0$. So, even though we didn't know that the period didn't depend on mass before, we have just proven that this is the case, assuming that our list of inputs was complete. Substituting the powers back into the original expression, we get an expression for the period $\tau$:
$$\tau=k \ell^{1/2}g^{-1/2}m^0=k\sqrt{\frac{\ell}{g}}$$
Since the solution to the linear system above is unique, there is only one term.
For Coulomb's law, we must use a system in which the units of each quantity are compatible; as such, we use Gaussian electromagnetic units, in which charge has units of $M^{1/2}L^{3/2}T^{-1}$ (i.e. units of g$^{1/2}$ cm$^{3/2}$/s). The units of the separation are, of course, $L$. In this case, we have three inputs: the two charges $Q_1$ and $Q_2$, which both have the above units, and the separation between the charges. Our output is force, which has units of $MLT^{-2}$ (i.e. g cm/s$^2$). Setting up dimensional analysis again:
$$MLT^{-2}=(M^{1/2}L^{3/2}T^{-1})^a(M^{1/2}L^{3/2}T^{-1})^bL^c$$
Solving for the powers:
$$1=\frac{a}{2}+\frac{b}{2}\quad\quad\quad 1=\frac{3a}{2}+\frac{3b}{2}+c\quad\quad\quad -2=-a-b$$
This system is degenerate, and gives you one free parameter, so $(a,b,c)=(a,2-a,-2)$. As such, the abstract general form of the force that you get with dimensional analysis is
$$F=f\left(\left\{k_a\frac{Q_1^aQ_2^{2-a}}{r^2}\right\}_{a\in\mathbb{R}}\right)$$
for some arbitrary function $f$. Note that we immediately get the $1/r^2$ nature of the force just through dimensional analysis. It is also very easy to experimentally eliminate all but one of these possible arguments, by invoking one piece of additional information, that can easily be gleaned from experiment: exchange symmetry. The force on two charges does not change if you swap the two charges with each other. This means that the powers on $Q_1$ and $Q_2$ must be equal. As such, the only possible powers are $(a,b,c)=(1,1,-2)$. This additional empirical information eliminates the redundancy in this dimensional analysis, and so we arrive at the correct formula:
$$F=k\frac{Q_1Q_2}{r^2}$$
This all comes with one major caveat: if there's an input that you don't know about and don't include, then you could get an entirely different formula. For example, let's look at the pendulum again. Let's assume that in this case, the rod is very slightly elastic, with an effective spring constant $K$ that has the usual units of N/m, or abstractly $MT^{-2}$. Now, redoing the dimensional analysis yields:
$$T=L^a\left(\frac{L}{T^2}\right)^b M^c \left(\frac{M}{T^2}\right)^d$$
Which yields the following system:
$$0=a+b\quad\quad\quad 1=-2b-2d\quad\quad\quad 0=c+d$$
Note that there are 4 variables and 3 equations, so there is 1 free parameter, which I will take to be $c$. As such, we solve the system to obtain $(a,b,c,d)=(-c+1/2,c-1/2,c,-c)$, which gives us an abstract general formula:
$$\tau=f\left(\left\{ k_c\left(\frac{\ell}{g}\right)^{1/2-c}\left(\frac{m}{K}\right)^c\right\}_{c\in\mathbb{R}}\right)$$
again for some arbitrary function $f$. Now, if $c$ is nonzero, then the period of a slightly elastic pendulum does depend on the mass of the pendulum, and the particular way that it does must be measured.
But now let's consider the limit of very slight elasticity (i.e. the limit of large $K$). Equivalently, suppose $\tau$ varies slowly enough with $m/K$ that $\log\tau$ vs. $\log(m/K)$ is well-approximated by a line. This means that there is only one nonzero term in the above set of arguments, since a linear log-log plot corresponds to power-law behavior. This simplified our expression considerably:
$$\log\tau=\log k+\left(\frac{1}{2}-c\right)\log\left(\frac{\ell}{g}\right)+c\log\left(\frac{m}{K}\right)$$
Therefore, we have reduced a complicated physical task (finding the period of a pendulum with a slightly elastic rod) into the much easier task of empirically finding the two constants $k $ and $c $.
