Extract a Substring from Every Element in a List

[vonp]

I have a dynamic M Query that sends the selected dates to a SQL query.  The format I want the slicer is in:

Mon, 01/09/2023 (Proposal 1)

Mon, 01/09/2023 (Proposal 2)

Tue, 01/10/2023 (Proposal 1)

etc.

I need to be able to pull out the date and rearrange it to fit the SQL required YYYY-MM-DD format.  Here is what I have tried:

	selectedDates = if Type.Is(Value.Type(DateParameter), List.Type) then
            Text.Combine({"'", 
				Text.Combine({
					Text.Combine({
						Text.Combine({Text.Range(DateParameter, 11, 4), Text.Range(DateParameter, 5, 2)}, "-"), 
						Text.Range(DateParameter, 8, 2)}, "-"),
				 	"','"}), "'"})
        else
            Text.Combine({"'", 
				Text.Combine({
					Text.Combine({
						Text.Range(DateParameter, 11, 4), Text.Range(DateParameter, 5, 2)}, "-"),
						Text.Range(DateParameter, 8, 2)}, "-"), "'"}),

It works for the defualt parameter, but when I select something on the slicer, I get the following error:

[Daryl-Lynch-Bzy · Accepted solution]

Okay - I am following now.  This is the transformation needed to each item selected in the Slicer:

let
    ParameterText = "Mon, 01/09/2023 (Proposal 1)",
    ExtractDateText = Text.BetweenDelimiters(ParameterText, " ", " "),
    ExtractDate = Date.From( ExtractDateText ),
    DateToText = "'" & Date.ToText( ExtractDate, "YYYY-MM-DD" ) & "'"
in
    DateToText

Your code can be updated to this:

let
ParameterText = { "Mon, 01/09/2023 (Proposal 1)", "Mon, 01/10/2023 (Proposal 1)" },
//ParameterText = "Mon, 01/09/2023 (Proposal 1)",
selectedDates = 
    if Type.Is(Value.Type(ParameterText), List.Type) then
        "in (" &
        Text.Combine(
            List.Distinct(
                List.Transform( 
                    ParameterText,  
                    each "'" & Date.ToText( Date.From( Text.BetweenDelimiters( _ , " ", " ") ), "YYYY-MM-DD" ) & "'"
                    )
            ),
            ", "
        )
        & ")"
    else
        "'" & Date.ToText( Date.From( Text.BetweenDelimiters(ParameterText, " ", " ") ), "YYYY-MM-DD" ) & "'"
in
    selectedDates

Note the results:

With list is:   "in ('2023-09-01', '2023-10-01')"
With Single is: "= '2023-09-01'"

I am assuming you need to include the text with an Native SQL String.

[vonp]

Thanks, that was super helpful

[Daryl-Lynch-Bzy]

Hi @vonp - have you considered using the Date.ToText - PowerQuery M | Microsoft Learn function.  The query would like something like this:

selectedDates = Date.ToText( DateParameter , "YYYY-MM-DD" ) 

[vonp]

I did try that, but got the same error.  I was able to just match the SQL column in the database to the Slicer, so this way I didn't need to convert the list