problem with getting data with SQL query

[vulpoid]

Hi friends...

i am brandnew to PowerBi for desktop and hope to get help from experienced users.

i have a huge xls.file on our company server. in this file are some spreadsheets (2018, 2019, 2020, 2021) that contain our salesdata - of course each sheet with identic layout (Column numbers, order of columns, formatting...) . Separation into yearly sheets was done because of row limitation in xls. 

In Excel i was able to add a dataconnection to one table in this file and i used PowerPivot tableproperties to modify SQL to create a union all to combine salesdata from more than 1 initial sheet. eg: 

SELECT ['2019$'].[Date],['2019$'].[Code],['2019$'].[Quantity],['2019$'].[PZN]

UNION ALL

SELECT ['2020$'].[Date],['2020$'].[Code],['2020$'].[Quantity],['2020$'].[PZN]

UNION ALL

SELECT ['2021$'].[Date],['2021$'].[Code],['2021$'].[Quantity],['2021$'].[PZN]

whenever i try to do a "GetData" using OLE BD or MySQL or SQL Database i fail due to some SQL settings...

Does anybody have an idea which connector to use that will fit my needs!?

Thanks, Alex

[amitchandak]

@vulpoid , It can issue because of MySQL diver too. What is the error you are getting

make sure the driver is installed: https://dev.mysql.com/downloads/connector/net/

Also, you have to give this query in the advance editor of the connection

[vulpoid]

Sorry for late answer - thanks for help. i found solution to  get same result with powerquery. so i won't try longer with SQL. 

Thanks anyway for your attempt to help. BR Alex