Applies ToExcel for Microsoft 365 for Mac

Excel for the Mac incorporates Power Query (also called Get & Transform) technology to provide greater capability when importing, refreshing, and authenticating data sources, managing Power Query data sources, clearing credentials, changing the location of file-based data sources, and shaping the data into a table that fits your requirements. You can also create a Power Query query by using VBA. 

Note: SQL Server Database data source can only be imported in Insiders Beta.

You can import data into Excel using Power Query from a wide variety of data sources: Excel Workbook, Text/CSV, XML, JSON, SQL Server Database, SharePoint Online List, OData, Blank Table, and Blank Query.

  1. Select Data > Get Data.PQ Mac Get Data (Power Query).png

  2. To select the desired data source, select Get Data (Power Query).

  3. In the Choose data source dialog box, select one of the available data sources.  Example of data sources to select in the dialog box

  4. Connect to the data source. To learn more about how to connect to each data source, see Import data from data sources.

  5. Choose the data you wish to import.

  6. Load the data by clicking on the Load button.

Result

The imported data appears in a new sheet.

Typical results for a query

Next steps

To shape and transform data by using the Power Query Editor, select Transform Data. For more information, see Shape data with Power Query Editor.

Note: This feature is generally available to Microsoft 365 subscribers, running Version 16.69 (23010700) or later of Excel for Mac. If you are a Microsoft 365 subscriber, make sure you have the latest version of Office.

Procedure

  1. Select Data > Get Data (Power Query).

  2. To open the Query Editor, select Launch Power Query Editor.PQ Mac Editor.png

    Tip: You can also access the Query Editor by selecting Get Data (Power Query), choosing a data source, and then clicking Next.

  3. Shape and transform your data by using the Query Editor as you would in Excel for Windows.The Power Query Editor For more information, see Power Query for Excel Help.

  4. When you’re done, Select Home > Close & Load.

Result

The newly imported data appears in a new sheet.

Typical results for a query

You can refresh the following data sources: SharePoint files, SharePoint lists, SharePoint folders, OData, text/CSV files, Excel workbooks (.xlsx), XML and JSON files, local tables and ranges, and a Microsoft SQL Server database.

Refresh the first time

The first time you try to refresh file-based data sources in your workbook queries, you may need to update the file path.

  1. Select Data, the arrow next to Get Data, and then Data Source Settings. The Data source settings dialog box appears.

  2. Select a connection, and then select Change File Path.

  3. In the File path dialog box, select a new location, and then select Get Data.

  4. Select Close.

Refresh subsequent times

To refresh:

  • All data sources in the workbook, select Data > Refresh All.

  • A specific data source, right click a query table on a sheet, and then select Refresh.

  • A PivotTable, select a cell in the PivotTable, and then select PivotTable AnalyzeRefresh Data.

The first time you access SharePoint, SQL Server, OData or other data sources that require permission, you must provide appropriate credentials. You may also want to clear the credentials to enter new ones.

Enter credentials

When you refresh a query for the first time, you may be asked to login. Select the authentication method and specify the login credentials to connect to the data source and continue with the refresh.

If login is required, the Enter credentials dialog box appears.

For example:

  • SharePoint credentials:SharePoint credentials prompt on Mac

  • SQL Server credentials:The SQL Server dialog box to enter server, database, and credentials

Clear credentials

  1. Select Data > Get Data > Data Source Settings.

  2. In the Data Source Settings dialog box, select the connection you want.

  3. At the bottom, select Clear Permissions.

  4. Confirm this is what you want to do, and then select Delete.

Although authoring in the Power Query Editor is not available in Excel for Mac, VBA does support Power Query authoring. Transferring a VBA code module in a file from Excel for Windows to Excel for Mac is a two-step process. A sample program is provided for you at the end of this section.

Step one: Excel for Windows

  1. On Excel Windows, develop queries by using VBA. VBA code that uses the following entities in the Excel's object model also work in Excel for Mac: Queries object, WorkbookQuery object, Workbook.Queries Property. For more information, see Excel VBA reference.

  2. In Excel, make sure the Visual Basic Editor is open by pressing ALT+F11.

  3. Right-click the module, and then select Export File. The Export dialog box appears.

  4. Enter a filename, make sure the file extension is .bas, and then select Save.

  5. Upload the VBA file to an online service to make the file accessible from the Mac. You can use Microsoft OneDrive. For more information, see Sync files with OneDrive on Mac OS X.

Step two: Excel for Mac

  1. Download the VBA file to a local file, the VBA file you saved in "Step one: Excel for Windows" and uploaded to an online service.

  2. In Excel for Mac, select Tools > Macro > Visual Basic Editor. The Visual Basic Editor window appears.

  3. Right-click on an object in the Project window, and then select Import File. The Import File dialog box appears.

  4. Locate the VBA file, and then select Open.

Sample code

Here is some basic code you can adapt and use. This is a sample query that creates a list with values from 1 to 100.

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

See Also

Power Query for Excel Help

ODBC drivers that are compatible with Excel for Mac

Create a PivotTable to analyze worksheet data

Need more help?

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.