Query output in Dataflow and Power BI limiting rows to 48,000.

[Anonymous]

I have a query that uses a function to pull data from a website via API.  In Excel Power Query, the same query+function returns 280,080 rows but in Dataflow and Power BI Desktop, it is only returning 48,000 rows.  This just happened within the past month.  Prior to that, this query and function were fine.  Any idea what could have caused this behavior?

Best regards,

Ferdinand

[v-yuta-msft]

@Anonymous ,

There should be something wrong with data loading(overload). Please try using Table.Buffer(), for example:

Source = Table.Buffer(Sql.Database())

Regards,

Jimmy Tao 

[Anonymous]

Here's the rest of the M code

------------------------------------

...

#"Replaced Value" = Table.ReplaceValue(#"Changed Type2", null, 0, Replacer.ReplaceValue, {"Bonus %", "Commission %", "Merit %", "Special Adjustment %", "Jan-2017", "Feb-2017", "Mar-2017", "Apr-2017", "May-2017", "Jun-2017", "Jul-2017", "Aug-2017", "Sep-2017", "Oct-2017", "Nov-2017", "Dec-2017", "Jan-2018", "Feb-2018", "Mar-2018", "Apr-2018", "May-2018", "Jun-2018", "Jul-2018", "Aug-2018", "Sep-2018", "Oct-2018", "Nov-2018", "Dec-2018", "Jan-2019", "Feb-2019", "Mar-2019", "Apr-2019", "May-2019", "Jun-2019", "Jul-2019", "Aug-2019", "Sep-2019", "Oct-2019", "Nov-2019", "Dec-2019", "Jan-2020", "Feb-2020", "Mar-2020", "Apr-2020", "May-2020", "Jun-2020", "Jul-2020", "Aug-2020", "Sep-2020", "Oct-2020", "Nov-2020", "Dec-2020"}),

#"Changed Type3" = Table.TransformColumnTypes(#"Replaced Value", {{"Bonus %", Percentage.Type}, {"Commission %", Percentage.Type}, {"Merit %", Percentage.Type}, {"Special Adjustment %", Percentage.Type}}),

#"Divided Column" = Table.TransformColumns(#"Changed Type3", {{"Bonus %", each _ / 100, Percentage.Type}}),

#"Divided Column1" = Table.TransformColumns(#"Divided Column", {{"Commission %", each _ / 100, Percentage.Type}}),

#"Divided Column2" = Table.TransformColumns(#"Divided Column1", {{"Merit %", each _ / 100, Percentage.Type}}),

#"Divided Column3" = Table.TransformColumns(#"Divided Column2", {{"Special Adjustment %", each _ / 100, Percentage.Type}}),

#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Divided Column3", {"Notes", "Additional Recruitment Cost", "Vendor", "Contractor's Agency", "Req Nbr", "Special Adjustment %", "Special Adjustment Date", "Merit %", "Auto Allowance", "Shift Annual Amount", "Hr/Week", "Work Location", "Tier", "Commission %", "Type", "Monthly T&E", "Job Function", "Job Title", "Excluded_From_Benefits", "Benefit_Level", "Funding", "Project Allocation", "DOJ", "Reason for Hire", "Offered CTC", "Hired Status", "Is_Term_Date", "End Date", "Start Date", "Original Hire Date", "Commission Annual Amt", "Bonus %", "Pay Rate", "First Name", "Last Name", "ID", "Plan", "Version", "Employee_Location", "BOD Initiative", "Project Category", "Project Initiative", "SVP", "VP", "FSC", "Function"}, "Attribute", "Value"),

#"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns", {{"Attribute", "Period"}}),

#"Divided Column4" = Table.TransformColumns(#"Renamed Columns", {{"Value", each _ / 100, type number}}),

#"Changed Type4" = Table.TransformColumnTypes(#"Divided Column4", {{"Period", type date}}),

#"Transform columns" = Table.TransformColumnTypes(#"Changed Type4", {{"BOD Initiative", type text}, {"Project Category", type text}, {"Project Initiative", type text}, {"SVP", type text}, {"VP", type text}, {"FSC", type text}, {"Function", type text}}),

#"Replace errors" = Table.ReplaceErrorValues(#"Transform columns", {{"BOD Initiative", null}, {"Project Category", null}, {"Project Initiative", null}, {"SVP", null}, {"VP", null}, {"FSC", null}, {"Function", null}}),

