Power Query - pass if statement through SQL WHERE statement to filter by date

[Anonymous]

Hi,

I have 2 parameters set up in Power Query. One for Month, the other for Year. Both of these are dropdown of numbers that users will select to choose the date range for their data. I'll then pass these values through the WHERE statement to filter the results.

In the data I'm pulling, the Date field is Text. For example "20230701". Below is the current set up:

WHERE DATEOFCHECK >= '"& Number.ToText(#"02c: Calendar Start Year") & Number.ToText(0) & Number.ToText(#"02b: Calendar Start Month (Number)") & "01" &"'

The issue I'm having is when the month value is greater than 9 - this would add another character to the text string, which wouldn't work.

I want to add in IF statement that looks at the Month parameter and either adds or removes the "0" depending on the value.

Any ideas on how to do this?

Thanks,

Mark 

[Anonymous]

Thanks @Anonymous , that works perfectly.

Mark

[Anonymous · Accepted solution]

Hi @Anonymous ,
You can try the following code

let
    Year = Number.ToText(#"02c: Calendar Start Year"),
    Month = Number.ToText(#"02b: Calendar Start Month (Number)"),
    FormattedMonth = if Number.FromText(Month) < 10 then "0" & Month else Month,
    DateString = Year & FormattedMonth & "01",
    Query = "WHERE DATEOFCHECK >= '" & DateString & "'"
in
    Query

This code converts the year and month parameters to text. Checks if the month is less than 10, and if true, presets it to “0”. Constructs a date string in “YYYYMMDD” format. Constructs the final query string using the formatted date.

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly