The data profiling tools provide intuitive ways to clean, transform, and understand query data, such as key statistics and distributions. In addition, by using the Count Rows command, you can also get a row count of all your query data.

By default, Power Query profiles data over the first 1,000 rows. To change the profile to operate over the entire dataset, in the lower-left corner of your editor, select either Column profiling based on to 1000 rows or Column profiling based on entire data set.   

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

  3. In the Data Preview group, select one or more of the elements you want to display:

Data Profiling options on the View tab of the Power Query Editor ribbon

What follows is more detail about available statistics and additional steps you can do. 

Quick commands

To further clean the data as you view the profiles, the following commands are available when you click More (…) at the bottom right of the various dialog boxes: Keep Duplicates, Keep Errors, Remove Duplicates, Remove Empty, Remove Errors, and Replace Errors. For more information, see Keep or remove duplicate rows, Remove or keep rows with errors, and Replace values.

Column Quality   

Displays data profiles indicating key percentages in a bar chart of three categories: Valid (green), Error (red), Empty (dark grey) for each column.

The three quality values

Hover over the display to see a summary enlargement. Select More () for additional commands to perform.

The quality values pop-up

Tip    If you haven't yet set the options in the Data Preview group in the ribbon, you can quickly see column quality profile data, by right clicking on the thin dark gray bar, and then selecting Show the Quality Column Peak. You can also quickly copy the data values.

Column Distribution   

Displays a visualization of frequency and distribution under each column, and sorted in descending order of the value with the highest frequency.

The distribution charts

Hover over the bulleted items to see a summary enlargement. Select More () for additional commands to perform.

The distribution chart pop-up

Column Profile: Column statistics and value distribution   

Displays a more thorough view of each column and the statistics associated to it.

The Column statistics and Value distribution views

Tip    On the far right, select More () to copy the data.

In addition, you can do the following:

  • Hover over each bar in the graph to see more detail. To filter that bar, select Equals or Does Not Equal. For additional commands, select More (...) or right click on the bar.

    A value distribution bar chart pop-up

  • On the far right, select More (), select Group by, and then select additional commands, which vary depending on the data type.

    An example of additional Group By commands

    You can continue to interact with individual values in the chart.

Often, you want to see the number of rows returned after you get data. The Count Rows command returns the actual row count of the data and is not affected by the Column Profile setting.

  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, edit, and load a query in Excel (Power Query).

  2. Select Transform> Count Rows.

  3. Optionally, select the Transform tab under Number Tools and select Convert to Table or Convert to List so you can load the results to a worksheet.

  4. Optionally, use the various number commands in the Transform group. For more information, see Add a column based on a data type.

See Also

Power Query for Excel Help

Using the data profiling tools (docs.com)

Need more help?

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

Was this information helpful?

What affected your experience?

Thank you for your feedback!

×