in

#"Replace errors"

[Anonymous]

@v-yuta-msft 

Below is the partial M code that only returns 48,000 rows.  But I have a workaround.  I split into 2 queries, one is {1..30} and the other one is {31..85}.  Then I combined the 2 queries and I was able to get all rows (305K now. It used to be 280K). Could the issue be related to "buffers"? Don't know much about it but I thought it might have something to do with that since splitting the query into 2 page sets made it work.  Also traced when it stopped working.  It was working fine without the need to split until 1/6/2020.  On 1/7/2020, it started giving only 48,000 rows.

Thanks,

Ferdinand

Partial M Code (exceeded 20K characters when I pasted full)

---------------------------------------------

let

Source = Table.FromColumns({{1..85},{"Forecast"}},{"Page","Version"}),

#"Filled Down" = Table.FillDown(Source, {"Version"}),

#"Changed Type" = Table.TransformColumnTypes(#"Filled Down", {{"Page", type text}, {"Version", type text}}),

#"Invoked Custom Function" = Table.AddColumn(#"Changed Type", "API Pull", each #"API Pull"([Page], [Version])),

#"Removed Errors" = Table.RemoveRowsWithErrors(#"Invoked Custom Function", {"API Pull"}),

#"Expanded API Pull" = Table.ExpandTableColumn(#"Removed Errors", "API Pull", {"Plan", "ID", "Last Name", "First Name", "Pay Rate", "Bonus %", "Commission Annual Amt", "Original Hire Date", "Start Date", "End Date", "Is_Term_Date", "Hired Status", "Offered CTC", "Reason for Hire", "DOJ", "Project Allocation", "Funding", "Benefit_Level", "Excluded_From_Benefits", "Job Title", "Job Function", "Monthly T&E", "Type", "Commission %", "Tier", "Work Location", "Employee_Location", "Hr/Week", "Shift Annual Amount", "Auto Allowance", "Merit %", "Special Adjustment Date", "Special Adjustment %", "Req Nbr", "Contractor's Agency", "Vendor", "Additional Recruitment Cost", "Notes", "BOD Initiative", "Project Category", "Project Initiative", "SVP", "VP", "FSC", "Function", "Jan-2017", "Feb-2017", "Mar-2017", "Apr-2017", "May-2017", "Jun-2017", "Jul-2017", "Aug-2017", "Sep-2017", "Oct-2017", "Nov-2017", "Dec-2017", "Jan-2018", "Feb-2018", "Mar-2018", "Apr-2018", "May-2018", "Jun-2018", "Jul-2018", "Aug-2018", "Sep-2018", "Oct-2018", "Nov-2018", "Dec-2018", "Jan-2019", "Feb-2019", "Mar-2019", "Apr-2019", "May-2019", "Jun-2019", "Jul-2019", "Aug-2019", "Sep-2019", "Oct-2019", "Nov-2019", "Dec-2019", "Jan-2020", "Feb-2020", "Mar-2020", "Apr-2020", "May-2020", "Jun-2020", "Jul-2020", "Aug-2020", "Sep-2020", "Oct-2020", "Nov-2020", "Dec-2020"}, {"Plan", "ID", "Last Name", "First Name", "Pay Rate", "Bonus %", "Commission Annual Amt", "Original Hire Date", "Start Date", "End Date", "Is_Term_Date", "Hired Status", "Offered CTC", "Reason for Hire", "DOJ", "Project Allocation", "Funding", "Benefit_Level", "Excluded_From_Benefits", "Job Title", "Job Function", "Monthly T&E", "Type", "Commission %", "Tier", "Work Location", "Employee_Location", "Hr/Week", "Shift Annual Amount", "Auto Allowance", "Merit %", "Special Adjustment Date", "Special Adjustment %", "Req Nbr", "Contractor's Agency", "Vendor", "Additional Recruitment Cost", "Notes", "BOD Initiative", "Project Category", "Project Initiative", "SVP", "VP", "FSC", "Function", "Jan-2017", "Feb-2017", "Mar-2017", "Apr-2017", "May-2017", "Jun-2017", "Jul-2017", "Aug-2017", "Sep-2017", "Oct-2017", "Nov-2017", "Dec-2017", "Jan-2018", "Feb-2018", "Mar-2018", "Apr-2018", "May-2018", "Jun-2018", "Jul-2018", "Aug-2018", "Sep-2018", "Oct-2018", "Nov-2018", "Dec-2018", "Jan-2019", "Feb-2019", "Mar-2019", "Apr-2019", "May-2019", "Jun-2019", "Jul-2019", "Aug-2019", "Sep-2019", "Oct-2019", "Nov-2019", "Dec-2019", "Jan-2020", "Feb-2020", "Mar-2020", "Apr-2020", "May-2020", "Jun-2020", "Jul-2020", "Aug-2020", "Sep-2020", "Oct-2020", "Nov-2020", "Dec-2020"}),

