Text from function

[samahiji]

Hi,

I have defined a function and named E_Date to generate the short date formate (i.e 01-Nov-24) to be embedded in SQL in power query. However, I'm not sure why it return null!

= "01-"&Text.Start(Date.MonthName(DateTime.LocalNow()),3)&"-"&Text.End(Date.ToText(DateTime.Date(DateTime.LocalNow())),2)

 The output is exactly what I need (01-Nov-24), however it return nothing to embedded query.

 .... DATE  <=  '"&E_Date&"'...

[Anonymous]

Hi @samahiji ,

Pls has your problem been solved? If so, accept the reply as a solution. This will make it easier for the future people to find the answer quickly.

When embedding E_Date in your SQL query, ensure that the syntax is correct. For example:

let
    SQLQuery = "SELECT * FROM Table WHERE DATE <= '" & E_Date & "'"
in
    SQLQuery

Best Regards,

Stephen Tao

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

[Anonymous]

Oh--in that case just escape the single quote like this:

'' (that is two apostrophes).

So

"SELECT...FROM...

WHERE DATE <= ''"&E_Date&"''AND..."

Remember, that's an apostrophe escaping an apostrophe.

--Nate

[Anonymous]

Looks like you just need to remove those single quotes. So

"SELECT...FROM...

WHERE DATE <= "&E_Date&" AND..."

--Nate

[samahiji]

The single quotes are required for the sql date to run. It returns error when removed.

I added the single quotes inside the function itself but it didn't work though.

However, creating parameter will solve the issue, but I need to make it dynamic.

= "'"&"01-"&Text.Start(Date.MonthName(DateTime.LocalNow()),3)&"-"&Text.End(Date.ToText(DateTime.Date(DateTime.LocalNow())),2)&"'"