Use Power Query in Excel for Mac

Excel for Mac now supports Power Query refresh for many data sources, as well as query creation through VBA. Authoring in the Power Query Editor is not supported yet.

Refresh Power Query queries

You can refresh queries from the following data sources:

  • Local .TXT, .CSV, .XLSX, .XML or .JSON files

  • Tables and ranges in the current workbook

  • SQL Server with database authentication 

  • SharePoint files, folders, and lists* 

  • OData feeds*

Note: *Refresh from SharePoint files, folders, and lists, and from OData feeds, is currently in beta, and currently only available to Office Insiders.

There's no special command needed - just click Data > Refresh All, or use whatever other external data refresh method you prefer.

Resfresh All command on the ribbon in Excel for Mac

Credentials prompt 

When you refresh a query for the first time, you may be asked to login. Choose the authentication method and specify the login credentials to connect to the data source and continue with the refresh. The credentials prompt you see depends on the data source. 

For example, here's the SQL Server credentials prompt: 

alternate text

And here's the SharePoint prompt:

SharePoint credentials prompt on Mac

Clear your login credentials 

If you want to clear the existing login credentials, select Data > Connections, choose the desired connection, and then select Clear Permissions

Removing saved credentials for a data connection on Mac

Use VBA to author Power Query queries

Although authoring in the Power Query Editor is not yet available in Excel for Mac, VBA does support Power Query authoring. Here is some basic code you can adapt and use.

Create a query and load its data

Here is a simple sample that adds a 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

M query language

Queries object

WorkbookQuery object

Need more help?

Expand your Office skills
Explore training
Get new features first
Join Office Insiders

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×