Error with Sage ODBC

You'll need to enter a SQL statement for Power BI to connect, the Sage ODBC driver works fine with MS Query via Excel but with Power Query you need to be quite explicit; also in my experience attempting to retrieve [TABLE NAME].RECORD_CREATE_DATE, [TABLE NAME].RECORD_MODIFY_DATE results in errors as well, so you'll probably want to avoid them.

I'm an ex-techie for Sage and used to do report design, I don't quite know what you are attempting to get from the AUDIT_HISTORY_SPLIT table, when I queried Demo data it was blank but that could just be the Demo data.

As an example for the AUDIT_HEADER table the SQL statement you'll want to use is;

SELECT AUDIT_HEADER.TRAN_NUMBER, AUDIT_HEADER.ITEM_COUNT, AUDIT_HEADER.TYPE, AUDIT_HEADER.DATE, AUDIT_HEADER.ACCOUNT_REF, AUDIT_HEADER.SALES_PURCHASE_REF, AUDIT_HEADER.BANK_CODE, AUDIT_HEADER.BANK_NAME, AUDIT_HEADER.INV_REF, AUDIT_HEADER.INV_REF_NUMERIC, AUDIT_HEADER.USER_NAME, AUDIT_HEADER.DETAILS, AUDIT_HEADER.DUE_DATE, AUDIT_HEADER.LAST_CHARGE_DATE, AUDIT_HEADER.FINANCE_CHARGE, AUDIT_HEADER.INTEREST_RATE, AUDIT_HEADER.ELECTRONIC_TRANS, AUDIT_HEADER.DISPUTED, AUDIT_HEADER.PAID_FLAG, AUDIT_HEADER.PAID_STATUS, AUDIT_HEADER.DELETED_FLAG, AUDIT_HEADER.DATE_FLAG, AUDIT_HEADER.DATE_BF, AUDIT_HEADER.DATE_FROM, AUDIT_HEADER.DATE_TO, AUDIT_HEADER.NET_AMOUNT, AUDIT_HEADER.TAX_AMOUNT, AUDIT_HEADER.GROSS_AMOUNT, AUDIT_HEADER.BANK_AMOUNT, AUDIT_HEADER.AMOUNT_PAID, AUDIT_HEADER.PAYMENT, AUDIT_HEADER.OUTSTANDING, AUDIT_HEADER.FOREIGN_OUTSTANDING, AUDIT_HEADER.FOREIGN_NET_AMOUNT, AUDIT_HEADER.FOREIGN_TAX_AMOUNT, AUDIT_HEADER.FOREIGN_GROSS_AMOUNT, AUDIT_HEADER.FOREIGN_AMOUNT_PAID, AUDIT_HEADER.FOREIGN_BANK_AMOUNT, AUDIT_HEADER.CURRENCY, AUDIT_HEADER.CURRENCY_TYPE, AUDIT_HEADER.EURO_GROSS, AUDIT_HEADER.EURO_RATE, AUDIT_HEADER.FOREIGN_RATE, AUDIT_HEADER.IS_DISCOUNT, AUDIT_HEADER.AGED_BALANCE, AUDIT_HEADER.AGED_FUTURE, AUDIT_HEADER.AGED_CURRENT, AUDIT_HEADER.AGED_30, AUDIT_HEADER.AGED_60, AUDIT_HEADER.AGED_90, AUDIT_HEADER.AGED_OLDER, AUDIT_HEADER.AGED_CUM_CURRENT, AUDIT_HEADER.AGED_CUM_30, AUDIT_HEADER.AGED_CUM_60, AUDIT_HEADER.AGED_CUM_90, AUDIT_HEADER.FOREIGN_AGED_BALANCE, AUDIT_HEADER.FOREIGN_AGED_FUTURE, AUDIT_HEADER.FOREIGN_AGED_CURRENT, AUDIT_HEADER.FOREIGN_AGED_30, AUDIT_HEADER.FOREIGN_AGED_60, AUDIT_HEADER.FOREIGN_AGED_90, AUDIT_HEADER.FOREIGN_AGED_OLDER, AUDIT_HEADER.FOREIGN_AGED_CUM_CURRENT, AUDIT_HEADER.FOREIGN_AGED_CUM_30, AUDIT_HEADER.FOREIGN_AGED_CUM_60, AUDIT_HEADER.FOREIGN_AGED_CUM_90, AUDIT_HEADER.BANK_FLAG, AUDIT_HEADER.HEADER_NUMBER, AUDIT_HEADER.DATE_ENTERED, AUDIT_HEADER.DATE_BANK_RECONCILED, AUDIT_HEADER.CIS_RECONCILED, AUDIT_HEADER.SPS_REF, AUDIT_HEADER.DEPOSIT_DATE, AUDIT_HEADER.DEPOSIT_FLAG, AUDIT_HEADER.RECURRING_ENTRY_ID, AUDIT_HEADER.ISP_REFERENCE, AUDIT_HEADER.OVERRIDDEN_CLOSED_LEDGER_DATE, AUDIT_HEADER.COUNTRY_CODE, AUDIT_HEADER.TAX_ID, AUDIT_HEADER.REVAL_TRANSACTION_FLAG, AUDIT_HEADER.POSTED_BY_DIRECT_DEBIT_SETTLEMENT, AUDIT_HEADER.DATE_AMENDED, AUDIT_HEADER.USER_NAME_AMENDED, AUDIT_HEADER.BANK_CHARGE_ACCOUNT, AUDIT_HEADER.BANK_CHARGE_IS_CUSTOMER, AUDIT_HEADER.IS_INVOICE_PAYMENT, AUDIT_HEADER.BANK_RECONCILIATION_REF, AUDIT_HEADER.BANK_RECONCILIATION_ID, AUDIT_HEADER.RECORD_DELETED
FROM AUDIT_HEADER AUDIT_HEADER
ORDER BY AUDIT_HEADER.TRAN_NUMBER

The best place to find the column names for each table would probably be the Sage Report Designer software. Open a report that does something similar to what you want to achieve in Power BI, then click Report and then Joins. The Join Editor will open and you can then see all the tables that make up that report, and column names within those tables. All you would need to do is write your SQL statement to bring in the columns you want. You may also want to use the same joins in Power BI as well if you're trying to recreate the same report.