API pagination from ACRA Information on Corporate entities on Data.gov.sg

[kbachova]

Hi,

I am trying to solve the pagination issue I have with the API data from data.gov.sg.

Here is my M code:

let
    Source = Json.Document(Web.Contents("https://data.gov.sg/api/action/datastore_search?resource_id=6b5cbfa7-b502-4ce6-875d-dafff7ff04f2")),
    BaseUrl = "https://data.gov.sg/api/action/datastore_search?resource_id=6b5cbfa7-b502-4ce6-875d-dafff7ff04f2",
    EntitiesPerPage = 100,
 
    GetJson = (Url) =>
        let RawData = Web.Contents(Url),
            Json = Json.Document(RawData)
        in  Json,
    
    GetTotalEntities = () =>
        let 	Json = GetJson(BaseUrl),
            	Result = Json[result],
		Total = Result[total]
        in  Total,

    GetPage = (Index) =>
        let Offset  = "offset=" & Text.From(Index * EntitiesPerPage),
            	Url   = BaseUrl & "&" & Offset,
            	Json  = GetJson(Url),
            	result = Json[result],
    	    	records = result[records]
	in records,

    GetUrl = (Index) =>
        let Offset  = "offset=" & Text.From(Index * EntitiesPerPage),
            Url   = BaseUrl & "&" & Offset
        in  Url,
  
    EntityCount = List.Max({ EntitiesPerPage, GetTotalEntities() }),
    PageCount   = Number.RoundUp(EntityCount / EntitiesPerPage),
    PageIndices = { 0 .. PageCount - 1 },
   
 URLs  = List.Transform(PageIndices, each GetUrl(_)),
 Pages       = List.Transform(PageIndices, each GetPage(_)),
    Entities    = List.Union(Pages),
    #"Converted to Table" = Table.FromList(Entities, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"business_constitution_description", "primary_ssic_description", "primary_user_described_activity", "street_name", "entity_status_description", "paid_up_capital8_ordinary", "postal_code", "paid_up_capital10_preference", "other_address_line1", "paid_up_capital2_others", "name_of_audit_firm3", "address_type", "paid_up_capital9_currency", "annual_return_date", "paid_up_capital6_others", "paid_up_capital6_ordinary", "level_no", "paid_up_capital9_others", "paid_up_capital5_preference", "paid_up_capital7_currency", "paid_up_capital6_preference", "paid_up_capital4_currency", "secondary_ssic_code", "paid_up_capital7_preference", "paid_up_capital1_preference", "paid_up_capital2_preference", "uen", "uen_issue_date", "no_of_officers", "former_entity_name3", "building_name", "no_of_charges", "paid_up_capital8_currency", "paid_up_capital2_ordinary", "paid_up_capital3_others", "paid_up_capital4_others", "secondary_ssic_description", "paid_up_capital10_currency", "paid_up_capital8_others", "paid_up_capital1_others", "paid_up_capital3_currency", "block", "other_address_line2", "entity_name", "account_due_date", "paid_up_capital7_ordinary", "former_entity_name8", "former_entity_name9", "former_entity_name2", "paid_up_capital3_preference", "former_entity_name1", "former_entity_name6", "former_entity_name7", "former_entity_name4", "former_entity_name5", "paid_up_capital4_ordinary", "secondary_user_described_activity", "issuance_agency_id", "former_entity_name14", "former_entity_name15", "former_entity_name10", "registration_incorporation_date", "former_entity_name12", "former_entity_name13", "paid_up_capital2_currency", "paid_up_capital1_currency", "paid_up_capital8_preference", "primary_ssic_code", "name_of_audit_firm5", "name_of_audit_firm4", "name_of_audit_firm1", "paf_constitution_description", "name_of_audit_firm2", "paid_up_capital9_ordinary", "paid_up_capital4_preference", "former_entity_name11", "unit_no", "paid_up_capital5_others", "paid_up_capital6_currency", "paid_up_capital5_currency", "company_type_description", "uen_of_audit_firm3", "uen_of_audit_firm2", "uen_of_audit_firm1", "paid_up_capital10_ordinary", "paid_up_capital10_others", "uen_of_audit_firm4", "paid_up_capital7_others", "uen_of_audit_firm5", "entity_type_description", "paid_up_capital3_ordinary", "paid_up_capital5_ordinary", "paid_up_capital9_preference", "paid_up_capital1_ordinary", "_id"}, {"business_constitution_description", "primary_ssic_description", "primary_user_described_activity", "street_name", "entity_status_description", "paid_up_capital8_ordinary", "postal_code", "paid_up_capital10_preference", "other_address_line1", "paid_up_capital2_others", "name_of_audit_firm3", "address_type", "paid_up_capital9_currency", "annual_return_date", "paid_up_capital6_others", "paid_up_capital6_ordinary", "level_no", "paid_up_capital9_others", "paid_up_capital5_preference", "paid_up_capital7_currency", "paid_up_capital6_preference", "paid_up_capital4_currency", "secondary_ssic_code", "paid_up_capital7_preference", "paid_up_capital1_preference", "paid_up_capital2_preference", "uen", "uen_issue_date", "no_of_officers", "former_entity_name3", "building_name", "no_of_charges", "paid_up_capital8_currency", "paid_up_capital2_ordinary", "paid_up_capital3_others", "paid_up_capital4_others", "secondary_ssic_description", "paid_up_capital10_currency", "paid_up_capital8_others", "paid_up_capital1_others", "paid_up_capital3_currency", "block", "other_address_line2", "entity_name", "account_due_date", "paid_up_capital7_ordinary", "former_entity_name8", "former_entity_name9", "former_entity_name2", "paid_up_capital3_preference", "former_entity_name1", "former_entity_name6", "former_entity_name7", "former_entity_name4", "former_entity_name5", "paid_up_capital4_ordinary", "secondary_user_described_activity", "issuance_agency_id", "former_entity_name14", "former_entity_name15", "former_entity_name10", "registration_incorporation_date", "former_entity_name12", "former_entity_name13", "paid_up_capital2_currency", "paid_up_capital1_currency", "paid_up_capital8_preference", "primary_ssic_code", "name_of_audit_firm5", "name_of_audit_firm4", "name_of_audit_firm1", "paf_constitution_description", "name_of_audit_firm2", "paid_up_capital9_ordinary", "paid_up_capital4_preference", "former_entity_name11", "unit_no", "paid_up_capital5_others", "paid_up_capital6_currency", "paid_up_capital5_currency", "company_type_description", "uen_of_audit_firm3", "uen_of_audit_firm2", "uen_of_audit_firm1", "paid_up_capital10_ordinary", "paid_up_capital10_others", "uen_of_audit_firm4", "paid_up_capital7_others", "uen_of_audit_firm5", "entity_type_description", "paid_up_capital3_ordinary", "paid_up_capital5_ordinary", "paid_up_capital9_preference", "paid_up_capital1_ordinary", "_id"})
