Forum Discussion

Vantage111's avatar
Vantage111
Frequent Visitor
2 years ago

Error when using parameters for Web.Contents query

I have a Source that works pefectly well when using static parameters in the query:

 

Source = Json.Document(Web.Contents("https://portals.acu#####ile.com/beta-###/api/?datefrom=2023-01-01&dateto=2023-12-31&token=zxdi#######ffh3ws-rr&username=IM#####12987")),

 

When I use parameters to refresh this source:

let

  Source = Json.Document(Web.Contents("https://portals.acu#####ile.com/beta-###"),
    [
      RelativePath="api",
      Query=
      [
        #"filter(datefrom;GREATEREQUALS)"=StartDate,
        #"filter(dateto;LESS)"=EndDate,
        #"filter(token;EQUALS)"=Token,
        #"filter(username;EQUALS)"=UserName
      ]
    ])
in Source

I get the following error:

 

Need Help
Tips and Tricks

3 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

    Is that an embedded scenario?

     

    The correct syntax would look like

     

    let
  Source = Json.Document(Web.Contents("https://portals.acu#####ile.com/beta-###"),
    [
      RelativePath="api",
      Query=
      [
        datefrom=StartDate,
        dateto=EndDate,
        token=Token,
        username=UserName
      ]
    ])
in Source
    • Vantage111's avatar
      Vantage111
      Frequent Visitor
      2 years ago

      Thanks for your response. Unfortunately your proposal also has the same error, here is the full query, if I substitute the "let source =..." for the one with static parameters it works fine:

      let
    Source = Json.Document(Web.Contents("https://porta#####bile.com/beta-iml"),
        [
            RelativePath="api",
            Query=
            [
                datefrom=StartDate,
                dateto=EndDate,
                token=Token,
                username=UserName         
            ]
        ]),

    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"projectID", "requestID", "requestedBy", "dateTimeRaised", "guid", "project", "site", "planningPeriod", "providerReference", "requestType", "funding", "category", "subCategory", "failure", "worksProposed", "deduction", "cdmNotifiable", "fireImpact", "planAllocation", "tenderReturn1", "tenderReturn2", "tenderReturn3", "fmProviderCost", "fmProvider", "totalCosts", "requestStatus", "deleted", "pmProvider", "fmUplift", "directProcurement", "worksCompleted", "completionValidated", "validatedBy", "validatedDate", "failureReason", "planReference", "invoiceStatus", "location", "roomNumber", "energy", "finalInvoice", "modelAmount", "planningAmount", "spvApprovedName", "spvApprovedDate", "regionalApprovedName", "regionalApprovedDate", "assetApprovedName", "assetApprovedDate", "approvedName", "approvedDate"}, {"projectID", "requestID", "requestedBy", "dateTimeRaised", "guid", "project", "site", "planningPeriod", "providerReference", "requestType", "funding", "category", "subCategory", "failure", "worksProposed", "deduction", "cdmNotifiable", "fireImpact", "planAllocation", "tenderReturn1", "tenderReturn2", "tenderReturn3", "fmProviderCost", "fmProvider", "totalCosts", "requestStatus", "deleted", "pmProvider", "fmUplift", "directProcurement", "worksCompleted", "completionValidated", "validatedBy", "validatedDate", "failureReason", "planReference", "invoiceStatus", "location", "roomNumber", "energy", "finalInvoice", "modelAmount", "planningAmount", "spvApprovedName", "spvApprovedDate", "regionalApprovedName", "regionalApprovedDate", "assetApprovedName", "assetApprovedDate", "approvedName", "approvedDate"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1",{{"projectID", Int64.Type}, {"requestID", Int64.Type}, {"requestedBy", type text}, {"dateTimeRaised", type datetime}, {"guid", type text}, {"project", type text}, {"site", type text}, {"planningPeriod", type text}, {"providerReference", type text}, {"requestType", type text}, {"funding", type any}, {"category", type text}, {"subCategory", type text}, {"failure", type text}, {"worksProposed", type text}, {"deduction", type text}, {"cdmNotifiable", type text}, {"fireImpact", type text}, {"planAllocation", Int64.Type}, {"tenderReturn1", Int64.Type}, {"tenderReturn2", Int64.Type}, {"tenderReturn3", Int64.Type}, {"fmProviderCost", Int64.Type}, {"fmProvider", type text}, {"totalCosts", type any}, {"requestStatus", type text}, {"deleted", type text}, {"pmProvider", type any}, {"fmUplift", Int64.Type}, {"directProcurement", type text}, {"worksCompleted", type any}, {"completionValidated", type text}, {"validatedBy", type any}, {"validatedDate", type any}, {"failureReason", type text}, {"planReference", type text}, {"invoiceStatus", type text}, {"location", type text}, {"roomNumber", type text}, {"energy", type text}, {"finalInvoice", Int64.Type}, {"modelAmount", Int64.Type}, {"planningAmount", Int64.Type}, {"spvApprovedName", type any}, {"spvApprovedDate", type any}, {"regionalApprovedName", type any}, {"regionalApprovedDate", type any}, {"assetApprovedName", type text}, {"assetApprovedDate", type datetime}, {"approvedName", type any}, {"approvedDate", type any}})
in
    #"Changed Type"

       

       

      • lbendlin's avatar
        lbendlin
        Icon for Super User rankSuper User
        2 years ago

        That error seems to come from elsewhere - I don't see any conversion to type number in the code you posted.