Issues using custom dates for API

[jbrown184]

Hey everyone!,

I am trying to add in custom dates in an API URL. Example: I am converting 

startDate=2024-12-01&endDate=2025-12-05

to

startDate=[Current Date]&endDate=[Start Date].

Here is what it looks like.

[v-xinruzhu-msft]

Hi @jbrown184 

Did the solutions Omid_Motamedise , ZhangKun  and lbendlin  offered help you solve the problem, if them helps you can consider to accept them as solutions so that more user can refer to, or if you have other problems you can offer some infomrmation so that can provide more suggestion for you.

Best Regards!

Yolo Zhu

[Omid_Motamedise]

use the below formual

="startDate="&Text.From([Current Date])&"&endDate="&Text.From([Start Date])

[ZhangKun]

If you just want to solve the problem, you only need to concatenate the strings:

"...&startDate=" & [Current Date] & "&endDate=" & [Start Date]

But for this slightly complex problem, decoupling should usually be done, that is, splitting the complex function into several small functions. Especially if there is some sensitive information in your problem.

For example, the following method first creates a function:

// just an example
(s, e) =>
// https://restapi.amap.com/v3/geocode/geo?startDate=xxx&endDate=yyyy&grouping[]=a,b,c
Web.Contents(
    "https://restapi.amap.com", 
    [
        Query=[
            startDate = s, 
			endDate = e, 
			// because record don't support multiple same field names, the following method is used.
			// most servers support this method to pass multiple parameters with the same field name.
			#"grouping[]" = "a,b,c"
        ], 
        RelativePath="/v3/geocode/geo"
    ]
	// other code
)

Then call the function fx in Table.AddColumn:

Table.AddColumn(tbl, "custom", each fx([Start Date], [Current Date]))

You can also pass other things like the table name (or perhaps other things like the dataset) as parameters, so that it is a generic function instead of having to rewrite it every time you use it.

[lbendlin]

Please follow the documentation. Use RelativePath and Query parameters. https://learn.microsoft.com/en-us/powerquery-m/web-contents#example-1