error message after building query returns zero rows in the error query....

In general this happens when there are no errors in the query, but errors occur when the query results are loaded into the data model.

Examples:

• unique key values in Power Query (case sensitive), that are not unique when loading (case insensitive), 
• numbers with fractions in a column with data type Int64.Type (Whole Number),
• etcetera.

I had the same problem, I had 500k+ rows loaded, all returning errors. However, I had no errors returned when I did either a Try on any column whatsoever, nor did I get any result with the kept errors function. Everything seemed to work fine though, so no worries...

After some time though, I found that certain fields didn't work as intended. That is, they turned up blank after being loaded, while they were not blank in the query editor. In my opinion, this is just another bug.

For example, what I did: I loaded a (couple) .csv file in one query. Then, using a second query I did a few small transformations. I did this using this query (I changed the columnnames, just in case you find something odd there):

= Table.TransformColumns(Source, {{"price", each Text.Replace(_, ".", ","), type number}, {"Weight", each Text.Replace(_, ".", ","), type number}, {"Length", each Text.Replace(_, ".", ","), type number}, {"Width", each Text.Replace(_, ".", ","), type number}, {"Height", each Text.Replace(_, ".", ","), type number}, {"startdate", each if (_ = "00-00-0000") then (null) else (Text.Range(_, 3, 2) & "-" & Text.Start(_, 2) & "-" & Text.End(_, 4)), type date}, {"some sign", each Logical.FromText(if(_ = "X") then "true" else "false"), type logical}, {"some different sign", each Logical.FromText(if(_ = "X") then "true" else "false"), type logical}, {"Segmented", each Logical.FromText(if(_ = "X") then "true" else "false"), type logical}})

The funny thing here is that in the query editor shows me a perfect column with the desired result (1.2 type and I see all the numbers, just like I would expect, except... the numbers are on the left side (I missed this at first).

After loading the query, I had 500k+ errors found in 500k+ rows. Clicking the show errors resulted in a non-responsive program the first time (with an I7 and 32Gb, so there something funny here for sure). The second time (and any time after), it returned a blank table. As mentioned, I have tried every column with the try statement, but no luck. Obviously there were no visible errors.

Ultimately, after my visuals didn't work with any of the numbers, I found out that the loaded tables contained blank number fields, rather than the numbers that the query editor showed me.

So I returned to the query editor and forced yet another change type with locale to number and to my no small surprise, all the numbers shifted to the right side of the column.

Reloading my queries to the report gave me no errors anymore and all my visuals worked instantly.

So long story short :), my advice is, if you ran out of other options, to try and force another "change type -> to number" on your decimal numbers.

Hope this helps.

Cheers,

Niels