Connecting to Azure SQL Server and Azure Data Warehouse using Power Query on Mac OS

When working with large datasets, it's often necessary to connect to external data sources such as Azure SQL Server and Azure Data Warehouse. In this article, we'll walk you through the process of connecting to these databases using Power Query on a Mac OS machine.

Authentication Requirements

To authenticate to Azure SQL Server or Azure Data Warehouse, you'll need to provide the following connection properties:

* User: The name of the user authenticating to Azure. This can be found by logging into the Azure portal and navigating to 'SQL databases' (or 'SQL data warehouses') -> 'Select your database' -> 'Overview' -> 'Server name.'

* Password: The password associated with the authenticating user.

* Database: The name of the SQL Server database.

Connecting using Power Query

To connect to Azure SQL Server or Azure Data Warehouse using Power Query, you'll need to follow these steps:

1. Open Power Query and click on 'Get Data' -> 'Other Sources' -> 'ODBC'.

2. In the ODBC dialog box, select 'SQL Server' as the database type.

3. Enter your connection details, including the User, Password, Database, and Server names.

Alternatively, you can also use the CData ODBC Driver for SQL Server to connect to Azure SQL Server or Azure Data Warehouse. This driver is preconfigured for the iODBC driver manager and makes it easy to use with tools like Power Query.

Configuring the ODBC Driver

To configure the ODBC driver on your Mac OS machine, you'll need to modify the system-wide settings in the `odbc.ini` file. You can do this by opening a terminal and running the command `sudo nano /Library/ODBC/odbc.ini`. This will open the file in a text editor.

Activating the Trial License

If you're using the CData ODBC Driver for SQL Server, you may need to activate a trial license. To do this, omit the key input and run the following command in a terminal: `/Applications/CData ODBC Driver for SQL/bin`.

Once you've activated the trial license, you can use the driver with Power Query.

In this article, we've walked through the process of connecting to Azure SQL Server and Azure Data Warehouse using Power Query on a Mac OS machine. By following these steps, you should be able to easily connect to your databases and start working with your data in Microsoft Excel.

Power query mac os