Power Query: Check if 32-bit or 64-bit

Question

Is it possible to determine whether the current process is 32-bit or 64-bit with M code in Power Query for Excel?

The specific use case is for ODBC connections, for which the connection details are necessarily different between 32-bit and 64-bit processes. Without the ability to check what the current architecture is, there is presumably no way that an ODBC connection can be used in Power Query in a way that's portable across 32-bit and 64-bit versions of Office.

Workarounds with VBA macros should be possible but would not answer the question. Besides security concerns, macros would be problematic because there is no way to guarantee that they are enabled.

Answer 1

For ODBC connections, based on DS_London's comment, I tried using

# DOES NOT WORK
try Odbc.Query(connStr32bit, query) otherwise Odbc.Query(connStr64bit, query)

but it doesn't catch the error properly, presumably due to lazy evaluation, similar to the known limitation described in this thread for a different type of data source.

The solution is to use a combination of Power Query's try syntax for error handling as well as Table.Buffer(...) to force evaluation of the expression. You can also use Odbc.DataSource when testing the connection to prevent forcing the query results to be buffered.

Full solution:

let
    ConnStr32Bit = "...",
    ConnStr64Bit = "...",
    Query = "...",
    Check32Bit = try Table.Buffer(Odbc.DataSource(ConnStr32Bit)),
    ConnStr = if Check32Bit[HasError] then ConnStr64Bit else ConnStr32Bit,
    Result = Odbc.Query(ConnStr, Query)
in
    Result