Sage 100

Any tips on linking Excel Power Query or Power BI to SOTAMAS odbc DSN? I've been using Power BI on a SQL based ERP for a while, and just ""assumed"" that connecting via the SOTAMAS odbc would be straight forward. And we all know how that usually goes...

Yes, we use Power Query with SOTAMAS and it works fairly straight forward.

Hmm, so you just log in with the SOTAMAS dsn and it works? When I tried, it did the infinite circle for 15 minutes before I killed it. I was assuming it needed some manual connection string information. There I go ""assuming"" again...

I tried it again, and after 5 minutes it came up with the Sage ODBC login. I entered it, and then it displayed the login prompt 3 more times before displaying the list of tables. Anyone know why it would prompt me multiple times for the sage login?

Once I got to the Power Query screen to select a table, I chose CI_Item. And had to log onto the Sage login 8 times before it finally showed a preview of the data. I ""assume"" this is not the same experience for everyone else. Any sage advice out there?

Try OLE (DB) ADO if it's SQL.

With our Power Query files, we pull the data (via SOTAMAS ODBC) into the Excel sheet, then use the data on the sheet in the Query. It's not a direct into the Query data pull.

I prefer working in SQL, so I'll create a silent ODBC login to Sage 100 and then create a Linked Server in SQL Server that points to it. If you need a step-by-step, just let me know.

My work around for now is to use the old MS Query to pull the SOTAMAS data into excel sheets, then use power query to do the ETL and load back to a sheet. I'm working on a client's Excel 2010, so that might be part of the issue.