How to Use Power Query in Excel for Mac: Import and Shape Data

Excel for Mac incorporates Power Query (also known as Get & Transform) technology to provide robust capabilities for data integration. With Power Query, macOS users can seamlessly import, refresh, and authenticate various data sources, manage data connections, clear credentials, and shape data into tailored tables that fit specific analytical requirements. Beyond the standard user interface, advanced users can also automate and create Power Query queries on Mac by leveraging Visual Basic for Applications (VBA).

Importing Data into Excel for Mac Using Power Query

Mac users can connect to and extract data from a wide variety of localized and cloud-based data sources. Supported formats include Excel Workbooks, Text/CSV, XML, JSON, SQL Server Databases, SharePoint Online Lists, OData feeds, Blank Tables, and Blank Queries.

Note: Importing from a SQL Server Database data source natively via Power Query is fully supported for Microsoft 365 subscribers running Version 16.69 (23010700) or later. SQL Server Database direct import options may vary based on your Office insider deployment channel.

Step-by-Step Data Import Process

1. Open your workbook, navigate to the top ribbon, and select Data > Get Data.
2. To launch the primary setup interface, click Get Data (Power Query).
3. In the Choose data source dialog box, select your desired data source from the grid of available connectors.
4. Establish a secure connection to your chosen data source by following the prompt instructions.
5. Preview and select the specific tables, sheets, or data subsets you wish to pull into your workspace.
6. Click the Load button to finalize the process.

Once loaded, the newly structured dataset will automatically populate a brand-new worksheet within your active Excel workbook.

Shaping and Transforming Data with the Power Query Editor

Data is rarely perfectly structured upon extraction. To clean, pivot, filter, or transform your information before committing it to a sheet, you can deploy the built-in Power Query Editor.

Launching and Using the Editor

1. Navigate to the ribbon and select Data > Get Data (Power Query).
2. Click Launch Power Query Editor from the dropdown options.
   (Tip: You can also access this editor directly during the initial configuration phase by clicking Next or Transform Data after selecting your source).
3. Use the contextual tools within the Query Editor window to clean, rename columns, modify data types, and shape your queries exactly as you would in Excel for Windows.
4. Once your data shaping sequence is complete, navigate to the Home tab and click Close & Load.

The cleaned dataset will render perfectly formatted into a standard Excel Table on a fresh worksheet.

Refreshing Data Sources and Managing Paths

Power Query allows you to establish dynamic connections that update as the underlying source data evolves. You can easily refresh connections pointing to SharePoint files, lists, or folders, OData feeds, text/CSV files, local tables, XML/JSON sheets, and SQL Server instances.

Correcting File Paths on the First Refresh

When executing a query for the first time on file-based data sources, Excel may require you to explicitly point to the local or network file paths.

1. Go to Data, click the arrow next to Get Data, and select Data Source Settings.
2. Highlight the active connection from the list and select Change File Path.
3. In the file navigation dialog, specify the updated location of your file, click Get Data, and then choose Close.

Subsequent Query Refreshes

To execute ongoing updates across your workbook tables, choose one of the following methods:

• Workbook-wide Refresh: Select Data > Refresh All to update every query, PivotTable, and web connection simultaneously.
• Targeted Table Refresh: Right-click anywhere within your specific query table on the worksheet and select Refresh.
• PivotTable Refresh: Select any cell inside your target PivotTable, navigate to the PivotTable Analyze tab on the ribbon, and choose Refresh Data.

Managing Privacy, Authentication, and Credentials

Connecting to enterprise data hubs like SharePoint or SQL Server requires proper authorization. Power Query safely caches these credentials, but you may occasionally need to supply new accounts or wipe old authentication tokens.

Supplying Source Credentials

When triggering a fresh data load or a first-time refresh on a restricted directory, an Enter credentials dialog box will launch. Select your desired authentication protocol (such as Anonymous, Windows, Username/Password, Kerberos, or NTLM) and type your security credentials to authorize the pipeline.

Clearing Saved Permissions

If password changes or permission shifts occur, clear old access data using these steps:

