cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Microsoft SQL: Incorrect syntax near the keyword 'union'. Table: SalesFact.

Hi All,

 

Can anyone help me with this error. When i refresh data set in powerbi service it give me this error.

 

Microsoft SQL: Incorrect syntax near the keyword 'union'. Table: SalesFact.

 

Where as in powerbi desktop the table is getting refreshed.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

 

Hi All,

 

i got the solution to this error, it was append query which was making the error. I was appending some data from another table. 

 

if anyone face this error in future always look for append in your query in your table just delete it this error will be resolved.

 

The append query was working fine but maybe now the data has increased in the table so it was not working fine. Thanks

 

View solution in original post

6 REPLIES 6
Highlighted
Anonymous
Not applicable

Hi amjedashraf,

 

Please check the SQL query of the datasets and overwrite your Datasource for the report in Power BI Service if required.

 

 

Regards,

Pradeep

Highlighted

Hi Pauwn,

 

I have checked the query, in the query editor there is no word like union in the query and there is no syntax error in the query editor. 

 

Dont know what is happening.  

Highlighted
Anonymous
Not applicable

Have you checked in your filters/Transformations applied in the datasets?

Highlighted

 

Hi All,

 

i got the solution to this error, it was append query which was making the error. I was appending some data from another table. 

 

if anyone face this error in future always look for append in your query in your table just delete it this error will be resolved.

 

The append query was working fine but maybe now the data has increased in the table so it was not working fine. Thanks

 

View solution in original post

Highlighted
Anonymous
Not applicable

Good. Appending two tables is being part of Transformations of Power BI.

Highlighted
Frequent Visitor

This fixes the issue while keeping the merged Query.

 

You can pull the underlying SQL by Right Clicking on the last Applied Steps on the query you merged in the Power Query Editor.  Then copy and open that query in Management Studio.  I received the same error "Incorrect Syntax... Union".  After reviewing the 7 pages of SQL, I noticed that the first Select had an ORDER BY at the bottom right before the UNION ALL.  In SQL only one ORDER BY is allowed at the end after Unioning the Select Statements together. 

To fix the issue I had to find where the I had "Sorted" in a query.  So, I looked at the Query steps in the Queries I created (I was merging a Query that was referenced from another Query and the original Query had a Step "Sorted Rows".  Removed that step and the Merged Query worked fine.  

 

Seems like a bug in the Query folding to not take out the first Order By.

 

Hope that helps.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors