Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin 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.
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:
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"
That error seems to come from elsewhere - I don't see any conversion to type number in the code you posted.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
14 | |
13 | |
8 | |
8 | |
7 |
User | Count |
---|---|
17 | |
11 | |
7 | |
6 | |
6 |