Reformatting date values when using them as URL parameters in a PowerQuery API request-CodePudding

I have two dates in my Excel table with the following format: "dd-mm-yyyy". These dates need to be sent as URL query parameters to an API endpoint for getting some data using PowerQuery. However, the API endpoint does not accept dates in that format. Therefore, I need to convert them to the format "mm-dd-yyyy" instead for it to work.

For getting the values from my table, I use the following code:

let GetNamedRange=(NamedRange) =>
 
let
    name = Excel.CurrentWorkbook(){[Name=NamedRange]}[Content],
    value = name{0}[Column1]
in
    value
in
    GetNamedRange

This function, called "GetValue", is then called when inserting URL query parameters in my GET request:

Csv.Document(Web.Contents("my.api/leave/leavecsv", [Query = [periodStart = GetValue("periodStart"), periodEnd = GetValue("periodEnd"), department = GetValue("department")]]),[Delimiter=";", Columns=14, Encoding=1252, QuoteStyle=QuoteStyle.None])

Currently the cells for my dates are in Text format. I tried using Date.FromText(...) to format the dates, but I get an error saying the datetime format is invalid.

https://learn.microsoft.com/en-us/powerquery-m/date-fromtext

How can I propertly format my date values before inserting them as URL query parameters using PowerQuery?

CodePudding user response：

Ensure your dates are real dates and set to type date. then you can use the Date.ToText function:

let
    theDate = #date(2022,12,7),
    output = Date.ToText(theDate,"MM-dd-yyyy")
in
    output

If, for some reason, you must maintain your dates as text strings (I'd like to know why, if that's the case), you can convert them first to a "real" date, and then create the string:

let
    theDate = "13-12-2022",
    output = Date.ToText(Date.FromText(theDate, "en-150"),"MM-dd-yyyy")
in
    output

CodePudding user response：

Make sure you pass in a culture and format. i.e.

Date.FromText([Column1], [Format="dd-MM-yyyy", Culture="en-UK"])