Pull data from API with multiple pages to refresh on Power Service

[Anonymous]

Hi, 

I am trying to pull an API with multiple pages, I was able to create the reports on desktop. However, when I published to web service it will not allow me to create a schedule or to refresh it online because of the dynamic way I am pulling the pages. Is there anyone to pull the data without using the dynamic approach?

here is the query I am using 
Report Pages:
let
Source = Json.Document(Web.Contents("https:/XXXXX?view=list", [Headers=[Authorization="Bearer XXXXX", Accept="*/*"]])),
#"Converted to Table" = Record.ToTable(Source),
Value = #"Converted to Table"{0}[Value],
Value1 = Value{0},
#"Converted to Table1" = Record.ToTable(Value1),
#"Transposed Table" = Table.Transpose(#"Converted to Table1"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"total_pages", Int64.Type}}),
total_pages = #"Changed Type"{0}[total_pages],
Custom1 = List.Numbers(1,total_pages,1),
#"Converted to Table2" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Changed Type1" = Table.TransformColumnTypes(#"Converted to Table2",{{"Column1", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Column1", "page"}})
in
#"Renamed Columns"
Data Function:
let
Source = Json.Document(Web.Contents("https:/XXXXX?view=list", [Headers=[Authorization="Bearer XXXXX", Accept="*/*"]])),
#"Converted to Table" = Record.ToTable(Source),
Value = #"Converted to Table"{0}[Value],
Value1 = Value{0},
#"Converted to Table1" = Record.ToTable(Value1),
#"Transposed Table" = Table.Transpose(#"Converted to Table1"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"total_pages", Int64.Type}}),
total_pages = #"Changed Type"{0}[total_pages],
Custom1 = List.Numbers(1,total_pages,1),
#"Converted to Table2" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Changed Type1" = Table.TransformColumnTypes(#"Converted to Table2",{{"Column1", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Column1", "page"}})
in
#"Renamed Columns"
Dataset:

let
Source = Json.Document(Web.Contents("https:/XXXXX?view=list", [Headers=[Authorization="Bearer XXXXX", Accept="*/*"]])),
#"Converted to Table" = Record.ToTable(Source),
Value = #"Converted to Table"{0}[Value],
Value1 = Value{0},
#"Converted to Table1" = Record.ToTable(Value1),
#"Transposed Table" = Table.Transpose(#"Converted to Table1"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"total_pages", Int64.Type}}),
total_pages = #"Changed Type"{0}[total_pages],
Custom1 = List.Numbers(1,total_pages,1),
#"Converted to Table2" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Changed Type1" = Table.TransformColumnTypes(#"Converted to Table2",{{"Column1", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Column1", "page"}}),
#"Invoked Custom Function" = Table.AddColumn(#"Renamed Columns", "Data", each #"Data function"([page]))

in

#"Invoked Custom Function"

[Anonymous]

@lbendlin 
here is the code without the relative path part

(page as text)=>

let

Source = Json.Document(Web.ContentsWeb.Contents("https:/XXXXX?view=list" &page, [Headers=[Authorization="Bearer XXXXX", Accept="*/*"]])),

#"Converted to Table" = Record.ToTable(Source),

Value = #"Converted to Table"{2}[Value],

#"Converted to Table1" = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

#"Renamed Columns" = Table.RenameColumns(#"Converted to Table1",{{"Column1", "rows"}}),

#"Added Index" = Table.AddIndexColumn(#"Renamed Columns", "Index", 0, 1, Int64.Type),

#"Expanded row" = Table.ExpandListColumn(#"Added Index", "rows"),

#"Expanded row1" = Table.ExpandRecordColumn(#"Expanded row", "rows", {"plain_value"}, {"rows.plain_value"}),

#"Trimmed Text" = Table.TransformColumns(#"Expanded row1",{{"rows.plain_value", Text.Trim, type text}}),

#"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{"rows.plain_value", Text.Clean, type text}}),

#"Replaced Value" = Table.ReplaceValue(#"Cleaned Text",null,"EMPTY",Replacer.ReplaceValue,{"rows.plain_value"}),

#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","","EMPTY",Replacer.ReplaceValue,{"rows.plain_value"}),

#"Grouped Rows" = Table.Group(#"Replaced Value1", {"Index"}, {{"Value", each Text.Combine([rows.plain_value], ";*"), type nullable text}}),

#"Split Column by Delimiter" = Table.SplitColumn(#"Grouped Rows", "Value", Splitter.SplitTextByDelimiter(";*", QuoteStyle.None), {"Value.1", "Value.2", "Value.3", "Value.4", "Value.5", "Value.6", "Value.7", "Value.8", "Value.9", "Value.10", "Value.11", "Value.12", "Value.13", "Value.14", "Value.15", "Value.16", "Value.17", "Value.18", "Value.19", "Value.20", "Value.21", "Value.22", "Value.23", "Value.24"}),

#"Changed Type0" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Value.1", type any}, {"Value.2", type any}, {"Value.3", type any}, {"Value.4", type any}, {"Value.5", type any}, {"Value.6", type any}, {"Value.7", type any}, {"Value.8", type any}, {"Value.9", type any}, {"Value.10", type any}, {"Value.11", type any}, {"Value.12", type any}, {"Value.13", type any}, {"Value.14", type any}, {"Value.15", type any}, {"Value.16", type any}, {"Value.17", type any}}),

in

#"Changed Type0"

And here is the code after I add the relative path

(page as text)=>

let

Source = Json.Document(Web.ContentsWeb.Contents("https:/XXXXX?view=list",
[
RelativePath = "page",
Query = [page = page]
],

[Headers=[Authorization="Bearer XXXXX", Accept="*/*"]])),