in
    #"Expanded Column1"

This is not working - and it looks like it is still loading. So I am assuming that I have overloaded the query and it cannot run properly, because there is too much to process. Could anyone advise how to sort this out?

And my second Question is - this is just to load API query for letter A - as you can see from the code above, the data is listed by 100 and if you examine tha data, you will see that there is 134866 total records just for letter A. And there is a whole alphabet that I was tasked to load, as then I need to apply some transformation on the data. (The table with API links per letter (I added total records column) can be generated using the M code below.) Can anyone help me to rewrite the code in a way so that it brings all rows of all data for all numbers in one query? 

So far I only managed to return first 100 lines of each Alphabet letter separately and then appended them together. I am not very experienced in advanced M, so any help would be superbeneficial!

Thank you!

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pZfZTiY2DEbfhevx4CTOVqmquu/7XoRGjp0M3JQR/98+f23UB4iUKwQEziHx8nF3d/PhzaubMrKMxRVGxggks0CrWUF5rVXXQlrRjj1cr+8u793eKl/59dunf19f3t7yu8dbluvj098vX75cn57nm8vkZ3n44Hlenv55lvnmUd/fQty/urv5yEBptJz6GJAl2uFMCEwRoRUdRNQCajr02UK4z8cG6pRb6HUADSpADSdw7gUS6VgrFm0qhz5bCPf5xN8rMI01CYosO8xs3lIFMM+chqqGdvxeOwj3+dRAo81SJU2Irbq8VugJB5SKcQxlCeX0frYQ7vOZgVi5c1gMErQBBZdH+zN6iymRBFnp9H62EO7zuYEkLx3cJ+RpV0h1JOiLrBdUcyyFE/dx6LOFcJ8vDGRFnimlAtLnssvkZoeL1V7FnJi9JU77awvhPl8aKGZULsG+m7FbB4QMjI2A+gg92e9pvA59thDu85X7TGo2jwpoCnaZ9gl0TAlq1YRWcJnTPPXZQbjP1+5TulLvC1qoyQ7zhNFzgUBah0a0j3Tqs4Nwn28MtOaKOKz7uA17XJYMo61uvYmMWWtOlQ99thDu863X8xjNZoPJ52LDnIc1o413iIiLW8ytaj2t5x2E+3zn+6vFFqc9aeMYrMRSBO6lwZpInMacpHi6v3YQ7vO91w/1VmZdEKpfJuYGHG045NFDmTGktfJp/ewg3OcH3191ttpHhEa2YSjZPfaOBGlxEkTSOU7rZwvhPj+++NRYG3VY5M0417KYIgiam9hQt43cTvt9C+E+P/m+KKVkrNP6cFqxdVsz3IpArKFLoGEL73SfbiHc52fvr5xLSNLA95oNz2zDagpB5BnyqL0xnc6fLYT7/GKgkNcSiyAWTqR7GChgwgSypM4+kJnKoc8Wwn1+9fyTU0JLk5DRw+0iG1Ya7GdTFKqriq7Tet5CuM9vnn8qoWhVi2xo4cTGEwxR8YprGtlGaAun+WcH4T6/v7zXoLQoQkjdil9SAC5YrfYk4iLSIKc+Wwj3+cPzPHoEsRE1Fo3/h/mcCpkzI/LCEtppnt9BuM+fBqISO6WoUKztbFi1YMvFElxQjcpjoBz7bCHc5y8DTba8WMOyw178yHaZQawDzNv+IZHAcrq/thAv++v6MJ8vLtVqipMTsArbC1vi7pYSII6OUm3Z2J2eSu0g7u//Aw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Letter = _t, #"Resource ID" = _t, URL = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Letter", type text}, {"Resource ID", type text}, {"URL", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "GetData", each Json.Document(Web.Contents([URL]))),
    #"Expanded GetData" = Table.ExpandRecordColumn(#"Added Custom", "GetData", {"result"}, {"result"}),
    #"Expanded result" = Table.ExpandRecordColumn(#"Expanded GetData", "result", {"records", "total"}, {"records", "total"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded result",{"Letter", "URL", "total"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Other Columns",{{"total", Int64.Type}}),
    PageCount = Number.RoundUp([total]/100),
    PageIndices = { 0 .. PageCount - 1 }
    
