Excel for Mac Power Query: Comprehensive Guide for Data Analysis

Rick Akura on
Learn how to leverage the power of Power Query Editor in Excel for Mac to perform efficient data analysis and transformations. Master data cleansing, merging, and more.
How to use Excel for Mac Power Query

While the journey to feature parity with the Windows version has taken time, Excel for Mac has been steadily improving its capabilities. From the introduction of data refreshing two years ago to data importing last year, each update has brought us closer to a comprehensive Power Query solution for Mac users.

Join us as we explore the latest milestone in Excel for Mac's Power Query journey. Learn how to leverage the Query Editor's powerful data cleaning and shaping capabilities, empowering you to unlock the full potential of data analysis in Excel for Mac.

Table of Contents

  1. Data Import and Transformation Made Easy
  2. Shape Data with Power Query Editor in Excel for Mac
  3. Refreshing Data Sources in Excel for Mac
  4. Refresh the First Time
  5. Refresh Subsequent Times
  6. Entering and Clearing Credentials in Excel for Mac
  7. Entering Credentials
  8. Clearing Credentials
  9. Transferring Code Modules
  10. Import the VBA file in Excel for Mac
  11. FAQs
  12. Final Thoughts

Data Import and Transformation Made Easy

To access Power Query in Excel for Mac, follow these steps:

  1. On the Data tab in the Excel ribbon, click the "Get Data (Power Query)" button. This button allows you to import and transform data using Power Query.
  2. Click "Launch Power Query Editor" to open the Query Editor. This powerful tool provides a user-friendly interface for shaping and transforming your data.
  3. In the Query Editor, you can perform various data transformations similar to Excel for Windows. This includes cleaning, filtering, merging, and more. Use the available options and functions to manipulate your data as needed.
  4. Once you've finished shaping your data, click the "Close & Load" button on the Home tab of the Query Editor. This will load the transformed data into a new sheet in Excel.
  5. The imported data will now appear in a new sheet within your Excel workbook, ready for further analysis and visualization.

Power Query in Excel for Mac supports a range of data sources, including:

  • Text, CSV, XLSX, XML, and JSON files: You can import data from these file formats directly into Power Query.
  • SharePoint: You can connect to SharePoint sites, lists, folders, and retrieve data using Power Query.
  • Local tables and ranges: Excel tables and ranges in your workbook can be used as a data source.
  • Microsoft SQL Server: Power Query enables you to connect to SQL Server databases and retrieve data.

Additionally, you can access the Query Editor from the data import flow by clicking the "Get Data (Power Query)" button, selecting a data source, and proceeding with the import process.

Shape Data with Power Query Editor in Excel for Mac

To shape and transform your data using Power Query Editor in Excel for Mac, follow these steps:

  1. Ensure you have the required version: This feature is available to Microsoft 365 subscribers running Version 16.69 (23010700) or later of Excel for Mac. Make sure you have the latest version of Office installed.
  2. Access Power Query: Go to the Data tab in the Excel ribbon and select "Get Data (Power Query)."
  3. Launch Query Editor: Choose "Launch Power Query Editor" to open the Query Editor interface. Alternatively, you can access the Query Editor directly by selecting "Get Data (Power Query)," choosing a data source, and clicking "Next."
  4. Shape and transform data: Use the Query Editor to apply various transformations to your data, such as cleaning, filtering, merging, and more. Leverage the available options and functions to manipulate your data according to your requirements.
  5. Review Power Query Help: If you need more information or guidance on using Power Query in Excel, refer to the Power Query for Excel Help documentation.
  6. Close and load: Once you have finished shaping your data, select the "Close & Load" option on the Home tab to load the transformed data into Excel.

Refreshing Data Sources in Excel for Mac

To keep your data up to date in Excel for Mac, you can refresh various data sources using the following steps:

Refresh the First Time

  1. Access Data Source Settings: Select the "Data" tab in the Excel ribbon, click the arrow next to "Get Data," and then choose "Data Source Settings." This will open the Data source settings dialog box.
  2. Update File Path: In the Data source settings dialog box, select the desired connection, and then click "Change File Path."
  3. Specify New Location: In the File path dialog box, choose a new location for the file, and then click "Get Data."
  4. Close the dialog box: After updating the file path, click "Close" to exit the Data source settings dialog box.

