cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Dataflow does not load all records from SQL database

Hi all, I am new in CDS and data flows. I am trying to import some data from our student record database which is an on-prem SQL database into 2 custom entities. Using Gateway, I can connect to the database from data flows, no problem.

I am interested in importing data from 2 specific tables, student detail table and student enrolment table. I want to import a list of all students who have an enrolment record for this year (2020)

In the data flow, I added the 2 tables and from the student enrolment table, I apply a filter to the table to extract data for this year only:

 

Table.SelectRows(#"Navigation 1", each [year] = "2020")

 

Then I remove duplicates to get one row per students:

 

Table.Distinct(#"Removed columns", {"Student_ID"})

 

Merge with student detail table:

 

Table.NestedJoin(#"Remove duplicates" , {"Student_ID"}, Student_Detail, {"Student_ID"}, "Student_Detail", JoinKind.LeftOuter)

 

I expanded the columns from the student detail table and choose the column I wanted. When I run this dataflow it added around 18K records into the student entity, but I know there should be 38K records. So I created a new data flow with no join to student detail and I can see 38K records was created.

The student detail table has almost 1M records, my theory is that data flow is not loading all records from student detail table to perform the join so it is missing them. Any thought on this?

 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Watch Now

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Users online (6,802)