Problem with data load via API from Business Central

[Kaca]

Hi everyone,

I stumbled on a problem with data refresh. I have model with 5 tables from BC, connected via API, for 3 of them refresh is working well but for 2 I am not receiving new data. Same thing happens when i load them in the blank report without any data transformation, always the same 141 rows are loaded out of 193. On the API side, no filters were used and when i load data with it in Excel i receive complete data. All permissions are set properly.

I will appreciate if you have any suggestions for the solution of this problem

[Akash_Varuna · Accepted solution]

Hi @Kaca The issue could be of query folding or caching in Power BI. Ensure query caching is disabled, and check for pagination handling in the API if the data exceeds the row limit. Review the M Code in Power Query for any unintended filters.

[v-dineshya · Accepted solution]

Hi @Kaca ,

Thanks for reaching out to the Microsoft fabric community forum.

1. API Pagination in Business Central:
BC API returns a maximum number of rows per page (default is 100, can be up to 1000). If you're getting exactly 141 out of 193 rows every time, it's likely you're hitting a pagination issue and Power BI isn’t handling the next page automatically.

Solution: Check if there's a @odata.nextLink in the response. Power BI should follow that link, but sometimes it doesn’t especially with custom connectors or if the structure is slightly off. Use Power Query (Advanced Editor) to handle pagination manually.

2. Data Type Issues or Errors in Rows:
Sometimes Power BI quietly skips rows due to schema mismatches, especially when a column suddenly has a different data type a null or text in a numeric column.

Solution: Load the API response into Power Query and check for errors in rows or columns. Use Table.HasErrors or add an error-checking step.

3. Query Folding Limitations:
If query folding isn’t fully supported for the specific BC tables you're using, Power BI may not properly paginate or retrieve all rows.

Solution: Go to Power Query → View → Query Diagnostics and see if folding is broken at any step. Try accessing the API directly using Web.Contents and parsing JSON manually.

4. Data Privacy Settings:
Power BI’s privacy levels can block cross-source queries or impact API behavior.

Solution: Go to File → Options → Privacy → Check that privacy levels aren’t causing issues. Set to "Ignore Privacy Levels" for testing (if secure).

5. Column Count Mismatch or Metadata Issues:
If the metadata from BC has changed but Power BI cached the old schema, rows may be skipped due to structural mismatches.

Solution: In Power BI, clear the Data Source Settings cache. Also try removing and re-adding the problematic tables entirely.

If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

Thank you

[v-dineshya · Accepted solution]

Hi @Kaca ,

Thanks for reaching out to the Microsoft fabric community forum.

1. API Pagination in Business Central:
BC API returns a maximum number of rows per page (default is 100, can be up to 1000). If you're getting exactly 141 out of 193 rows every time, it's likely you're hitting a pagination issue and Power BI isn’t handling the next page automatically.

Solution: Check if there's a @odata.nextLink in the response. Power BI should follow that link, but sometimes it doesn’t especially with custom connectors or if the structure is slightly off. Use Power Query (Advanced Editor) to handle pagination manually.

2. Data Type Issues or Errors in Rows:
Sometimes Power BI quietly skips rows due to schema mismatches, especially when a column suddenly has a different data type a null or text in a numeric column.

Solution: Load the API response into Power Query and check for errors in rows or columns. Use Table.HasErrors or add an error-checking step.

3. Query Folding Limitations:
If query folding isn’t fully supported for the specific BC tables you're using, Power BI may not properly paginate or retrieve all rows.

Solution: Go to Power Query → View → Query Diagnostics and see if folding is broken at any step. Try accessing the API directly using Web.Contents and parsing JSON manually.

4. Data Privacy Settings:
Power BI’s privacy levels can block cross-source queries or impact API behavior.

Solution: Go to File → Options → Privacy → Check that privacy levels aren’t causing issues. Set to "Ignore Privacy Levels" for testing (if secure).

5. Column Count Mismatch or Metadata Issues:
If the metadata from BC has changed but Power BI cached the old schema, rows may be skipped due to structural mismatches.

Solution: In Power BI, clear the Data Source Settings cache. Also try removing and re-adding the problematic tables entirely.

If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

Thank you

[Kaca]

Hello Dineshya

My appologies for late response. At the end it was problem in API setup but your reply had all options for resolving this issue if Power BI had it. Thank you very much!

[v-dineshya · Accepted solution]

Hi @Kaca ,

If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS. Feel free to reach out if you need further assistance.
Thanks and Regards

[v-dineshya]

Hi @Kaca ,

If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS. Feel free to reach out if you need further assistance.
Thanks and Regards

[v-dineshya]

Hi @Kaca ,

If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS. Feel free to reach out if you need further assistance.
Thanks and Regards

[Kaca]

Hi Akash,

Thanks for your reply.

I cleared cache and also row number limit is not reached, in the same API, tabels with 500 rows are not having problem with data refresh. Also, in Power Query no aditional transformations were done, in Naviation step only 141 rows are loaded.

[Akash_Varuna · Accepted solution]

Hi @Kaca The issue could be of query folding or caching in Power BI. Ensure query caching is disabled, and check for pagination handling in the API if the data exceeds the row limit. Review the M Code in Power Query for any unintended filters.