cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Advocate I
Advocate I

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

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.Power BI Query Error returns blank.PNG

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Community Champion
Community Champion

Re: 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.
Specializing in Power Query Formula Language (M)

View solution in original post

Highlighted
Helper I
Helper I

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

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

View solution in original post

5 REPLIES 5
Highlighted
Community Champion
Community Champion

Re: 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.
Specializing in Power Query Formula Language (M)

View solution in original post

Highlighted
Helper I
Helper I

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

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

View solution in original post

Highlighted
Regular Visitor

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

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.

Highlighted
Regular Visitor

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

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.

Highlighted
Advocate I
Advocate I

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

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.

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors