I am using Power BI to connect to a Python Script. Within this Python Script I am utilizing PandaSql and SQLDF. I am taking this approach as I need to manipulate an excel file with SQL queries. Unfortunately the only way I can go about this in PowerBI is to connect to a Python Script and then run my SQL queries within the Python Script. Here is my code: (https://i.stack.imgur.com/wlbgn.png)
Note, I modified the path in df due to security concerns. That is not the issue I am having. Everything works great until I get to the query. I know there are many records in my data with Facility City = San Diego. However, when I click ok and the script runs, the table SD_table has no data.
I tested an alternate where clause (where Sector = 'DS&J') to see if it was working at all. This loaded SD_table with all records where the Sector column was equal to DS&J. Therefore, this leads me to believe that my issue in the attached code is due to the column name containing spaces and the column value I am searching for also containing spaces.
Can someone please educate me on how to call upon columns with spaces in their name and also how to have a where clause searching for values containing spaces?
Thank you in advance!
I tried to pull only the records in which the Facility City = San Diego. I wanted this loaded into the table SD_table. However, when PowerBI executes the script, SD_table does not have any data loaded.
OH MY GOD WHO KNEW THE SQL WITHIN THE PYTHON SCRIPT IS CASE SENSITIVE... I write SQL code all the time in MySQL and you can call values regardless of case. However, in my data the values were SAN DIEGO, LOS ANGELES, COSTA MESA. So when i changed it to : where
Facility CityIN ("SAN DIEGO", "LOS ANGELES", "COSTA MESA")It worked. Hope this saves someone the many hours I've spent researching sqldf :)