Use Power Query in Excel for Mac

1. Open the Excel workbook.

2. If you get a security warning about external data connections being disabled, select Enable Content.

3. 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.

4. Select Data > Get External Data > From Text. The Finder dialog box appears.

5. 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.

6. 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.

7. 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.

8. 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.

9. Choose where you want the data to be added: either on the existing sheet, on a new sheet, or in a PivotTable.

10. Select OK.

    To ensure the connection is working, enter some data, and then select Connections > Refresh.

Some data sources require you to enter credentials. Occasionally, you may need to clear your credentials to fix any issues.

Entering 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. The credentials prompt you see depends on the data source. For example:

SharePoint credentials prompt:

SQL Server credentials prompt:

Clearing your credentials

If your permissions are not working, you may need to clear them first, and then log in.

1. Select Data > Connections. The Workbook Connections dialog box appears.

2. Select the connection you want in the list, and then select Clear Permissions.
   
   

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

REVIEWERS: I could not get this to work. Should it? If not, I can remove it.

If you already have Excel files and data sources on Windows or want to create them on Windows first, here's what you can do.

1. Using Excel for Windows, create a query in the Power Query Editor using a supported file data source on Excel for Mac. For more information, see Power Query for Excel Help.

2. Move the data source file and the Excel workbook containing the query (.xlsx) from your Windows desktop computer to the Mac. You can use Microsoft OneDrive. For more information, see Sync files with OneDrive on Mac OS X.

3. On the Mac, open Excel for Mac and then open the Excel workbook containing the query (.xlsx) and previously connected to the data source file.

4. If you get a security warning about external data connections being disabled, select Enable Content.

5. 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.

6. Select Data > Refresh All.
   
   The first time you do this, you will probably get an error. That's because you need to update the location of the data source file path so that the refresh operation works on your Mac. Here are the differences between file paths on each operating system:

   • Mac file path     /Users/USERNAME/Desktop/data.csv

   • Windows file path    C:\Users\USERNAME\Desktop\data.csv

7. To open the Workbook Connections dialog box, select Data > Connections.

8. Select the connection, select Change File Path, and then enter the correct file path for a Mac.
   

9. To ensure the connection is working, enter some data in the data source, and then select Data > Refresh All.