#"Converted to Table" = Record.ToTable(Source),

Value = #"Converted to Table"{2}[Value],

#"Converted to Table1" = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

#"Renamed Columns" = Table.RenameColumns(#"Converted to Table1",{{"Column1", "rows"}}),

#"Added Index" = Table.AddIndexColumn(#"Renamed Columns", "Index", 0, 1, Int64.Type),

#"Expanded row" = Table.ExpandListColumn(#"Added Index", "rows"),

#"Expanded row1" = Table.ExpandRecordColumn(#"Expanded row", "rows", {"plain_value"}, {"rows.plain_value"}),

#"Trimmed Text" = Table.TransformColumns(#"Expanded row1",{{"rows.plain_value", Text.Trim, type text}}),

#"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{"rows.plain_value", Text.Clean, type text}}),

#"Replaced Value" = Table.ReplaceValue(#"Cleaned Text",null,"EMPTY",Replacer.ReplaceValue,{"rows.plain_value"}),

#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","","EMPTY",Replacer.ReplaceValue,{"rows.plain_value"}),

#"Grouped Rows" = Table.Group(#"Replaced Value1", {"Index"}, {{"Value", each Text.Combine([rows.plain_value], ";*"), type nullable text}}),

#"Split Column by Delimiter" = Table.SplitColumn(#"Grouped Rows", "Value", Splitter.SplitTextByDelimiter(";*", QuoteStyle.None), {"Value.1", "Value.2", "Value.3", "Value.4", "Value.5", "Value.6", "Value.7", "Value.8", "Value.9", "Value.10", "Value.11", "Value.12", "Value.13", "Value.14", "Value.15", "Value.16", "Value.17", "Value.18", "Value.19", "Value.20", "Value.21", "Value.22", "Value.23", "Value.24"}),

#"Changed Type0" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Value.1", type any}, {"Value.2", type any}, {"Value.3", type any}, {"Value.4", type any}, {"Value.5", type any}, {"Value.6", type any}, {"Value.7", type any}, {"Value.8", type any}, {"Value.9", type any}, {"Value.10", type any}, {"Value.11", type any}, {"Value.12", type any}, {"Value.13", type any}, {"Value.14", type any}, {"Value.15", type any}, {"Value.16", type any}, {"Value.17", type any}}),

in

#"Changed Type0"

The endpoint structure it paginated by adding & after the report type(list) then page= page number

[lbendlin]

view=list needs to move to the Query part.

The rest looks ok in Power Query Formatter.  Still can't find the function call with the excess supplied parameters.  Do you have a "Go to error"  button?

[lbendlin]

- please clear out your Bearer token, you don't want that stuff on the interwebs.

- please read about RelativePath and Query options https://learn.microsoft.com/en-us/powerquery-m/web-contents

[Anonymous]

Thank you @lbendlin, for catching my token 
yeah, I have been trying to use the Relative Path and query; I am lost when I go to the dataset, and invoke custom function, I am receiving an error, and the error is 
An error occurred in the ‘’ query. Expression.Error: 3 arguments were passed to a function which expects between 1 and 2.
Details:
Pattern=
Arguments=[List]
and this where I am stuck 

[lbendlin]

please show a sanitized version of your Power Query code.

[Anonymous]

@lbendlin, here is the query where I am getting the error and thank you so much for your help
Dataset:

let
Source = Json.Document(Web.Contents("https:/XXXXX?view=list", [Headers=[Authorization="Bearer XXXXX", Accept="*/*"]])),
#"Converted to Table" = Record.ToTable(Source),
Value = #"Converted to Table"{0}[Value],
Value1 = Value{0},
#"Converted to Table1" = Record.ToTable(Value1),
#"Transposed Table" = Table.Transpose(#"Converted to Table1"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"total_pages", Int64.Type}}),
total_pages = #"Changed Type"{0}[total_pages],
Custom1 = List.Numbers(1,total_pages,1),
#"Converted to Table2" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Changed Type1" = Table.TransformColumnTypes(#"Converted to Table2",{{"Column1", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Column1", "page"}}),
#"Invoked Custom Function" = Table.AddColumn(#"Renamed Columns", "AHA", each #"AHA function"([page])),
#"Expanded AHA" = Table.ExpandTableColumn(#"Invoked Custom Function", "AHA", {"Feature #", "Jira keyOC", "Jira IDOC", "Release name", "Release date", "Project assigned to", "Workspace name", "Epic name", "Project name", "External Title", "External Description", "Roadmap Visibility", "1H / 2H", "PillarOC", "Value StreamOC", "Pillar (Workspace line label)", "Product Group (Workspace line label)", "Product Line (Workspace line label)", "Value Stream (Workspace line label)", "Code freeze date", "Roll-up release name", "Readiness Priority", "Feature Impact Feature On or Off", "Feature Impact Personas Impacted by Default", "Jira key", "Pillar", "Value Stream", "Feature"}, {"Feature #", "Jira keyOC", "Jira IDOC", "Release name", "Release date", "Project assigned to", "Workspace name", "Epic name", "Project name", "External Title", "External Description", "Roadmap Visibility", "1H / 2H", "PillarOC", "Value StreamOC", "Pillar (Workspace line label)", "Product Group (Workspace line label)", "Product Line (Workspace line label)", "Value Stream (Workspace line label)", "Code freeze date", "Roll-up release name", "Readiness Priority", "Feature Impact Feature On or Off", "Feature Impact Personas Impacted by Default", "Jira key", "Pillar", "Value Stream", "Feature"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded AHA",{"page"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Columns", {"Feature #"})
in
#"Removed Duplicates"

[lbendlin]

what's the code behind #"Data function"  ?