Solved
Extract a Substring from Every Element in a List
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:
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 DateToTextYour 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 selectedDatesNote 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.
