You can manage several aspects of Data Preview in the Power Query Editor by setting different options. Several of these options have performance implications that are helpful to know.   

Sample data used to explain the Merge command

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

See Also

Power Query for Excel Help

Need more help?

Expand your skills
Explore Training
Get new features first
Join Microsoft Insiders

Was this information helpful?

What affected your experience?

Any additional feedback? (Optional)

Thank you for your feedback!

×