Not all rows loading in Power Query

[Frauke]

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?

[JoeBarry]

Hi @Frauke 

Have you tried removing the where statement?

Joe

[Frauke]

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.