Forum Discussion

mmace1's avatar
mmace1
Impactful Individual
5 years ago
Solved

Get Parameter from SQL Query - then pass that Parameter into a different SQL Query

I'm sucessfully passing a Parameter into my Power Query SQL statements.  An example of the M code to reference a Parameter: p.CreateDateUTC >= '"&StartDT&"'  And the parameter itself: ...
Need Help
  • mahoneypat's avatar
    mahoneypat
    5 years ago

    Usually you need a format like YYYY-MM-DD, but get it working with a hard-code value and then reproduce that using Date.ToText( ), if needed.  Then you just reference the query name, not the step name.  In your case,

    pCreateDateUTC<" & RevEndDt

     

    If there is more after that, you can concatenate that with & "rest of sql expression"

     

    Regards,

    Pat

mmace1's avatar
mmace1
Impactful Individual
5 years ago

mahoneypat 

 

Thanks. 

 

So for converting my query to text - like this?   I converted to date first (to remove the time portion of the datetime), then converted to text, then drilled down. 



But then, - since my query is all in SQL, I'm not sure how to reference that value in the M Code?? Referencing the Parameter works like this: 

p.CreateDateUTC <'&"EndDT"&'

 

 

 But referencing this text value, I'm not clear the syntax? 


 

 

mahoneypat's avatar
mahoneypat
Microsoft Employee
5 years ago

Usually you need a format like YYYY-MM-DD, but get it working with a hard-code value and then reproduce that using Date.ToText( ), if needed.  Then you just reference the query name, not the step name.  In your case,

pCreateDateUTC<" & RevEndDt

 

If there is more after that, you can concatenate that with & "rest of sql expression"

 

Regards,

Pat

  • mmace1's avatar
    mmace1
    Impactful Individual
    5 years ago

    Thanks, Date.ToText( ) is what i needed, specifically this format: 

    '"&Date.ToText(RevEndDT)&"'