Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
amjedashraf
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

 

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
toms-dsi
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.

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

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.  

Anonymous
Not applicable

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

 

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

 

Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors