In the past when there has been an error when loading data into the semantic model, there can be times when clicking on the View errors can either take a very long time to show those errors. Or in some cases it never shows you the error.

In this blog post I am going to show you an alternative way to quickly find the errors.

This is what the dreaded errors look like.

For me to find the errors I went back into Power Query and to the table which had the error.

As shown above it was in my table called “Date”

If you did not know Power Query will only load the top 1000 rows when in the Power Query Editor.

To find the errors I had to click on the “Column profiling” on the bottom left-hand corner and change it to say “Colum profiling based on entire dataset”

NOTE: This means that it can take some time to load the additional data, but this is needed as sometimes the error not in the top 1000 rows.

To make it easier to find the errors, I then enabled the Column quality.

In the ribbon I clicked on View and under Data Preview I enabled “Column quality”

Now all the columns I can then see the Column Quality making it easier to identify which column might have the error.

I then click on the first column in my table, scroll across to the last column, hold down SHIFT and click on the last column to get all the columns highlighted. As shown below the column name is highlighted in Green showing it has been selected.

I then click on Home in the ribbon, then I click on Keep Rows and select “Keep Errors”

This will then only keep the rows where there are errors across all my columns. Without me having to select each individual column.

In my experience this then also reduces the amount of data in each of the columns.

I can now try and find the column which has got the error by looking at the Column Profile, which would show the percentage of errors and possibly a red bar as shown below.

I could then resolve the error and load the data successfully.

Summary

In this blog post I have shown you an alternative way to quickly find your errors and resolve them so that you can go back to creating amazing Semantic models and reports.

If you have any comments or questions, please let me know and thanks for reading.