Power Query data changes depending on rows imported

[thatpowerbiguy2]

Hi All,

Loaded an SQL Query as a source for my PowerBI and noticed that two columns were appearing as all null.  If I run the exact same SQL query (against the same database) in SSMS, I get data (some rows do contain null, but only a few).  I cross checked this with specific rows.  The columns in question are date and number types. 

The interesting part is that I have two other columns next to it with the same date and number types that will appear the same as I get in SSMS.  I've checked the SQL and they are CAST as the exact same as the columns that are working.

To give you an idea from an SQL and results perspective...

SELECT id, field1, field2, field3, CAST(date1 AS DATE) as date1, days1, CAST(date2 as DATE) as date2, days2 FROM ....

In SSMS I would get a rows, where one looks like...

id	field1	field2	field3	date1	days1	date2	days2
C001	...	...	...	2023-03-23	793	2017-05-03	2213

Copy and pasting the exact same sql from SSMS into Power Query (before any transformations) shows the same row/id as...

id	field1	field2	field3	date1	days1	date2	days2
C001	...	...	...	null	null	2017-05-03	2213

Here's where it gets wierd!  If I change the SQL (in Power Query) and add a limit to the rows to 6900 or less, then the date1 and days1 get populated.  Anything over 6900, date1 and days1 become blank.

SELECT TOP 6900 id, field1....

I've tried this in PowerBI's Power Query, in my Data Flow's Power Query (online) and Excel's Power Query and they all have this issue, yet SSMS does not.

I've doubled the cache to 8GB (although it's only showing 220MB used) in the Query settings, and still getting the issue.

Is this a bug with Power Query? because if Microsoft's products decide to just randomly drop data in reporting, then geeez, how am I to trust my PowerBI reports!!

[thatpowerbiguy2 · Accepted solution]

After much desparate attempts at anything and everything I some how managed to get it to function properly.

Don't know how, or why, but adjusting my SQL seem to have resolved it.  My SQL was using your basic joins to expand the data, and then using a window function to limit the results for each id to the top item.  This produced the expected results in SSMS (but for some reasons Power Query just would turn two columns to null unless it was under 6900 rows).

Refactoring the SQL to use a single join on a sub-query seems to work for Power Query.  Here is an example of the failing query...

   SELECT id, field1, field2, date1, days1, date2, days2

   FROM (

        SELECT id, field1, field2, apts.date1, DATEDIFF(DAY, apts.date1, CURRENT_TIMESTAMP) as days1,

           date2, days2,

           ROW_NUMBER() OVER (ORDER BY apts.date1) AS rn
        FROM table1 t

        LEFT JOIN associateivetable ast ON (t.id = ast.table1id)

        LEFT JOIN appointments apts ON (ast.appid = apts.appid)

   ) ordrd

   WHERE rn = 1

After refactoring it to the following the failing columns started working..

   SELECT id, field1, field2, date1, DATEDIFF(DAY, date1, CURRENT_TIMESTAMP) as days1, date2, days2

   FROM table1 t

   LEFT JOIN (

       SELECT ast.table1id, MAX(apts.date1) AS date1

       FROM associateivetable ast

       LEFT JOIN appointments apts ON (ast.table1id = apts.appid)

   ) sq ON (t.table1id = sq.tableid)

Don't know why that worked, as I would expect results returned from SQL Server to be identical to what SSMS was getting.  Perhaps some sort of AST that Power Query does to identify column type, not sure, but refactoring it to the later seemed to do the trick.

Hope that helps for anyone else who comes across this same issue.