Forum Discussion

Frauke's avatar
Frauke
Frequent Visitor
3 years ago

Not all rows loading in Power Query

Hello,

 

I'm pulling data from a system-DSN into PowerQuery with the following query:

= Odbc.Query("dsn=PEO-OdooLive", "SELECT * FROM mail_message WHERE [id] > 0")

 

Eventough I cleary select all, it's not loading all the rows.

With the above query I get only 4592 rows with highest id = 7081

 

However, when I change the query to:

= Odbc.Query("dsn=PEO-OdooLive", "SELECT * FROM mail_message WHERE [id] > 10000")

I get 4861 different rows with lowest id = 10010 and highest id = 15825

 

= Odbc.Query("dsn=PEO-OdooLive", "SELECT * FROM mail_message WHERE [id] > 7081 and <= 10000")

I get 1579 different rows with lowest id = 7082 and highest id = 10000

 

I'm really puzzled as to why this is happening and the only solution I can think of is to split my query into id-ranges, but the highest id I've found is 261145 and I get about 4500-5000 rows with one query, so it would quite a hassle and not a sustainable solution, since the table will keep growing.

 

Does anybody have an idea to what might be causing this?

 

Need Help

2 Replies

    • Frauke's avatar
      Frauke
      Frequent Visitor
      3 years ago

      Hi Barry,

       

      Thanks for your reply.

      My original query was without the WHERE statement, this gives the same result as the first query in my question (WHERE id >0)

       

      I've tried the crappy solution I've mentioned earlier for id's to 100k, so I've created 20 queries for each range of 5000 id's and combined those queries into a new query, which gives me the expected result in PowerQuery. But if I try to load this I get an error message:

       

      What I need from the mail_message table are the values from column "create_date" for all message_id in another table "crm_messages".

      Therefor I've also tried to get just the two columns [id] and [create_date] but this gives me the same number of rows.

       

      I wish I could give some more information, but I haven't got a clue what more to mention.