{"id":6400,"date":"2024-11-10T13:37:30","date_gmt":"2024-11-10T13:37:30","guid":{"rendered":"https:\/\/powergi.net\/?p=6400"},"modified":"2026-06-16T16:40:04","modified_gmt":"2026-06-16T16:40:04","slug":"power-query-pagination-for-apis-excel-power-bi","status":"publish","type":"post","link":"https:\/\/powergi.net\/es\/blog\/power-query-pagination-for-apis-excel-power-bi\/","title":{"rendered":"Power Query pagination for APIs | Excel | Power BI"},"content":{"rendered":"\t\t<div data-elementor-type=\"wp-post\" data-elementor-id=\"6400\" class=\"elementor elementor-6400\" data-elementor-post-type=\"post\">\n\t\t\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-d50cb0c elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"d50cb0c\" data-element_type=\"section\" data-e-type=\"section\" data-settings='{\"jet_parallax_layout_list\":[]}'>\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-306ba1c\" data-id=\"306ba1c\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-747eb05 elementor-toc--minimized-on-tablet elementor-widget elementor-widget-table-of-contents\" data-id=\"747eb05\" data-element_type=\"widget\" data-e-type=\"widget\" data-settings='{\"exclude_headings_by_selector\":[],\"no_headings_message\":\"No headings were found on this page.\",\"headings_by_tags\":[\"h2\",\"h3\",\"h4\",\"h5\",\"h6\"],\"marker_view\":\"numbers\",\"minimize_box\":\"yes\",\"minimized_on\":\"tablet\",\"hierarchical_view\":\"yes\",\"min_height\":{\"unit\":\"px\",\"size\":\"\",\"sizes\":[]},\"min_height_tablet\":{\"unit\":\"px\",\"size\":\"\",\"sizes\":[]},\"min_height_mobile\":{\"unit\":\"px\",\"size\":\"\",\"sizes\":[]}}' data-widget_type=\"table-of-contents.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t\t<div class=\"elementor-toc__header\">\n\t\t\t\t\t\t<h4 class=\"elementor-toc__header-title\">\n\t\t\t\tTable of Contents\t\t\t<\/h4>\n\t\t\t\t\t\t\t\t\t\t<div class=\"elementor-toc__toggle-button elementor-toc__toggle-button--expand\" role=\"button\" tabindex=\"0\" aria-controls=\"elementor-toc__747eb05\" aria-expanded=\"true\" aria-label=\"Abrir la tabla de contenidos\"><i aria-hidden=\"true\" class=\"fas fa-chevron-down\"><\/i><\/div>\n\t\t\t\t<div class=\"elementor-toc__toggle-button elementor-toc__toggle-button--collapse\" role=\"button\" tabindex=\"0\" aria-controls=\"elementor-toc__747eb05\" aria-expanded=\"true\" aria-label=\"Cerrar la tabla de contenidos\"><i aria-hidden=\"true\" class=\"fas fa-chevron-up\"><\/i><\/div>\n\t\t\t\t\t<\/div>\n\t\t\t\t<div id=\"elementor-toc__747eb05\" class=\"elementor-toc__body\">\n\t\t\t<div class=\"elementor-toc__spinner-container\">\n\t\t\t\t<i class=\"elementor-toc__spinner eicon-animation-spin eicon-loading\" aria-hidden=\"true\"><\/i>\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-63b83ae elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"63b83ae\" data-element_type=\"section\" data-e-type=\"section\" data-settings='{\"jet_parallax_layout_list\":[]}'>\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-f35648f\" data-id=\"f35648f\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-9ee57d0 elementor-widget elementor-widget-text-editor\" data-id=\"9ee57d0\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t\t<p><span style=\"font-weight: 400;\">We can use <\/span><b>Power Query<\/b><span style=\"font-weight: 400;\"> to pull records from APIs where our data resides, but most of the time each API we connect to will work in different ways:\u00a0<\/span><\/p><ul><li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">Some APIs might bring all records on the same page\u00a0<\/span><\/li><\/ul><ul><li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">Some APIs will limit the number of records you can retrieve\u00a0<\/span><\/li><\/ul><ul><li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">Some APIs will pull data into pages so you can only bring X amount of records at a time\u00a0<\/span><\/li><\/ul><p><span style=\"font-weight: 400;\">In this blog, we will cover the scenario where we need to paginate to extract all available records based on the data we requested from the API. If you\u2019re working with <a href=\"https:\/\/powergi.net\/microsoft-power-platform-consulting-services\/\"><strong>Microsoft Power Platform consulting services<\/strong><\/a>, understanding API pagination is crucial for optimizing data retrieval and integration processes.<\/span><\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-60d6395 elementor-widget elementor-widget-heading\" data-id=\"60d6395\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t<h2 class=\"elementor-heading-title elementor-size-default\">Solution description \n<\/h2>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-ee7cceb elementor-widget elementor-widget-text-editor\" data-id=\"ee7cceb\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t\t<p><span style=\"font-weight: 400;\">Let\u2019s suppose we want to extract the list of invoices for the current from our accounting system, when we send this request to the API we get the following:\u00a0<\/span><\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-27c4d9a elementor-widget elementor-widget-image\" data-id=\"27c4d9a\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"image.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<img fetchpriority=\"high\" decoding=\"async\" width=\"429\" height=\"119\" src=\"https:\/\/powergi.net\/wp-content\/uploads\/2024\/12\/unnamed-53.png\" class=\"attachment-large size-large wp-image-6403\" alt=\"unnamed (53)\" srcset=\"https:\/\/powergi.net\/wp-content\/uploads\/2024\/12\/unnamed-53.png 429w, https:\/\/powergi.net\/wp-content\/uploads\/2024\/12\/unnamed-53-300x83.png 300w\" sizes=\"(max-width: 429px) 100vw, 429px\">\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-5646424 elementor-widget elementor-widget-text-editor\" data-id=\"5646424\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t\t<p><span style=\"font-weight: 400;\">The total invoices we get in the result are 917 but the current page only has 100 records, that means we need to pull 10 different pages to extract the full set of records available.\u00a0<\/span><\/p><div id=\"seology-cta-1\">\t\t<div data-elementor-type=\"section\" data-elementor-id=\"7267\" class=\"elementor elementor-7267\" data-elementor-post-type=\"elementor_library\">\n\t\t\t<div class=\"elementor-element elementor-element-7e3f164 e-con-full e-flex e-con e-parent\" data-id=\"7e3f164\" data-element_type=\"container\" data-e-type=\"container\" data-settings='{\"background_background\":\"classic\",\"jet_parallax_layout_list\":[]}'>\n\t\t<div class=\"elementor-element elementor-element-9078c9f e-con-full e-flex e-con e-child\" data-id=\"9078c9f\" data-element_type=\"container\" data-e-type=\"container\" data-settings='{\"jet_parallax_layout_list\":[]}'>\n\t\t\t\t<div class=\"elementor-element elementor-element-7b99949 elementor-widget elementor-widget-heading\" data-id=\"7b99949\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t<p class=\"elementor-heading-title elementor-size-default\">Turn your ideas into digital solutions<\/p>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-8e61db5 elementor-widget elementor-widget-heading\" data-id=\"8e61db5\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t<p class=\"elementor-heading-title elementor-size-default\">Our team guides you step by step to build custom apps in Power Platform.<\/p>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t<div class=\"elementor-element elementor-element-a578907 e-flex e-con-boxed e-con e-child\" data-id=\"a578907\" data-element_type=\"container\" data-e-type=\"container\" data-settings='{\"jet_parallax_layout_list\":[]}'>\n\t\t\t\t\t<div class=\"e-con-inner\">\n\t\t\t\t<div class=\"elementor-element elementor-element-e8c827b elementor-view-stacked elementor-shape-circle elementor-widget elementor-widget-icon\" data-id=\"e8c827b\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"icon.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<div class=\"elementor-icon-wrapper\">\n\t\t\t<a class=\"elementor-icon\" href=\"#ctc_chat\">\n\t\t\t<i aria-hidden=\"true\" class=\"fab fa-whatsapp\"><\/i>\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-d834237 elementor-view-stacked elementor-shape-circle elementor-widget elementor-widget-icon\" data-id=\"d834237\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"icon.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<div class=\"elementor-icon-wrapper\">\n\t\t\t<a class=\"elementor-icon\" href=\"https:\/\/powergi.net\/es\/contact\/\">\n\t\t\t<i aria-hidden=\"true\" class=\"far fa-envelope\"><\/i>\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t<\/div><p><span style=\"font-weight: 400;\">To achieve this in <strong><a href=\"https:\/\/powergi.net\/blog\/epoch-dates-in-power-query-excel-power-bi\/\" target=\"_blank\" rel=\"noopener\">Power Query<\/a><\/strong> we will use Power Query\u2019s List.Generate() function. This function is the closest feature we have in M language to loop through a set. <\/span><\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-e74512e elementor-widget elementor-widget-text-editor\" data-id=\"e74512e\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t\t<p><span style=\"font-weight: 400;\">The way we use List.Generate depends on the type of results the API returns. We will cover 2 scenarios in this blog:\u00a0<\/span><\/p><ol><li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">API gives us information about how many pages the results will have\u00a0<\/span><\/li><li><span style=\"font-weight: 400;\">API only returns a list of records and we need to \u201cskip\u201d records to find the next available batch<\/span><\/li><\/ol><p>As a team deeply entrenched in <a href=\"https:\/\/powergi.net\/microsoft-power-platform-consulting-services\/\"><strong>Microsoft Power Platform consulting services<\/strong><\/a>, we\u2019re uniquely positioned to guide businesses through the complexities of API pagination with Power Query. Our expertise ensures that organizations can optimize their data retrieval processes, allowing them to focus more on strategic initiatives rather than getting bogged down by technical hurdles.<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-8fc247c elementor-widget elementor-widget-heading\" data-id=\"8fc247c\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t<h2 class=\"elementor-heading-title elementor-size-default\">Scenario 1. API uses page numbers to paginate <\/h2>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-383f4f7 elementor-widget elementor-widget-text-editor\" data-id=\"383f4f7\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t\t<p><span style=\"font-weight: 400;\">Some APIs will include in the results attributes we can use to calculate the number of pages we need. In the below example we can use the number of records and page size to calculate how many times we need to paginate.\u00a0<\/span><\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-757ca70 elementor-widget elementor-widget-image\" data-id=\"757ca70\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"image.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<img decoding=\"async\" width=\"429\" height=\"119\" src=\"https:\/\/powergi.net\/wp-content\/uploads\/2024\/12\/unnamed-53-1.png\" class=\"attachment-large size-large wp-image-6404\" alt=\"unnamed (53)\" srcset=\"https:\/\/powergi.net\/wp-content\/uploads\/2024\/12\/unnamed-53-1.png 429w, https:\/\/powergi.net\/wp-content\/uploads\/2024\/12\/unnamed-53-1-300x83.png 300w\" sizes=\"(max-width: 429px) 100vw, 429px\">\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-61248fa elementor-widget elementor-widget-text-editor\" data-id=\"61248fa\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t\t<p><span style=\"font-weight: 400;\">Note: Some other APIs will give us directly the number of pages available so we can loop them directly.\u00a0<\/span><\/p><p><span style=\"font-weight: 400;\">API GET request looks like this:\u00a0<\/span><\/p><p><a href=\"https:\/\/abc.abc.com\/api\/Invoices?page_no=1&amp;page_size=100\" target=\"_blank\" rel=\"noopener\"><span style=\"font-weight: 400;\">https:\/\/abc.abc.com\/api\/Invoices?page_no=1&amp;page_size=100<\/span><\/a><span style=\"font-weight: 400;\">\u00a0<\/span><\/p><p><span style=\"font-weight: 400;\">\u00a0<\/span><span style=\"font-weight: 400;\">Power Query to extract pages for this API would be as follows:<\/span><\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-bdd444c elementor-widget elementor-widget-code-highlight\" data-id=\"bdd444c\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"code-highlight.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<div class=\"prismjs-default copy-to-clipboard \">\n\t\t\t<pre data-line=\"\" class=\"highlight-height language-javascript line-numbers\">\n\t\t\t\t<code readonly class=\"language-javascript\">\n\t\t\t\t\t<xmp>\/\/make an initial request to calculate the number of pages to loop through \r\nSource_pages = Json.Document(Web.Contents(\"https:\/\/abc.abc.com\/api\", [ \r\n\u202f RelativePath=\"Invoices\", \r\n\u202f Query=[ page_no=\"1\",page_size=\"100\"], \r\n\u202f Headers=[#\"Content-Type\"=\"application\/json\", Authorization=\"xxxxx\"]])), \r\n\/\/get number of pages: Records available in the result \/ Number of records in each page \r\n\u202f \u202f \u202f \u202f \u202ftotal_pages = Number.RoundUp(Source_pages[record_count]\/Source_pages[page_size]), \r\n\/\/create a function to extract each page \r\n\/\/this function will be looped by each of the pages \r\n\u202f \u202f GetDataAPI = (pageNum as number) =&gt;  \r\n\u202f \u202f \u202f \u202f let \r\n\u202f \u202f \u202f \u202f \u202f \u202f Source = Json.Document(Web.Contents(\"https:\/\/ abc.abc.com\/api\", [ \r\n\u202f RelativePath=\" Invoices \", \r\n\u202f Query=[page_no=Number.ToText(pageNum),page_size=\"100 \"], \r\n\u202f Headers=[#\"Content-Type\"=\"application\/json\", Authorization=\"xxxxx\"]])),\/\/connect to API \r\n\/\/obtain page if results are available \r\n\u202f \u202f \u202f \u202f \u202f \u202f items = if pageNum &lt;= Number.RoundUp(Source[record_count]\/Source[page_size]) then Source[invoices] else null \/\/drill down to query results \r\n\u202f \u202f \u202f \u202f in \r\n\u202f \u202f items, \r\n\u202f \u202f Source = List.Generate( () =&gt;\/\/create a loop to iterate through the pages and append results \r\n\u202f \u202f \u202f \u202f \u202f \u202f \u202f \u202f \u202f[Result = try GetDataAPI(1) otherwise null, Page =1 ], \/\/connect the first time and set number of pages \r\n\u202f \u202f \u202feach ([Result] &lt;&gt; null and [Page] &lt;= total_pages) ,  \r\n\u202f \u202f \u202f \u202f \u202f \u202f \u202f \u202f \u202f\/\/ establish the conditions to keep looping through the pages:  \r\n\u202f \u202f \u202f \u202f \u202f \u202f \u202f \u202f \u202f\/\/ loop until finding an empty result \r\n\u202f \u202f \u202f \u202f \u202f \u202f \u202f \u202f \u202feach [Result = try GetDataAPI([Page]+1) otherwise null, Page =[Page]+1 ], \/\/what to do each loop  \r\n\u202f \u202f \u202f \u202f \u202f \u202f \u202f \u202f \u202feach [Result] \/\/append results in the list \r\n), \r\n\u202f \u202f #\"Converted to Table\" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),\/\/convert list to table \r\n\/\/add following steps to convert your data into a table \r\n<\/xmp>\n\t\t\t\t<\/code>\n\t\t\t<\/pre>\n\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-2bdfcb5 elementor-widget elementor-widget-heading\" data-id=\"2bdfcb5\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t<h2 class=\"elementor-heading-title elementor-size-default\">Scenario 2. API uses offset records to paginate \n<\/h2>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-8588ec8 elementor-widget elementor-widget-text-editor\" data-id=\"8588ec8\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t\t<p><span style=\"font-weight: 400;\">Some APIs will not return the number of available records, it will only give us back the current page:\u00a0<\/span><\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-9b72362 elementor-widget elementor-widget-image\" data-id=\"9b72362\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"image.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<img decoding=\"async\" width=\"285\" height=\"330\" src=\"https:\/\/powergi.net\/wp-content\/uploads\/2024\/12\/unnamed-54.png\" class=\"attachment-large size-large wp-image-6405\" alt=\"unnamed (54)\" srcset=\"https:\/\/powergi.net\/wp-content\/uploads\/2024\/12\/unnamed-54.png 285w, https:\/\/powergi.net\/wp-content\/uploads\/2024\/12\/unnamed-54-259x300.png 259w\" sizes=\"(max-width: 285px) 100vw, 285px\">\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-24c3028 elementor-widget elementor-widget-text-editor\" data-id=\"24c3028\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t\t<p><span style=\"font-weight: 400;\">When this is the case, usually APIs will have an \u201coffset\u201d attribute that we can use to extract all available pages. This \u201coffset\u201d parameter will \u201cskip\u201d X number of records to extract the next page\u2019s result.\u00a0<\/span><\/p><p><span style=\"font-weight: 400;\">For example, if we know our API will bring 100 records at a time, we will need to \u201cskip\u201d these 100 records each time until we find less than 100 records. On the first page, our offset will be 0 because we don\u2019t need to skip any record, but once we reach the second page, we need to offset the first 100 records to bring records from 101 to 200 and so on.\u00a0<\/span><\/p><p><span style=\"font-weight: 400;\">\u00a0<\/span><span style=\"font-weight: 400;\">API GET request looks like this:\u00a0<\/span><\/p><div id=\"seology-cta-2\">\t\t<div data-elementor-type=\"section\" data-elementor-id=\"7276\" class=\"elementor elementor-7276\" data-elementor-post-type=\"elementor_library\">\n\t\t\t<div class=\"elementor-element elementor-element-7e3f164 e-con-full e-flex e-con e-parent\" data-id=\"7e3f164\" data-element_type=\"container\" data-e-type=\"container\" data-settings='{\"background_background\":\"classic\",\"jet_parallax_layout_list\":[]}'>\n\t\t<div class=\"elementor-element elementor-element-9078c9f e-con-full e-flex e-con e-child\" data-id=\"9078c9f\" data-element_type=\"container\" data-e-type=\"container\" data-settings='{\"jet_parallax_layout_list\":[]}'>\n\t\t\t\t<div class=\"elementor-element elementor-element-7b99949 elementor-widget elementor-widget-heading\" data-id=\"7b99949\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t<p class=\"elementor-heading-title elementor-size-default\">From vision to execution<\/p>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-8e61db5 elementor-widget elementor-widget-heading\" data-id=\"8e61db5\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t<p class=\"elementor-heading-title elementor-size-default\">Whether you're just starting or scaling automation, we help turn your ideas into impactful solutions.<\/p>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t<div class=\"elementor-element elementor-element-a578907 e-flex e-con-boxed e-con e-child\" data-id=\"a578907\" data-element_type=\"container\" data-e-type=\"container\" data-settings='{\"jet_parallax_layout_list\":[]}'>\n\t\t\t\t\t<div class=\"e-con-inner\">\n\t\t\t\t<div class=\"elementor-element elementor-element-e8c827b elementor-view-stacked elementor-shape-circle elementor-widget elementor-widget-icon\" data-id=\"e8c827b\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"icon.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<div class=\"elementor-icon-wrapper\">\n\t\t\t<a class=\"elementor-icon\" href=\"#ctc_chat\">\n\t\t\t<i aria-hidden=\"true\" class=\"fab fa-whatsapp\"><\/i>\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-d834237 elementor-view-stacked elementor-shape-circle elementor-widget elementor-widget-icon\" data-id=\"d834237\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"icon.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<div class=\"elementor-icon-wrapper\">\n\t\t\t<a class=\"elementor-icon\" href=\"https:\/\/powergi.net\/es\/contact\/\">\n\t\t\t<i aria-hidden=\"true\" class=\"far fa-envelope\"><\/i>\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t<\/div><p><span style=\"font-weight: 400;\">https:\/\/abc.abc.se\/abc -webapi\/v1\/projects?company=SE&amp;limit=100&amp;offset=200\u00a0\u00a0<\/span><\/p><p><span style=\"font-weight: 400;\">\u00a0<\/span><span style=\"font-weight: 400;\">Power Query to extract pages for this API would be as follows:\u00a0<\/span><\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-f1b02b8 elementor-widget elementor-widget-code-highlight\" data-id=\"f1b02b8\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"code-highlight.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<div class=\"prismjs-default copy-to-clipboard \">\n\t\t\t<pre data-line=\"\" class=\"highlight-height language-javascript line-numbers\">\n\t\t\t\t<code readonly class=\"language-javascript\">\n\t\t\t\t\t<xmp>Source = List.Generate( () =&gt; \u202f  \r\n\/\/start with offset = 0 because in the first pull we need to get the first 100 records, send API request with headers, auth and query parameters if needed \r\n\u202f \u202f \u202f \u202f [ Offset = 0 , Result = Json.Document(Web.Contents(\"https:\/\/abc.abc.se\/abc -webapi\/v1\/\", [ \r\n\u202f \u202f \u202f \u202f RelativePath=\"\/projects\", \r\n\u202f \u202f \u202f \u202f Headers =[#\"Content-type\"=\"application\/json\", \r\n\u202f \u202f \u202f \u202f \u202f \u202f \u202f #\"Authorization\"=\"Basic xxxx\"], \r\n\u202f \u202f \u202f \u202f Query=[company=\"SE\",limit=\"100\",offset=\"0\"] \r\n\u202f \u202f \u202f \u202f ])) ], \u202f \r\n\/\/This parameter helps us verify when to stop paginating. If the page has less than 100 records or if it\u2019s empty it will stop. If the page has 100 records it will continue (Mod = 0) \r\n\u202f \u202f \u202f \u202f each Number.Mod([Offset],100) = 0 and not List.IsEmpty([Result]), \u202f  \r\n\/\/Here is where we loop the next time to get the following pages. As soon as Mod = 0 (i.e., the number of records is = 100) Power Query will execute this step until the end of the results \r\n\u202f \u202f \u202f \u202f each [ \u202fOffset = [Offset]+List.Count([Result]) , Result = Json.Document(Web.Contents(\"https:\/\/abc.abc.se\/abc -webapi\/v1\/\", [ \r\n\u202f \u202f \u202f \u202f RelativePath=\"\/projects\", \r\n\u202f \u202f \u202f \u202f Headers =[#\"Content-type\"=\"application\/json\", \r\n\u202f \u202f \u202f \u202f \u202f \u202f \u202f #\"Authorization\"=\"Basic xxxxxx\"], \r\n\u202f \u202f \u202f \u202f Query=[ company =\"SE\",filter=datefilter,limit=\"100\",offset=Number.ToText(Offset)] \r\n\u202f \u202f \u202f \u202f ])) ],  \r\n\/\/append the results to a list \r\n\u202f \u202f \u202f \u202feach [Result] \r\n\u202f \u202f ), \r\n\/\/convert list to table \r\n\u202f \u202f #\"Converted to Table\" = Table.FromList(if List.IsEmpty(Source) then {{}} else Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error) \r\n\/\/add following steps to convert your data into a table\r\n<\/xmp>\n\t\t\t\t<\/code>\n\t\t\t<\/pre>\n\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-13a9b7b elementor-widget elementor-widget-heading\" data-id=\"13a9b7b\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t<h2 class=\"elementor-heading-title elementor-size-default\">Conclusion<\/h2>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-2a0a492 elementor-widget elementor-widget-text-editor\" data-id=\"2a0a492\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t\t<p><span style=\"font-weight: 400;\">APIs will work in different ways but Power Query\u2019s List.Generate() function is amazing and can adjust to multiple scenarios and we can use it to pull data from APIs.\u00a0<\/span><\/p><p><span style=\"font-weight: 400;\">At <\/span><a href=\"https:\/\/powergi.net\/\"><b>Power GI<\/b><\/a><span style=\"font-weight: 400;\">, we understand the challenges of integrating data from multiple sources. Power Query\u2019s capabilities enable you to automate and simplify the data extraction process, making it a valuable tool for business intelligence and reporting tasks. Whether you\u2019re working with Power BI, Excel, or another platform, Power Query ensures efficient and reliable API data handling.<\/span><\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<\/div>\n\t\t","protected":false},"excerpt":{"rendered":"<p>Table of Contents We can use Power Query to pull records from APIs where our data resides, but most of the time each API we connect to will work in different ways: Some APIs might bring all records on the same page Some APIs will limit the number of records you can retrieve Some APIs [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":6401,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"default","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","ast-disable-related-posts":"","theme-transparent-header-meta":"default","adv-header-id-meta":"","stick-header-meta":"default","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"set","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"glossary_letter":"a","footnotes":""},"categories":[155],"tags":[156,161,17],"class_list":["post-6400","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-canvas-apps","tag-canvas-apps","tag-dataverse-connector","tag-power-automate"],"glossary_letter":"a","rank_math_title":"Power Query API pagination for Excel and Power BI - Power GI","rank_math_description":"Master Power Query API pagination for seamless data extraction in Power BI and Excel. Learn step-by-step how to handle page numbers and offset-based APIs.","rank_math_focus_keyword":"power query api pagination","_links":{"self":[{"href":"https:\/\/powergi.net\/es\/wp-json\/wp\/v2\/posts\/6400","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/powergi.net\/es\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/powergi.net\/es\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/powergi.net\/es\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/powergi.net\/es\/wp-json\/wp\/v2\/comments?post=6400"}],"version-history":[{"count":2,"href":"https:\/\/powergi.net\/es\/wp-json\/wp\/v2\/posts\/6400\/revisions"}],"predecessor-version":[{"id":14949,"href":"https:\/\/powergi.net\/es\/wp-json\/wp\/v2\/posts\/6400\/revisions\/14949"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/powergi.net\/es\/wp-json\/wp\/v2\/media\/6401"}],"wp:attachment":[{"href":"https:\/\/powergi.net\/es\/wp-json\/wp\/v2\/media?parent=6400"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/powergi.net\/es\/wp-json\/wp\/v2\/categories?post=6400"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/powergi.net\/es\/wp-json\/wp\/v2\/tags?post=6400"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}