Un usuario Pregunto ✅
kbachova
Hola,
Estoy tratando de resolver el problema de paginación que tengo con los datos de la API de data.gov.sg.
Aquí está mi código M:
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"Esto no funciona, y parece que todavía se está cargando. Así que asumo que he sobrecargado la consulta y no puede ejecutarse correctamente porque hay demasiado que procesar. ¿Alguien podría aconsejarme cómo solucionar esto?
Y mi segunda pregunta es: esto es solo para cargar la consulta API para la letra A, como puede ver en el código anterior, los datos se enumeran por 100 y si examina los datos, verá que hay 134866 registros totales solo para letra A. Y hay un alfabeto completo que se me encargó cargar, ya que luego necesito aplicar alguna transformación en los datos. (La tabla con enlaces API por letra (agregué la columna de registros totales) se puede generar usando el código M a continuación). ¿Alguien puede ayudarme a reescribir el código de manera que traiga todas las filas de todos los datos para todos los números en uno? ¿consulta?
Hasta ahora solo logré devolver las primeras 100 líneas de cada letra del alfabeto por separado y luego las añadí juntas. No tengo mucha experiencia en M avanzado, ¡así que cualquier ayuda sería muy beneficiosa!
¡Gracias!
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
Aquí está el .pbit que estoy usando para esto: url
kbachova
Sin embargo, luego uso estos datos para crear 5 nuevas consultas que se manipulan.
Y luego fusioné estas 5 consultas en la consulta principal de DATOS
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"Sin embargo, parece que esta consulta es masiva y no se carga… o mi conexión a Internet es lenta para manejar esto. ¿Alguien podría aconsejarme qué podría hacer para que funcione, por favor?
¡Gracias!
kbachova
Gracias a ambos por los recursos. Me las arreglé para que funcione un poco haciendo esto:
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)"Entonces esto funciona y carga todos los datos.
v-eachen-msft
Hola @kbachova,
Puede consultar los blogs a continuación para probar diferentes formas de raspar varias páginas utilizando 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/
amichandak
@kbachova, consulte si esto puede ayudar
https://stackoverflow.com/questions/46904641/how-to-get-paginated-data-from-api-in-power-bi
https://docs.microsoft.com/en-us/power-query/handlingpaging
