Forum Discussion

powerbi_ohio's avatar
powerbi_ohio
New Member
2 years ago
Solved

PowerBI Import Issues

I have an existing query that I need to pull into PowerBI.  The query runs in dBeaver, SAS EG, Toad but when I paste the query into the SQL Statement under Advanced Options when I 'Get Data' it provides an error message.  The issue seems to be tied to a date time field.  
Here is the example of the From and Where clause:

from db.census
LEFT JOIN db.enroll on
census.id = enroll.id
and census.term = enroll.term
and enroll.date = '2024-01-29 00:00:37'dt
where (census.start_dt is null 
or census.leave_dt >= '2024-01-22 00:00:00'dt)
and census.include_flag = 'Y'

Any ideas on what I need to change the above date filters to so Desktop will import the data through the SQL statement?

 

Need Help

9 Replies

    • powerbi_ohio's avatar
      powerbi_ohio
      New Member
      2 years ago

      I had a typo in my query - I have included the updated query below that works in other applications but errors out on the import into PowerBI through the SQL statement box.  The error that is displayed is:

      DataSource.Error: ODBC: ERROR [HY000] [Oracle][ODBC][Ora]ORA-01843: not a valid month

      Details:
      DataSourceKind=Odbc
      DataSourcePath=dsn=~db~
      OdbcErrors=[Table]

       

       

       

      FROM db.census
      LEFT JOIN db.enroll ON
      census.id = enroll.id
      AND census.term = enroll.term
      AND enroll.date = '2024-01-29 00:00:37 AM'

      WHERE (census.start_dt IS NULL
      OR census.leave_dt >= '2024-01-22 00:00:00')
      AND census.include_flag = 'Y'

      • lbendlin's avatar
        lbendlin
        Super User
        2 years ago

        Maybe take it a bit easy on the date formatting

         

        FROM db.census
        LEFT JOIN db.enroll ON
        census.id = enroll.id
        AND census.term = enroll.term
        WHERE enroll.date = '2024-01-29'

        AND (census.start_dt IS NULL OR census.leave_dt >= '2024-01-22')
        AND census.include_flag = 'Y'

         

        Note that 

         

        WHERE enroll.date = '2024-01-29'

         

        changes this to an inner join.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi powerbi_ohio ,

     

    Based on the descriptive information you provided, the issue appears to be related to the date time field. In Power BI, you need to use a specific date format for the literal amount. Make sure your date literals are in a format like "YYYY-MM-DD HH:mm:ss" so that Power BI recognizes them.

     

    Try to modify your formula like below:

    FROM db.census
LEFT JOIN db.enroll ON
    census.id = enroll.id
    AND census.term = enroll.term
    AND enroll.date = '2024-01-29 00:00:37'

WHERE (census.start_dt IS NULL 
    OR census.leave_dt >= '2024-01-22 00:00:00')
    AND census.include_flag = 'Y'

     

    Best Regards,
    Adamk Kong

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    • powerbi_ohio's avatar
      powerbi_ohio
      New Member
      2 years ago

      I can't even get the raw data into PowerBI.  I'm using the SQL Statement dialogue box under advanced options.  The query that I have works in dBeaver, SAS and Toad, but when inserted into the SQL Statement dialogue box in PowerBI is when the error is triggered when trying to import.