1. Navigate to Data > Get Data > Data Source Settings.
2. Identify and click the specific connection you intend to modify from the connection list.
3. At the bottom of the interface window, click Clear Permissions.
4. Confirm the prompt action by selecting Delete.

Automating Power Query with VBA Code

While native graphical query composition tools are continually expanding on macOS, advanced developers can use VBA modules to write, edit, and control Power Query definitions directly on Mac. Transferring operational VBA scripts from Windows to Mac involves a straightforward transition process.

Step 1: Exporting from Excel for Windows

1. Open your workbook in Windows and tap ALT + F11 to open the Visual Basic Editor.
2. Locate the code module handling your Queries or WorkbookQuery objects.
3. Right-click the module file and select Export File.
4. Save the file locally using a standard .bas extension.
5. Upload this .bas script to a accessible cloud directory, such as Microsoft OneDrive, to transfer it over to your Mac system.

Step 2: Importing to Excel for Mac

1. Download the .bas file to a local directory on your Mac.
2. Open Excel for Mac, then head to the top menu bar and select Tools > Macro > Visual Basic Editor.
3. Right-click an existing object or folder inside your Mac Project window and select Import File.
4. Locate your transferred .bas module file and click Open to integrate it.

Practical VBA Sample Query Script

This sample script builds a fundamental query named SampleList that automatically generates an array containing indexed rows from 1 to 100 directly into an active worksheet layout:

Sub CreateSampleList()
    ActiveWorkbook.Queries.Add Name:="SampleList", Formula:= _
        "let" & vbCr & vbLf & _
        "Source = {1..100}," & vbCr & vbLf & _
        "ConvertedToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)," & vbCr & vbLf & _
        "RenamedColumns = Table.RenameColumns(ConvertedToTable,{{""Column1"", ""ListValues""}})" & vbCr & vbLf & _
        "in" & vbCr & vbLf & _
        "RenamedColumns"
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=SampleList;Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [SampleList]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "SampleList"
        .Refresh BackgroundQuery:=False
    End With
End Sub

Legacy Data Connections and Alternate Drivers

For data pipelines utilizing flat text files or specialized databases outside the direct Power Query ecosystem, Excel for Mac offers robust alternate connection wizards.

Importing via the Text Import Wizard (Legacy)

1. Open your workbook. If prompted with an external connection warning, click Enable Content.
2. If the Grant File Access dialog appears, click Select and authorize the parent directory.
3. Navigate to Data > From Text (Legacy) to open the Mac Finder window.
4. Choose your .txt or .csv asset and hit Open to trigger the Text Import Wizard.
5. Configure your settings across the three procedural steps:

• Page 1: Select data structure traits (Delimited or Fixed width), choose a starting row index, and match the text asset’s native File origin character encoding.
• Page 2: Adjust delimiter flags (Tab, Semicolon, Comma, Space) or configure Fixed Width column break lines. Set your text qualifier properties as needed.
• Page 3: Assign specific data formats (Date, Text, Advanced Numeric) column-by-column, then click Finish.

6. Identify your targeted array location inside the Import Data box and confirm by clicking OK.

Querying Non-SQL Databases via External ODBC Drivers

If you are connecting to alternative enterprise engines like FileMaker Pro, you can use a compatible Open Database Connectivity (ODBC) driver tailored for macOS.

1. Ensure the specialized driver is installed on your operating system.
2. Navigate to Data > From Database (Microsoft Query).
3. Choose your configured database source name from the options panel and select OK.
4. Enter your connection credentials at the server login prompt.
5. In the left navigation pane, expand the server directories, choose your target tables, and execute a preview using the Run button.
6. Once validated, select Return Data, assign your final location in the Import Data menu, and select OK to load.

References

• Microsoft Support: Power Query for Excel Help Documentation.
• Microsoft Developer Network: Excel VBA Reference Guides for Queries, WorkbookQuery, and Workbook Object Models.
• Apple macOS Compatibility Matrix: Open Database Connectivity (ODBC) Driver Specifications for External Applications.