Manage Data Preview (Power Query)

You can control how Preview data downloads whether directly or in the background.

1. Do one of the following:

   • In Excel, select Data > Get Data > Query Options.

   • In Power Query Editor, select File > Option Settings > Query Options.

2. In the left pane, under GLOBAL, select Data Load, and then under Background Data, select one of the following options:

   • Always allow data previews to download in the background

   • Allow data previews to download in the background according to each file's setting

   • Never allow data previews to download in the background

3. In the left pane, under CURRENT WORKBOOK, select Data Load, and then under Background Data, select or clear Allow data previews to download in the background.

You can view data in a monospaced font or reveal special characters, such as newlines.

Set options for the current Preview Data

1. In Power Query Editor, select View, and then select or clear one of the following in the Data Preview group:

   • Monospaced    Display preview contents using a monospaced font.

   • Show whitespace    Show whitespace and newline characters.

Set options for all your workbooks

1. Select File > Options and Settings > Query Options > Power Query Editor > Data Preview.

2. In the left pane, under GLOBAL, select Power Query Editor.

3. In the right pane, under Data Preview, select or clear the following:

   • Display preview contents using a monospaced font

   • Show whitespace and newline characters

There are times when you want to see the entire dataset. For example, you need a count of all rows. But you’ll get better performance if you select just the first 1000 rows, especially if the dataset is quite large.

1. To open a query, locate one previously loaded from the Power Query Editor, select a cell in the data, and then select Query > Edit. For more information see Create, load, or edit a query in Excel.

2. At the bottom right hand corner of Data Preview, select one of the commands to the right of the columns and row count:

   • Column profiling based on top 1000 rows

   • Column profiling based on entire dataset