Refresh Subsequent Times

  1. Refresh All Data Sources: To refresh all data sources in your workbook, select "Data" and then choose "Refresh All." This will update all the data connections in your workbook.
  2. Refresh a Specific Data Source: If you want to refresh a specific data source or query table on a sheet, right-click the query table, and then select "Refresh" from the context menu.
  3. Refresh a PivotTable: If you have a PivotTable in your workbook, select any cell within the PivotTable, go to the "PivotTable Analyze" tab, and click "Refresh Data" to update the PivotTable with the latest data.

Entering and Clearing Credentials in Excel for Mac

To access data sources such as SharePoint, SQL Server, or OData in Excel for Mac, you may need to provide credentials for authentication. Here's how to enter and clear credentials:

Entering Credentials

  1. Refresh Query: When you refresh a query for the first time that requires authentication, you will be prompted to log in. Select the appropriate authentication method and enter the login credentials to establish a connection with the data source and proceed with the refresh.
  2. Enter Credentials Dialog Box: Depending on the data source, you will encounter an "Enter credentials" dialog box where you can provide the required login information. This dialog box will appear when authentication is necessary.

Clearing Credentials

  1. Access Data Source Settings: Select the "Data" tab in the Excel ribbon and click on "Get Data" followed by "Data Source Settings."
  2. Choose the Connection: In the Data Source Settings dialog box, select the connection for which you want to clear the credentials.
  3. Clear Permissions: At the bottom of the dialog box, select "Clear Permissions." This will remove the stored credentials for the selected connection.
  4. Confirm and Delete: Confirm that you want to clear the credentials by selecting "Delete."

Transferring Code Modules

Power Query authoring within the Power Query Editor is currently not available in Excel for Mac. However, you can still use VBA (Visual Basic for Applications) to support Power Query authoring in Excel for Mac. 

Import the VBA file in Excel for Mac

  1. Open Excel for Mac and select "Tools" from the menu.
  2. Choose "Macro" and then click on "Visual Basic Editor." This will open the Visual Basic Editor window.
  3. In the Project window of the Visual Basic Editor, right-click on an object (such as a workbook or module).
  4. Select "Import File" from the context menu. This will open the Import File dialog box.
  5. Locate the VBA file you downloaded in step one and select "Open."

FAQs

How do I add a Power Query to the ribbon in Excel for Mac?

To add Power Query to the ribbon in Excel for Mac, go to "Excel" on the menu bar, select "Preferences," choose "Ribbon & Toolbar," and then enable the "Power Query" checkbox.

How do I use the Power Query Editor in Excel?

To use the Power Query Editor in Excel, click on the "Data" tab in the ribbon, select "Get Data," and choose the data source. Then, use the Power Query Editor interface to transform and shape your data.

Why doesn't my Mac Excel have Power Query?

Power Query was introduced in later versions of Excel for Mac, so if you have an older version, it may not have this feature. Update your Excel to the latest version to access Power Query.

How do I enable Power Query editor on Mac?

Ensure you have the latest version of Excel for Mac installed, as earlier versions may not include Power Query. If you have the latest version, Power Query should be available under the "Data" tab in the ribbon.

Is Power Query compatible with Mac?

Yes, Power Query is compatible with Excel for Mac. However, it was introduced in later versions, so make sure you have an updated version of Excel for Mac to access Power Query.

Final Thoughts

In conclusion, the Power Query Editor in Excel for Mac provides Mac users with powerful data transformation and analysis capabilities. Although it took some time for the features to catch up with their Windows counterparts, Excel for Mac has made significant progress in incorporating Power Query functionality. 

With the ability to refresh data, import various data sources, and shape data using the Query Editor, Mac users can now enjoy a more comprehensive data analysis experience. The supported data sources, such as SharePoint, SQL Server, and file formats like CSV and XML, offer flexibility in accessing and transforming data. 

One more thing

If you have a second, please share this article on your socials; someone else may benefit too. 

Subscribe to our newsletter and be the first to read our future articles, reviews, and blog post right in your email inbox. We also offer deals, promotions, and updates on our products and share them via email. You won’t miss one.

Related articles 

» Set Excel as the Default Application on Mac | Excel for Mac
» Installing Microsoft Excel for Mac: Step-by-Step Guide
» Choosing the Right Operating System: Windows, macOS, or Linux?

Tags: Product guides