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

[Moh_ali_square]

Hi, 

I am building my query from multiple tables in power BI, then I got a message there are errors in the query. once I return to query editor, I found a blank report. and after that it works fine, why I got this error message?

Thanks.

[MarcelBeug · Accepted solution]

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.

[C4YNelis · Accepted solution]

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

[Anonymous]

Thanks....this tip saved me.  In my case, I was appending queries and had a whole number type on a field in the first query and a decimal number type on the same field in the second query.  Changing both to decimal fixed the issue.

[AussieCoops]

Awesome.   I have the same error with 8793 rows of 24444 showing errors but the error table is blank.

I will work through the de-bug and let you know how I go.

[AussieCoops]

Worked the query through each step and found the anomaly in the column data types.

Despite having the int64.type the column was appearing as text.  Changed it to type number and it all works now.

Thanks for the tip.

[MarcelBeug · Accepted solution]

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.