Hi,
I have been working on developing formulas to import all of the records from our online database (Web- Basic Authentication)
The standard source link EG: http://api.company.com/table/tablename draws 200 records (eg 200 records are available per page/API call)
EG:
let
Source = Json.Document(Web.Contents("https://api.company.com/table/tablename"))
in
Source
The formula below allows me to get all the data but because it's "dynamic" it can't be refreshed online.
How can I import all the records from each table and still have the ability to refresh online.
Final table
let
Source = #"Paginate Base Query"(10000, 0, "id"),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"Top", "Skip", "Orderby", "Table"}, {"Top", "Skip", "Orderby", "Table"}),
#"Filtered Hidden Files3" = Table.SelectRows(#"Expanded Column1", each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function3" = Table.AddColumn(#"Filtered Hidden Files3", "Transform File (38)", each #"Transform File (38)"([Table])),
in
#"Invoke Custom Function3"
Transform File (38)
let
Source = (Parameter16 as binary) => let
Source = Json.Document(Parameter16),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"name", "clientid", "extendedvalues"}),
#"Expanded extendedvalues" = Table.ExpandRecordColumn(#"Expanded Column1", "extendedvalues", {"clientid", extendedvalues.clientid"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded extendedvalues",{{"name", type text}, {"clientid", Int64.Type}})
in
#"Changed Type"
in
Source
Paginate Base Query
(top_rows_to_return as number, number_of_rows_to_skips as number,column_to_order_by as text)=>
let
Source = List.Generate(()=>
[Top = top_rows_to_return, Skip = number_of_rows_to_skips,Orderby = column_to_order_by, Table = #"OffsetBase Query" (Top,Skip,Orderby)],
each [Skip] <= #"Table Name count",
each [Top = [Top], Skip = [Skip] + top_rows_to_return,Orderby = [Orderby], Table = #"OffsetBase Query"(Top, Skip,Orderby)])
in
Source
#"OffsetBase Query"
(top_number as number,skip_number as number,orderby as text)=>
let
Source = Web.Contents("https://api.company.com/table/tablename?&$top="&Number.ToText(top_number)&"&$skip="&Number.ToText(skip_number)&"&$orderby="&orderby)
in
Source
#"Table Name count"
= Total Number of records
