from_date Excel cell reference in PowerQuery - Stack Overflow

admin管理员组

文章数量:1415145

I am currently pulling data from a URL into PowerQuery. The URL looks like this:

= Web.BrowserContents(";to_date=2025-02-28")

The from and to dates are entered on the webpage but I would like to reference cells in Excel for those two dates such that I can update the outputs of the query based on date changes I make in excel, not in the webpage or in the PowerQuery editor.

More specifically, I simply want the 'from_date' to be the start of the current month and the "to_date" to be the last day of the current month.

• I have created another query called 'StartDate' which contains the text 2025-02-01 and then tried to reference it in the above command as the following.

= Web.BrowserContents("="StartDate"&to_date=2025-02-28")

but I get a syntax error that says:

Expression.SyntaxError: Token ',' expected.

I have replaced 2025-02-01 with Date.StartofMonth(Datetime.Now()) as follows:

=Web.BrowserContents(".StartofMonth(Datetime.Now())&to_date=2025-02-28")

In this instance I get the same error as above

Appreciate the help. Thanks

I am currently pulling data from a URL into PowerQuery. The URL looks like this:

= Web.BrowserContents("https://xxxxx.xxx.xxxx/api/protected/profit_loss?from_date=2025-02-01&to_date=2025-02-28")

The from and to dates are entered on the webpage but I would like to reference cells in Excel for those two dates such that I can update the outputs of the query based on date changes I make in excel, not in the webpage or in the PowerQuery editor.

More specifically, I simply want the 'from_date' to be the start of the current month and the "to_date" to be the last day of the current month.

• I have created another query called 'StartDate' which contains the text 2025-02-01 and then tried to reference it in the above command as the following.

= Web.BrowserContents("https://xxxxx.xxx.xxxx/api/protected/profit_loss?from_date="StartDate"&to_date=2025-02-28")

but I get a syntax error that says:

Expression.SyntaxError: Token ',' expected.

I have replaced 2025-02-01 with Date.StartofMonth(Datetime.Now()) as follows:

=Web.BrowserContents("https://xxxxx.xxx.xxxx/api/protected/profit_loss?from_date=Date.StartofMonth(Datetime.Now())&to_date=2025-02-28")

In this instance I get the same error as above

Appreciate the help. Thanks

• excel
• powerquery

Share Improve this question Follow edited Feb 11 at 17:40 jarlh 44.8k88 gold badges5050 silver badges6767 bronze badges asked Feb 11 at 12:45 gheppghepp 1111 bronze badge

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 0

How about

let start= Date.ToText(Date.StartOfMonth(DateTime.Date(DateTime.LocalNow())), [Format="yyyy-MM-dd"]),
end = Date.ToText(Date.EndOfMonth(DateTime.Date(DateTime.LocalNow())), [Format="yyyy-MM-dd"]),
url="https://xxxxx.xxx.xxxx/api/protected/profit_loss?from_date="&start&"&to_date="&end,
Source = Web.BrowserContents(url)
in Source

If, as you write, you want to reference a cell on your Excel worksheet to decide on the dates, and if the date range will always be from the beginning to the end of the month of that date, then

• Name the range where you will enter some date. I used urlDate
• You can then use this code:

let
    baseDate = Date.From(Excel.CurrentWorkbook(){[Name="urlDate"]}[Content]{0}[Column1]),
    startDate = Date.ToText(Date.StartOfMonth(baseDate),"yyyy-MM-dd"),
    endDate = Date.ToText(Date.EndOfMonth(baseDate),"yyyy-MM-dd"),
    
    url = "https://xxxxx.xxx.xxxx/api/protected/profit_loss?from_date=" 
            & startDate 
            & "&to_date=" 
            & endDate

in
    url

With a date in the month of February in urlDate, the output of the above will be:

https://xxxxx.xxx.xxxx/api/protected/profit_loss?from_date=2025-02-01&to_date=2025-02-28

本文标签： fromdate Excel cell reference in PowerQueryStack Overflow