Basic S Column Alignment with siunitx package

The S column type is meant to take inputs in the same format as the single mandatory argument to siunitx's \num{} command. Anything else is interpreted as text (for headings, etc.) and is centered about the decimal point location.

This is why you see the overall centering behavior: The \SI{}{} is interpreted as something that is "not a number" and is formatted as header text or other material.

To typeset the units, use an s column, which takes inputs in the same form as the mandatory argument to the \si{} command. This should only be used if the units are not the same for every row in the table.

Example:

\documentclass{article}
\usepackage{siunitx}

\begin{document}
    \begin{tabular}{S[table-format=1.6]s}
    \multicolumn{2}{c}{Measured Values} \\
        0.0031   & \meter \\
        0.003123 & \meter \\
        0.00345  & \meter \\
    \end{tabular} 
\end{document}

If the units of a particular column are the same for every row (as is the case for most tables), use the recommendation of Section 9.3 of the siunitx manual; that is, represent the numbers using dimensionless ratios:

\documentclass{article}
\usepackage{siunitx}

\begin{document}
    \begin{tabular}{S[table-format=1.6]}
    {Measured Values/\si{\meter}} \\
        0.0031   \\
        0.003123 \\
        0.00345  \\
    \end{tabular} 
\end{document}

Or (my preference):

\documentclass{article}
\usepackage{siunitx}

\begin{document}
    \begin{tabular}{S[table-format=1.6]}
    {Measured Values (\si{\meter})} \\
        0.0031   \\
        0.003123 \\
        0.00345  \\
    \end{tabular} 
\end{document}

Notice how table-format=1.6 is used to specify the maximum number of integer and decimal digits in each column entry. This allows for proper centering of the data. Also, I used curly braces around the heading to "protect" it and mark it as non-numerical data. This is not required here, because siunitx is pretty smart about detecting these cases. The curly brace protection is useful in certain cases where siunitx gets confused (integer index headings, for example).

In case if you have to use different units for different numbers, you have these possibilities with table-space-text-post and table-align-text-post:

\documentclass{article}
\usepackage{siunitx}

\begin{document}
    \begin{tabular}{S[table-format=1.6,table-space-text-post = \si{\meter}]}
    {Measured Values} \\
        0.0031\si{\meter}   \\
        0.003123\si{\meter} \\
        0.00345\si{\meter}  \\
    \end{tabular}
    \begin{tabular}{S[table-format=1.7,table-align-text-post = false]}  %% note 1.7 here just to align things at center
    {Measured Values} \\
        0.0031\,\si{\meter}   \\
        0.003123\,\si{\meter} \\
        0.00345\,\si{\meter}  \\
    \end{tabular}
\end{document}

There is also pre version of above commands - table-align-text-pre which does the same before the number.