Get data from api loads in powerquery with correct data but as soon as i close & apply, i get error

[Anonymous]

Hello everyone, for reference this is my code

let
    // Set Dates
    Today = DateTime.LocalNow(),
    LastYearDate = Date.AddYears(Today, -1),
    TodayFormat = DateTime.ToText(Today,"yyyy-MM-dd"),

    StartingDate = DateTime.ToText(LastYearDate,"yyyy-MM-dd")&"T00:00:00.000Z", 
    Endingdate = TodayFormat & "T00:00:00.000Z",

    // Get Access Token
    clientkey = "key",
    clientsecret = "secret",
    url = "https://api.8x8.com",
    clientId = key,
    secret = secret,
    base64Encoded = Binary.ToText(Text.ToBinary(clientkey & ":" & clientsecret), BinaryEncoding.Base64),
    headers = [
        #"content-type" = "application/x-www-form-urlencoded",
        #"authorization" = "Basic " & base64Encoded
    ],
    body = "grant_type=client_credentials",
    options = [
        RelativePath = "oauth/v2/token",
        Headers = headers,
        Content = Text.ToBinary(body),
        ManualStatusHandling = {400, 401, 403, 404, 500}
    ],
    response = Web.Contents(url, options),
    jsonResponse = Json.Document(response),
    #"Converted to Table" = Record.ToTable(jsonResponse),

    // Get Report ID
    access_token= #"Converted to Table"{0}[Value],
    startdate = StartingDate,
    enddate = Endingdate,
    
    payload = [
        dateRange = [
            start = startdate,
            end = enddate
        ],
        intraDayTimeRange = [
            start = "00:00:00.000",
            end = "23:45:00.000"
        ],
        groupBy = [ name = "group-and-agent-and-media-and-channel-and-queue" ],
        granularity = "month",
        metrics = {"abandoned", "abandonedPercentage", "accepted", "acceptedPercentage", "avgBusyTime", "avgHandlingTime", "avgHoldTime", "avgSpeedToAnswer", "avgWrapUpTime", "busyTime", "handlingTime", "offeringTime", "presented", "rejected", "rejectedPercentage", "transfersInitiatedPercentage", "wrapUpTime"},
        timezone = "America/New_York",
        title = "Agents",
        type = "agent-interactions-summary",
        includeSubTotal = false,
        includeGrandTotal = false
    ],

    reportheaders = [
        #"Accept" = "application/json;charset=UTF-8",
        #"Content-Type" = "application/json",
        #"Authorization" = "Bearer " & access_token
    ],

    reportoptions = [
        RelativePath = "analytics/cc/v7/historical-metrics",
        Headers = reportheaders,
        Content = Json.FromValue(payload)
    ],

    reportresponse = Web.Contents(url, reportoptions),
    reportjsonResponse = Json.Document(reportresponse),
    #"Converted to Table2" = Record.ToTable(reportjsonResponse),
    Value1 = Text.From(#"Converted to Table2"{0}[Value]),


    // Get the Full Data
    report_Id =Value1,
    dataheaders = [
        #"Accept" = "application/json;charset=UTF-8",
        #"Content-Type" = "application/json",
        #"Authorization" = "Bearer " & access_token
    ],
    relativepath = "analytics/cc/v7/historical-metrics/" & report_Id & "/data?page=0&size=1000",
    dataoptions = [
        RelativePath = relativepath,
        Headers = dataheaders
    ],
    dataresponse = Function.InvokeAfter(()=>Web.Contents(url, dataoptions), #duration(0,0,0,10)),
    #"Imported JSON" = Json.Document(dataresponse),


    // Convert JSON response to a table
    data = Table.FromList(#"Imported JSON", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(data, "Column1", {"total", "items"}, {"total", "items"}),
    #"Expanded items" = Table.ExpandListColumn(#"Expanded Column1", "items"),
    #"Expanded items1" = Table.ExpandRecordColumn(#"Expanded items", "items", {"key", "label", "value"}, {"key", "label", "value"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded items1", {"key", "total"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"label"}, {{"Count", each _, type table [label=text, value=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count], "Index", 1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"value", "Index"}, { "value", "Index"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded Custom", {"Count"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[label]), "label", "value"),
    #"Changed Type" = Table.TransformColumnTypes(#"Pivoted Column",{{"Index", Int64.Type}, {"Start Time", type datetimezone}, {"End Time", type datetimezone}, {"Group", type text}, {"Group Id", Int64.Type}, {"Agent", type text}, {"Agent Id", type text}, {"Media", type text}, {"Channel", type text}, {"Queue", type text}, {"Queue Id", Int64.Type}, {"Abandoned", Int64.Type}, {"Abandoned %", Percentage.Type}, {"Accepted", Int64.Type}, {"Accepted %", Percentage.Type}, {"Average Busy Time", type duration}, {"Average Handling Time", type duration}, {"Average Hold Time", type duration}, {"Average Speed To Answer", type duration}, {"Average Wrap Up Time", type duration}, {"Busy Time", type duration}, {"Handling Time", type duration}, {"Offering Time", type duration}, {"Presented", Int64.Type}, {"Rejected", Int64.Type}, {"Rejected %", Percentage.Type}, {"Transfers Initiated %", Percentage.Type}, {"Wrap Up Time", type duration}})
in
    #"Changed Type"

in power query

you see 200 response and i get my full data, however as soo nsa i close and apply, i get error 400 bad request. please help

[lbendlin]

Use the Query attribute.  The RelativePath should only be  

"analytics/cc/v7/historical-metrics/" & report_Id & "/data"

[lbendlin]

I don't think they have a Page 0.

[Anonymous]

thats from their api documenttion they do and the data loads perfectly in powerquery so yes it exists the issue isnt from page its from the Relativepath of report_id being dynamic

[Anonymous]

@lbendlin for more clarification 

if i hardcode reportid into the reativepath, the data loads, it only gives error when reportid is dynamic