Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
thatpowerbiguy2
Frequent Visitor

Power Query data changes depending on rows imported

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...

idfield1field2field3date1days1date2days2
C001.........2023-03-237932017-05-032213

 

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

idfield1field2field3date1days1date2days2
C001.........nullnull2017-05-032213

 

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!!

1 ACCEPTED SOLUTION
thatpowerbiguy2
Frequent Visitor

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.

View solution in original post

1 REPLY 1
thatpowerbiguy2
Frequent Visitor

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.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors