Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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.
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.
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
Good. Appending two tables is being part of Transformations of Power BI.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.