Use Power Query in Excel for Mac

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

Refresh Power Query queries

Currently, you can refresh queries that use local .TXT, .CSV, .XLSX, .XML or .JSON files as data sources. Network sources such as SQL Server are not supported yet.

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

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.

Important: The following sample subroutine refer to cells that contain strings that get incorporated into various elements of the code when it runs. To adapt them, you can either add your own query info to a worksheet in the specified cells, or change the cell references to match those containing your query info.

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

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.

×