Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Below is the code i am trying to connect paginated API in data flow but while saving the dataflow I am getting the error as given below.
let
header = [#"client_id" = "", #"client_secret" = "", #"Content-Type"="application/json"],
BaseUrl = "*********************************************************************",
EntitiesPerPage = 20000,
GetJson = (Url) =>
let
RawData = Web.Contents(Url, [Timeout=#duration(0,0,60,0), Headers=header]),
Json = Json.Document(RawData)
in Json,
GetPage = (Index) =>
let
Url = BaseUrl & "operationtype=count&pagenumber=" & Text.From(Index) & "&pagesize=" & Text.From(EntitiesPerPage),
Json = GetJson(Url)
in Json,
PageCount = 2,
PageIndices = { 1 .. PageCount - 1 },
Pages = List.Transform(PageIndices, each GetPage(_)),
Table = Table.FromList(Pages, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
BufferMyTable = Table.Buffer(Table),
#"Expanded Column1" = Table.ExpandRecordColumn(BufferMyTable, "Column1", {"page_count"}, {"page_count"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1", {{"page_count", Int64.Type}}),
NumberOfPages = #"Changed Type"{0}[page_count],
GetJson_contents = (Url) =>
let
RawData = Web.Contents(Url, [Timeout=#duration(0,0,60,0), Headers=header]),
Json = Json.Document(RawData)
in Json,
GetPage_contents = (Index) =>
let
Url = BaseUrl & "operationtype=page&pagenumber=" & Text.From(Index) & "&pagesize=" & Text.From(EntitiesPerPage),
Json = GetJson_contents(Url)
in Json,
PageCount_contents = Number.RoundUp(NumberOfPages),
PageIndices_contents = { 1 .. PageCount_contents - 0 },
Pages_contents = List.Transform(PageIndices_contents, each GetPage_contents(_)),
Table_contents = Table.FromList(Pages_contents, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
BufferMyTable_contents = Table.Buffer(Table_contents),
#"Expanded Column1_contents" = Table.ExpandListColumn(BufferMyTable_contents, "Column1"),
#"Expanded Column2_contents" = Table.ExpandRecordColumn(#"Expanded Column1_contents", "Column1", {"BATCHNO", "TSA", "Units", "Particulate", "Alert Limit", "Sample Zone", "Result", "Alert", "HIGHA", "INVENTORYID", "Action", "Rep", "Class", "Sample No", "SAMPDATE", "Static", "Test", "Date Sampled", "Analyte", "DEPT", "Deviation No", "Room", "Site", "MATERIAL", "Sampling Point", "EMKey", "Action Limit", "ANALTYPE", "Status"}, {"BATCHNO", "TSA", "Units", "Particulate", "Alert Limit", "Sample Zone", "Result", "Alert", "HIGHA", "INVENTORYID", "Action", "Rep", "Class", "Sample No", "SAMPDATE", "Static", "Test", "Date Sampled", "Analyte", "DEPT", "Deviation No", "Room", "Site", "MATERIAL", "Sampling Point", "EMKey", "Action Limit", "ANALTYPE", "Status"}),
#"Transform columns" = Table.TransformColumnTypes(#"Expanded Column2_contents", {{"BATCHNO", type text}, {"TSA", type text}, {"Units", type text}, {"Particulate", type text}, {"Alert Limit", type text}, {"Sample Zone", type text}, {"Result", type text}, {"Alert", type text}, {"HIGHA", type text}, {"INVENTORYID", type text}, {"Action", type text}, {"Rep", type text}, {"Class", type text}, {"Sample No", type text}, {"SAMPDATE", type text}, {"Static", type text}, {"Test", type text}, {"Date Sampled", type text}, {"Analyte", type text}, {"DEPT", type text}, {"Deviation No", type text}, {"Room", type text}, {"Site", type text}, {"MATERIAL", type text}, {"Sampling Point", type text}, {"EMKey", type text}, {"Action Limit", type text}, {"ANALTYPE", type text}, {"Status", type text}}),
#"Replace errors" = Table.ReplaceErrorValues(#"Transform columns", {{"BATCHNO", null}, {"TSA", null}, {"Units", null}, {"Particulate", null}, {"Alert Limit", null}, {"Sample Zone", null}, {"Result", null}, {"Alert", null}, {"HIGHA", null}, {"INVENTORYID", null}, {"Action", null}, {"Rep", null}, {"Class", null}, {"Sample No", null}, {"SAMPDATE", null}, {"Static", null}, {"Test", null}, {"Date Sampled", null}, {"Analyte", null}, {"DEPT", null}, {"Deviation No", null}, {"Room", null}, {"Site", null}, {"MATERIAL", null}, {"Sampling Point", null}, {"EMKey", null}, {"Action Limit", null}, {"ANALTYPE", null}, {"Status", null}})
in
#"Replace errors"
I think I know where the problem is. In the two functions I used:
GetJson = (Url) =>
let
Options = [Headers = header, Timeout = #duration(0, 0, 60, 0)],
RawData = Web.Contents(Url, Options),
Json = Json.Document(RawData)
in
Json
GetPage = (Index) =>
let
Url = BaseUrl
& RelativePath
& "operationtype=count&pagenumber="
& Text.From(Index)
& "&pagesize="
& Text.From(EntitiesPerPage),
Json = GetJson(Url)
in
Json
Kindly review and help me if any thing wrong i am doing or something majorly wrong with the code
Don't specify your Url like that. Use RelativePath and Query properties of the Web.Contents call.
User | Count |
---|---|
5 | |
4 | |
4 | |
2 | |
2 |
User | Count |
---|---|
8 | |
6 | |
4 | |
4 | |
4 |