Power BI : Web API data

From blank Query

= let
    Source = Json.Document(
        Web.Contents(
            "https://app.runn.io/api/v0/",
            [
                RelativePath = "people", 
                Headers = [
                            #"Authorization" = ("Bearer " & #"Runn_api_token")
                ]
            ]
        )
    ),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Converted to Table"

With Parameters

= Json.Document(
	Web.Contents(
        "https://app.runn.io/api/v0/",
            [
            RelativePath = "actuals?start=2022-01-01&end=2022-12-31&page=1",
            Headers= 
                #"Authorization" = ("Bearer " & #"Runn_api_token")
            ]
        )
    )

with variable dates (and PageNo)

= let

    #"Start Date" = Number.ToText(#"Report Year") &  "-11-01" ,
    #"End Date"= Number.ToText(#"Report Year") &  "-12-31" ,
    #"Params" = "start="&#"Start Date"&"&end="&#"End Date"&"&",
    #"PageNo" = "page=" & Number.ToText(1),


mySource = Json.Document(
        Web.Contents(
            "https://app.runn.io/api/v0/",
            [
                RelativePath = "actuals?" & #"Params" & #"PageNo",
                Headers= [
                    #"Authorization" = ("Bearer " & #"Runn_api_token")
                ]
            ]
        )
    )

in mySource

On the subject of page numbers

if you do not know how many pages to list … try this…

Set the above query to a function that will accept a page number …

fRunn_actuals =

= (page as number) =>

let

    #"Start Date" = Number.ToText(#"Report Year") &  "-11-01" ,
    #"End Date"= Number.ToText(#"Report Year") &  "-12-31" ,
    #"Params" = "start="&#"Start Date"&"&end="&#"End Date"&"&",
    #"PageNo" = "page=" & Number.ToText(page),

mySource = Json.Document(
                            Web.Contents(
                                "https://app.runn.io/api/v0/",
                                [
                                    RelativePath = "actuals?" & #"Params" & #"PageNo",
                                    Headers=[
                                        #"Authorization" = ("Bearer " & #"Runn_api_token"),
                                        #"content-type" = "application/json"
                                     ]
            ]
        )
    )


in mySource

Testing the invoke should allow you to enter a page number

Create a Generate List query

which iterates through the page numbers until null is found

= List.Generate(()=>
[Result = try fRunn_actuals(1) otherwise null, Page=1],
each [Result] <> null,
each [Result = try fRunn_actuals([Page]+1) otherwise null, Page=[Page]+1],
each [Result]
)

TaDaa… Loads of Data