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.
-
Select Data > Get Data.
-
To select the desired data source, select Get Data (Power Query).
-
In the Choose data source dialog box, select one of the available data sources.
-
Connect to the data source. To learn more about how to connect to each data source, see Import data from data sources.
-
Choose the data you wish to import.
-
Load the data by clicking on the Load button.
Result
The imported data appears in a new sheet.
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
-
Select Data > Get Data (Power Query).
-
To open the Query Editor, select Launch Power Query Editor.
Tip: You can also access the Query Editor by selecting Get Data (Power Query), choosing a data source, and then clicking Next.
-
Shape and transform your data by using the Query Editor as you would in Excel for Windows.
-
When you’re done, Select Home > Close & Load.
Result
The newly imported data appears in a new sheet.
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.
-
Select Data, the arrow next to Get Data, and then Data Source Settings. The Data source settings dialog box appears.
-
Select a connection, and then select Change File Path.
-
In the File path dialog box, select a new location, and then select Get Data.
-
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 Analyze > Refresh 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:
-
SQL Server credentials:
Clear credentials
-
Select Data > Get Data > Data Source Settings.
-
In the Data Source Settings dialog box, select the connection you want.
-
At the bottom, select Clear Permissions.
-
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
-
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.
-
In Excel, make sure the Visual Basic Editor is open by pressing ALT+F11.
-
Right-click the module, and then select Export File. The Export dialog box appears.
-
Enter a filename, make sure the file extension is .bas, and then select Save.
-
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
-
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.
-
In Excel for Mac, select Tools > Macro > Visual Basic Editor. The Visual Basic Editor window appears.
-
Right-click on an object in the Project window, and then select Import File. The Import File dialog box appears.
-
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-
Open the Excel workbook.
-
If you get a security warning about external data connections being disabled, select Enable Content.
-
If the Grant File Access dialog box appears, select Select, and then select Grant Access to the top-level folder containing the data source files.
-
Select Data > From Text (Legacy). The Finder dialog box appears.
-
Locate the .txt or .csv file, and the select Open. The Text Import Wizard appears.Tip Repeatedly check the Preview of selected data pane to confirm your choices.
-
In the first page, do the following:
File Type To choose the type of text file, select Delimited or Fixed width.Row Number In Start import at row, select a row number to specify the first row of data that you want to import.Character Set In File origin, select the character set that is used in the text file. In most cases, you can leave this setting at its default.
-
In the second page, do the following:Delimited If you chose Delimited on the first page, under Delimiters, select the delimiter character or use the Other check box to enter one not listed. Select Treat consecutive delimiters as one if your data contains a delimiter of more than one character between data fields or if your data contains multiple custom delimiters. In Text qualifier, select the character that encloses values in your text file, which is most often the quote (") character.
Fixed width If you chose Fixed width on the first page, follow the instructions to create, delete, or move a break line in the Preview of selected data box.
-
In the third page, do the following: For each column under Preview of selected data, select it, and then change it to a different column format if you want. You can further set the date format and select Advanced to change numeric data settings. You can also convert the data after you import it. Select Finish. The Import Data dialog box appears.
-
Choose where you want the data to be added: either on the existing sheet, on a new sheet, or in a PivotTable.
-
Select OK.
To ensure the connection is working, enter some data, and then select Connections > Refresh.
-
Select Data > From SQL Server ODBC. The Connect to SQL Server ODBC Data Source dialog box appears.
-
Enter the server in the Server Name box, and optionally, enter the database in the Database Name box. Get this information from the database administrator.
-
Under Authentication, select a method from the list: Username/Password, Kerberos, or NTLM.
-
Enter credentials in the User Name and Password boxes.
-
Select Connect. The Navigator dialog box appears.
-
In the left pane, navigate to the table you want, and then select it.
-
Confirm the SQL statement on the right pane. You can change the SQL statement as you see fit.
-
To preview the data, select Run.
-
When you're ready, select Return Data. The Import Data dialog box appears.
-
Choose where you want the data to be added: either on the existing sheet, on a new sheet, or in a PivotTable.
-
To set connection properties on the Usage and Definition tabs of the Properties dialog box, select Properties. After you import the data, you can also select Data > Connections, and then in the Connection Properties dialog box, select Properties.
-
Select OK.
-
To ensure the connection is working, enter some data, and then select Data > Refresh All.
If you want to use an external source that is not a SQL Database (for example, FileMaker Pro), you can use an Open Database Connectivity (ODBC) driver installed on your Mac. Information on drivers is available on this web page. Once the driver for your data source is installed, follow these steps:
-
Select Data > From Database (Microsoft Query).
-
Add the data source for your database, and then select OK.
-
At the SQL Server credentials prompt, enter the authentication method, the username and the password.
-
On the left, select the arrow next to the server to see the databases.
-
Select the arrow next to the database you want.
-
Select the table you want.
-
To preview the data, select Run.
-
When you're ready, select Return Data.
-
In the Import Data dialog box, choose where you want the data to be located: either on the existing sheet, on a new sheet, or in a PivotTable.
-
Select OK.
-
To ensure the connection is working, enter some data, and then select Data > Refresh All.
If your permissions are not working, you may not to clear them first, and then log in.
-
Select Data > Connections. The Workbook Connections dialog box appears.
-
Select the connection you want in the list, and then select Clear Permissions.
