Running SQL Queries on an excel table fails in PBI but works in excel

[smpa01]

Hello Experts,

I am currently querying a large excel table in PQWRY and to avoid transformations using M, I thought of running SQL Statement on an excel table.

It is doable in excel. For example, I can query an excel data source in another excel by

Get Data-> From Other Queries-> From Microsoft Query->Excel Files.

I can utilize the Command text box to execute the SQL statement. It is

Now when I tried the same thing in Power BI, it did not work. I am not sure what am I doing wrong?

My raw data (table) is following

Count

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

I want to execute a SQL statement as

SELECT *
FROM table
WHERE [Count]<=10.
ORDER BY [Count] DESC

Any help is greatly apprecaited. I want to avoid slow and performance deterring M statements.

Thank you in advance.

[smpa01]

I did another test where I queired a SQL server view and it worked fine both in excel and power bi

Excel

This is what I am getting in PBI and these are my current ODBC drivers. I am not sure if the eroor is happeing due to the fact I don't have an odbc driver for excel. But I am also not sure if that is the case why it worked in excel in first place.

Thanks

[Anonymous]

@smpa01 

You might want to create a support ticket for further analysis of this issue.

Regards
Paul