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
Advocate II
Advocate II

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
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Users online (1,208)