cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Matt-Majedi
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?

 

1 REPLY 1
calerof
Helper III
Helper III

Hi @Matt-Majedi,

No answer, right? I'm experiencing something similar. I have the following tables in my DataFlow:

  1. one excel table in OneDrive for Business
  2. one SharePoint List
  3. one imported table from SQL Server.

 I'm using DataFlow to import them and appending them in a new table. When I check each table in the DataFlow I see all the records, even with the new appended table, but when I see the table already in Tables, not DataFlows, then it's truncated, not all the records appear. 

Would the Query Folding have something to do here?

Anyway, I'll keep checking some where else.

Fernando

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

Power Apps Community Call

Monthly Power Apps Community Call

Did you miss the call?? Check out the Power Apps Community Call here!

secondImage

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 the go-to Power Platform destination for ISV’s to monitor & manage applications post-AppSource publish.

Users online (73,090)