in
    #"Changed Type1"

[kbachova]

Here is the .pbit that I am using for this: url 

[kbachova]

However, then I use this data to create 5 new queries which are manipulated.

And then I merged these 5 queries into the main DATA query

let
    Source = APIalldata,
    #"Removed Other Columns" = Table.SelectColumns(#"Source",{"block", "building_name", "business_constitution_description", "entity_name", "entity_status_description", "entity_type_description", "former_entity_name1", "former_entity_name10", "former_entity_name11", "former_entity_name12", "former_entity_name13", "former_entity_name14", "former_entity_name15", "former_entity_name2", "former_entity_name3", "former_entity_name4", "former_entity_name5", "former_entity_name6", "former_entity_name7", "former_entity_name8", "former_entity_name9", "level_no", "name_of_audit_firm1", "name_of_audit_firm2", "name_of_audit_firm3", "name_of_audit_firm4", "name_of_audit_firm5", "paid_up_capital1_currency", "paid_up_capital1_ordinary", "paid_up_capital1_others", "paid_up_capital1_preference", "paid_up_capital10_currency", "paid_up_capital10_ordinary", "paid_up_capital10_others", "paid_up_capital10_preference", "paid_up_capital2_currency", "paid_up_capital2_ordinary", "paid_up_capital2_others", "paid_up_capital2_preference", "paid_up_capital3_currency", "paid_up_capital3_ordinary", "paid_up_capital3_others", "paid_up_capital3_preference", "paid_up_capital4_currency", "paid_up_capital4_ordinary", "paid_up_capital4_others", "paid_up_capital4_preference", "paid_up_capital5_currency", "paid_up_capital5_ordinary", "paid_up_capital5_others", "paid_up_capital5_preference", "paid_up_capital6_currency", "paid_up_capital6_ordinary", "paid_up_capital6_others", "paid_up_capital6_preference", "paid_up_capital7_currency", "paid_up_capital7_ordinary", "paid_up_capital7_others", "paid_up_capital7_preference", "paid_up_capital8_currency", "paid_up_capital8_ordinary", "paid_up_capital8_others", "paid_up_capital8_preference", "paid_up_capital9_currency", "paid_up_capital9_ordinary", "paid_up_capital9_others", "paid_up_capital9_preference", "postal_code", "primary_ssic_description", "primary_user_described_activity", "registration_incorporation_date", "secondary_ssic_description", "secondary_user_described_activity", "street_name", "uen", "unit_no"}),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Other Columns","na",null,Replacer.ReplaceValue,{"block", "building_name", "business_constitution_description", "entity_name", "entity_status_description", "entity_type_description", "former_entity_name1", "former_entity_name10", "former_entity_name11", "former_entity_name12", "former_entity_name13", "former_entity_name14", "former_entity_name15", "former_entity_name2", "former_entity_name3", "former_entity_name4", "former_entity_name5", "former_entity_name6", "former_entity_name7", "former_entity_name8", "former_entity_name9", "level_no", "name_of_audit_firm1", "name_of_audit_firm2", "name_of_audit_firm3", "name_of_audit_firm4", "name_of_audit_firm5", "paid_up_capital1_currency", "paid_up_capital1_ordinary", "paid_up_capital1_others", "paid_up_capital1_preference", "paid_up_capital10_currency", "paid_up_capital10_ordinary", "paid_up_capital10_others", "paid_up_capital10_preference", "paid_up_capital2_currency", "paid_up_capital2_ordinary", "paid_up_capital2_others", "paid_up_capital2_preference", "paid_up_capital3_currency", "paid_up_capital3_ordinary", "paid_up_capital3_others", "paid_up_capital3_preference", "paid_up_capital4_currency", "paid_up_capital4_ordinary", "paid_up_capital4_others", "paid_up_capital4_preference", "paid_up_capital5_currency", "paid_up_capital5_ordinary", "paid_up_capital5_others", "paid_up_capital5_preference", "paid_up_capital6_currency", "paid_up_capital6_ordinary", "paid_up_capital6_others", "paid_up_capital6_preference", "paid_up_capital7_currency", "paid_up_capital7_ordinary", "paid_up_capital7_others", "paid_up_capital7_preference", "paid_up_capital8_currency", "paid_up_capital8_ordinary", "paid_up_capital8_others", "paid_up_capital8_preference", "paid_up_capital9_currency", "paid_up_capital9_ordinary", "paid_up_capital9_others", "paid_up_capital9_preference", "postal_code", "primary_ssic_description", "primary_user_described_activity", "registration_incorporation_date", "secondary_ssic_description", "secondary_user_described_activity", "street_name", "uen", "unit_no"}),
    #"Renamed Columns" = Table.RenameColumns(#"Replaced Value",{{"entity_name", "Entity Name"}, {"uen", "UEN"}, {"registration_incorporation_date", "Date of Incorporation"}}),
    #"Merged Columns" = Table.CombineColumns(#"Renamed Columns",{"entity_type_description","business_constitution_description"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Business Structure"),
    #"Renamed Columns1" = Table.RenameColumns(#"Merged Columns",{{"entity_status_description", "Entity Status"}, {"primary_ssic_description", "Primary Business Category"}, {"primary_user_described_activity", "Primary Business Detail"}}),
    #"Merged Columns4" = Table.CombineColumns(#"Renamed Columns1",{"unit_no", "level_no"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"level_no-unit_no"),
    #"Replaced Value1" = Table.ReplaceValue(#"Merged Columns4","-","",Replacer.ReplaceValue,{"level_no-unit_no"}),
    #"Added Prefix" = Table.TransformColumns(#"Replaced Value1", {{"postal_code", each "SINGAPORE " & _, type text}}),
    #"Merged Columns1" = Table.CombineColumns(#"Added Prefix",{"building_name", "block", "street_name", "level_no-unit_no", "postal_code"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Registered Address"),
    #"Trimmed Text" = Table.TransformColumns(#"Merged Columns1",{{"Registered Address", Text.Trim, type text}}),
    #"Renamed Columns2" = Table.RenameColumns(#"Trimmed Text",{{"secondary_ssic_description", "Secondary Business Category"}, {"secondary_user_described_activity", "Secondary Business Detail"}}),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Renamed Columns2", {{"Date of Incorporation", each Text.BeforeDelimiter(_, "T"), type text}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Extracted Text Before Delimiter",{{"Date of Incorporation", type date}}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Changed Type",{"Business Structure", "Entity Name", "Entity Status", "Registered Address", "Primary Business Category", "Primary Business Detail", "Date of Incorporation", "Secondary Business Category", "Secondary Business Detail", "UEN"}),
    #"Merged Queries" = Table.NestedJoin(#"Removed Other Columns1", {"UEN"}, #"FORMER ENTITY NAME", {"uen"}, "FORMER ENTITY NAME", JoinKind.LeftOuter),
    #"Added Custom" = Table.AddColumn(#"Merged Queries", "Former Entity Name", each Table.Column([FORMER ENTITY NAME],"Value")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Former Entity Name", each Text.Combine(List.Transform(_, Text.From), ", "), type text}),
    #"Merged Queries1" = Table.NestedJoin(#"Extracted Values", {"UEN"}, #"AUDIT FIRM", {"uen"}, "AUDIT FIRM", JoinKind.LeftOuter),
    #"Added Custom1" = Table.AddColumn(#"Merged Queries1", "Audit Firm", each Table.Column([AUDIT FIRM],"Value")),
    #"Extracted Values1" = Table.TransformColumns(#"Added Custom1", {"Audit Firm", each Text.Combine(List.Transform(_, Text.From), ", "), type text}),
    #"Merged Queries2" = Table.NestedJoin(#"Extracted Values1", {"UEN"}, #"PAID UP CAPITAL (ORDINARY)", {"uen"}, "PAID UP CAPITAL (ORDINARY)", JoinKind.LeftOuter),
    #"Added Custom2" = Table.AddColumn(#"Merged Queries2", "Paid up Capital (Ordinary)", each Table.Column([#"PAID UP CAPITAL (ORDINARY)"],"Value")),
    #"Extracted Values2" = Table.TransformColumns(#"Added Custom2", {"Paid up Capital (Ordinary)", each Text.Combine(List.Transform(_, Text.From), ", "), type text}),
    #"Merged Queries3" = Table.NestedJoin(#"Extracted Values2", {"UEN"}, #"PAID UP CAPITAL (PREFERNECE)", {"uen"}, "PAID UP CAPITAL (PREFERNECE)", JoinKind.LeftOuter),
    #"Added Custom3" = Table.AddColumn(#"Merged Queries3", "Paid up Capital (Preference)", each Table.Column([#"PAID UP CAPITAL (PREFERNECE)"],"Value")),
    #"Extracted Values3" = Table.TransformColumns(#"Added Custom3", {"Paid up Capital (Preference)", each Text.Combine(List.Transform(_, Text.From), ", "), type text}),
    #"Merged Queries4" = Table.NestedJoin(#"Extracted Values3", {"Registered Address"}, #"BUSINESS IN THE SAME ADDRESS", {"Registered Address"}, "BUSINESS IN THE SAME ADDRESS", JoinKind.LeftOuter),
    #"Added Custom4" = Table.AddColumn(#"Merged Queries4", "Custom", each Table.Column([BUSINESS IN THE SAME ADDRESS],"Entity Name")),
    #"Extracted Values4" = Table.TransformColumns(#"Added Custom4", {"Custom", each Text.Combine(List.Transform(_, Text.From), "#(cr)#(lf)"), type text}),
    #"Added Conditional Column" = Table.AddColumn(#"Extracted Values4", "Business in the same address", each if [Custom] = [Entity Name] then null else [Custom]),
    #"Removed Other Columns2" = Table.SelectColumns(#"Added Conditional Column",{"Business Structure", "Entity Name", "Entity Status", "Registered Address", "Primary Business Category", "Primary Business Detail", "Date of Incorporation", "Secondary Business Category", "Secondary Business Detail", "UEN", "Former Entity Name", "Audit Firm", "Paid up Capital (Ordinary)", "Paid up Capital (Preference)", "Business in the same address"})
