Forum Discussion

vonp's avatar
vonp
Frequent Visitor
3 years ago
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:

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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.

     

4 Replies

    • vonp's avatar
      vonp
      Frequent Visitor
      3 years ago

      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

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      vonp
      Frequent Visitor
      3 years ago

      Thanks, that was super helpful