#"Removed Columns" = Table.RemoveColumns(#"Expanded API Pull", {"Page"}),

#"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns", {{"Original Hire Date", type date}, {"Start Date", type date}, {"End Date", type date}, {"Pay Rate", type number}, {"Commission Annual Amt", type number}, {"Plan", type text}, {"ID", type text}, {"Last Name", type text}, {"First Name", type text}, {"Is_Term_Date", type text}, {"Hired Status", type text}, {"Reason for Hire", type text}, {"DOJ", type text}, {"Project Allocation", type text}, {"Funding", type text}, {"Benefit_Level", type text}, {"Excluded_From_Benefits", type text}, {"Job Title", type text}, {"Job Function", type text}, {"Type", type text}, {"Offered CTC", type number}, {"Monthly T&E", type number}, {"Auto Allowance", type number}, {"Shift Annual Amount", type number}, {"Hr/Week", Int64.Type}, {"Tier", type text}, {"Work Location", type text}, {"Special Adjustment Date", type date}, {"Req Nbr", type text}, {"Contractor's Agency", type text}, {"Vendor", type text}, {"Notes", type text}, {"Additional Recruitment Cost", Int64.Type}, {"Employee_Location", type text}}),

#"Changed Type2" = Table.TransformColumnTypes(#"Changed Type1", {{"Jan-2017", type number}, {"Feb-2017", type number}, {"Mar-2017", type number}, {"Apr-2017", type number}, {"May-2017", type number}, {"Jun-2017", type number}, {"Jul-2017", type number}, {"Aug-2017", type number}, {"Sep-2017", type number}, {"Oct-2017", type number}, {"Nov-2017", type number}, {"Dec-2017", type number}, {"Jan-2018", type number}, {"Feb-2018", type number}, {"Mar-2018", type number}, {"Apr-2018", type number}, {"May-2018", type number}, {"Jun-2018", type number}, {"Jul-2018", type number}, {"Aug-2018", type number}, {"Sep-2018", type number}, {"Oct-2018", type number}, {"Nov-2018", type number}, {"Dec-2018", type number}, {"Jan-2019", type number}, {"Feb-2019", type number}, {"Mar-2019", type number}, {"Apr-2019", type number}, {"May-2019", type number}, {"Jun-2019", type number}, {"Jul-2019", type number}, {"Aug-2019", type number}, {"Sep-2019", type number}, {"Oct-2019", type number}, {"Nov-2019", type number}, {"Dec-2019", type number}, {"Special Adjustment %", type number}, {"Merit %", type number}, {"Commission %", type number}, {"Bonus %", type number}, {"Jan-2020", type number}, {"Feb-2020", type number}, {"Mar-2020", type number}, {"Apr-2020", type number}, {"May-2020", type number}, {"Jun-2020", type number}, {"Jul-2020", type number}, {"Aug-2020", type number}, {"Sep-2020", type number}, {"Oct-2020", type number}, {"Nov-2020", type number}, {"Dec-2020", type number}}),

...

[v-yuta-msft]

@Anonymous ,

Have you solved your issue by now? If you have, could you please help mark the correct answer to finish the thread? Your contribution will be much appreciated.

Regards,

Jimmy Tao

[v-yuta-msft]

@Anonymous ,

Could you please show some part of M code in Excel PQ editor for further analysis(e.g.: record count step)?

Regards,

Jimmy Tao

[Anonymous]

Update:

While inside Excel PQ editor, I added a record count step and it showed 280,080. But when I closed the editor and chose to load output to worksheet, I'm also just getting 48,000 rows.  Why is this happening?  For a temporary workaround, is there a way to remove this limit?

Regards,

Ferdienand