in
    #"Removed Other Columns2"

However, it feels like this query is massive and it won't load... or my internet connection is slow to deal with this. Could anyone advise what I could do to make it work, please?

Thank you!

[kbachova]

Thank you both for resources. I managed to get it somewhat working by doing this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pZfZTiY2DEbfhevx4CTOVqmquu/7XoRGjp0M3JQR/98+f23UB4iUKwQEziHx8nF3d/PhzaubMrKMxRVGxggks0CrWUF5rVXXQlrRjj1cr+8u793eKl/59dunf19f3t7yu8dbluvj098vX75cn57nm8vkZ3n44Hlenv55lvnmUd/fQty/urv5yEBptJz6GJAl2uFMCEwRoRUdRNQCajr02UK4z8cG6pRb6HUADSpADSdw7gUS6VgrFm0qhz5bCPf5xN8rMI01CYosO8xs3lIFMM+chqqGdvxeOwj3+dRAo81SJU2Irbq8VugJB5SKcQxlCeX0frYQ7vOZgVi5c1gMErQBBZdH+zN6iymRBFnp9H62EO7zuYEkLx3cJ+RpV0h1JOiLrBdUcyyFE/dx6LOFcJ8vDGRFnimlAtLnssvkZoeL1V7FnJi9JU77awvhPl8aKGZULsG+m7FbB4QMjI2A+gg92e9pvA59thDu85X7TGo2jwpoCnaZ9gl0TAlq1YRWcJnTPPXZQbjP1+5TulLvC1qoyQ7zhNFzgUBah0a0j3Tqs4Nwn28MtOaKOKz7uA17XJYMo61uvYmMWWtOlQ99thDu863X8xjNZoPJ52LDnIc1o413iIiLW8ytaj2t5x2E+3zn+6vFFqc9aeMYrMRSBO6lwZpInMacpHi6v3YQ7vO91w/1VmZdEKpfJuYGHG045NFDmTGktfJp/ewg3OcH3191ttpHhEa2YSjZPfaOBGlxEkTSOU7rZwvhPj+++NRYG3VY5M0417KYIgiam9hQt43cTvt9C+E+P/m+KKVkrNP6cFqxdVsz3IpArKFLoGEL73SfbiHc52fvr5xLSNLA95oNz2zDagpB5BnyqL0xnc6fLYT7/GKgkNcSiyAWTqR7GChgwgSypM4+kJnKoc8Wwn1+9fyTU0JLk5DRw+0iG1Ya7GdTFKqriq7Tet5CuM9vnn8qoWhVi2xo4cTGEwxR8YprGtlGaAun+WcH4T6/v7zXoLQoQkjdil9SAC5YrfYk4iLSIKc+Wwj3+cPzPHoEsRE1Fo3/h/mcCpkzI/LCEtppnt9BuM+fBqISO6WoUKztbFi1YMvFElxQjcpjoBz7bCHc5y8DTba8WMOyw178yHaZQawDzNv+IZHAcrq/thAv++v6MJ8vLtVqipMTsArbC1vi7pYSII6OUm3Z2J2eSu0g7u//Aw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Letter = _t, #"Resource ID" = _t, URL = _t]),
    #"Invoked Custom Function" = Table.AddColumn(Source, "Fetching paginated data (function)", each #"Fetching paginated data (function)"([URL])),
    #"Removed Other Columns" = Table.SelectColumns(#"Invoked Custom Function",{"Fetching paginated data (function)"}),
    #"Expanded Fetching paginated data (function)" = Table.ExpandTableColumn(#"Removed Other Columns", "Fetching paginated data (function)", {"business_constitution_description", "primary_ssic_description", "primary_user_described_activity", "street_name", "entity_status_description", "paid_up_capital8_ordinary", "postal_code", "paid_up_capital10_preference", "other_address_line1", "paid_up_capital2_others", "name_of_audit_firm3", "address_type", "paid_up_capital9_currency", "annual_return_date", "paid_up_capital6_others", "paid_up_capital6_ordinary", "level_no", "paid_up_capital9_others", "paid_up_capital5_preference", "paid_up_capital7_currency", "paid_up_capital6_preference", "paid_up_capital4_currency", "secondary_ssic_code", "paid_up_capital7_preference", "paid_up_capital1_preference", "paid_up_capital2_preference", "uen", "uen_issue_date", "no_of_officers", "former_entity_name3", "building_name", "no_of_charges", "paid_up_capital8_currency", "paid_up_capital2_ordinary", "paid_up_capital3_others", "paid_up_capital4_others", "secondary_ssic_description", "paid_up_capital10_currency", "paid_up_capital8_others", "paid_up_capital1_others", "paid_up_capital3_currency", "block", "other_address_line2", "entity_name", "account_due_date", "paid_up_capital7_ordinary", "former_entity_name8", "former_entity_name9", "former_entity_name2", "paid_up_capital3_preference", "former_entity_name1", "former_entity_name6", "former_entity_name7", "former_entity_name4", "former_entity_name5", "paid_up_capital4_ordinary", "secondary_user_described_activity", "issuance_agency_id", "former_entity_name14", "former_entity_name15", "former_entity_name10", "registration_incorporation_date", "former_entity_name12", "former_entity_name13", "paid_up_capital2_currency", "paid_up_capital1_currency", "paid_up_capital8_preference", "primary_ssic_code", "name_of_audit_firm5", "name_of_audit_firm4", "name_of_audit_firm1", "paf_constitution_description", "name_of_audit_firm2", "paid_up_capital9_ordinary", "paid_up_capital4_preference", "former_entity_name11", "unit_no", "paid_up_capital5_others", "paid_up_capital6_currency", "paid_up_capital5_currency", "company_type_description", "uen_of_audit_firm3", "uen_of_audit_firm2", "uen_of_audit_firm1", "paid_up_capital10_ordinary", "paid_up_capital10_others", "uen_of_audit_firm4", "paid_up_capital7_others", "uen_of_audit_firm5", "entity_type_description", "paid_up_capital3_ordinary", "paid_up_capital5_ordinary", "paid_up_capital9_preference", "paid_up_capital1_ordinary", "_id"}, {"business_constitution_description", "primary_ssic_description", "primary_user_described_activity", "street_name", "entity_status_description", "paid_up_capital8_ordinary", "postal_code", "paid_up_capital10_preference", "other_address_line1", "paid_up_capital2_others", "name_of_audit_firm3", "address_type", "paid_up_capital9_currency", "annual_return_date", "paid_up_capital6_others", "paid_up_capital6_ordinary", "level_no", "paid_up_capital9_others", "paid_up_capital5_preference", "paid_up_capital7_currency", "paid_up_capital6_preference", "paid_up_capital4_currency", "secondary_ssic_code", "paid_up_capital7_preference", "paid_up_capital1_preference", "paid_up_capital2_preference", "uen", "uen_issue_date", "no_of_officers", "former_entity_name3", "building_name", "no_of_charges", "paid_up_capital8_currency", "paid_up_capital2_ordinary", "paid_up_capital3_others", "paid_up_capital4_others", "secondary_ssic_description", "paid_up_capital10_currency", "paid_up_capital8_others", "paid_up_capital1_others", "paid_up_capital3_currency", "block", "other_address_line2", "entity_name", "account_due_date", "paid_up_capital7_ordinary", "former_entity_name8", "former_entity_name9", "former_entity_name2", "paid_up_capital3_preference", "former_entity_name1", "former_entity_name6", "former_entity_name7", "former_entity_name4", "former_entity_name5", "paid_up_capital4_ordinary", "secondary_user_described_activity", "issuance_agency_id", "former_entity_name14", "former_entity_name15", "former_entity_name10", "registration_incorporation_date", "former_entity_name12", "former_entity_name13", "paid_up_capital2_currency", "paid_up_capital1_currency", "paid_up_capital8_preference", "primary_ssic_code", "name_of_audit_firm5", "name_of_audit_firm4", "name_of_audit_firm1", "paf_constitution_description", "name_of_audit_firm2", "paid_up_capital9_ordinary", "paid_up_capital4_preference", "former_entity_name11", "unit_no", "paid_up_capital5_others", "paid_up_capital6_currency", "paid_up_capital5_currency", "company_type_description", "uen_of_audit_firm3", "uen_of_audit_firm2", "uen_of_audit_firm1", "paid_up_capital10_ordinary", "paid_up_capital10_others", "uen_of_audit_firm4", "paid_up_capital7_others", "uen_of_audit_firm5", "entity_type_description", "paid_up_capital3_ordinary", "paid_up_capital5_ordinary", "paid_up_capital9_preference", "paid_up_capital1_ordinary", "_id"})
in
    #"Expanded Fetching paginated data (function)"

So this works, and loads all the data.

[v-eachen-msft]

Hi @kbachova ,

You could refer to blogs below to try different ways to scrape multiple pages using power query:

https://www.myonlinetraininghub.com/scrape-data-multiple-web-pages-power-query

https://adatis.co.uk/Loop-through-Multiple-Web-Pages-using-Power-Query/

[amitchandak]

@kbachova , Refer if these can help

https://stackoverflow.com/questions/46904641/how-to-get-paginated-data-from-api-in-power-bi

https://www.youtube.com/watch?v=vhr4w5G8bRA

https://docs.microsoft.com/en-us/power-query/handlingpaging