Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Vantage111
Frequent Visitor

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######...")),

 

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:

Vantage111_0-1709808346922.png

 

3 REPLIES 3
lbendlin
Super User
Super User

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

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"

 

Vantage111_0-1709885795013.png

 

 

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

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.