cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Advocate V
Advocate V

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:
StartDT Parameter.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

And I have a query to determine what the StartDT should be:

Value.PNG

 

I can't seem to get my Parameter, to automatically update though.  Instead I just take note of the value, then update the Parameter myself under 'Current Value'

 



asf.PNG

?


1 ACCEPTED SOLUTION

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

4 REPLIES 4
Super User IV
Super User IV

Instead of using a parameter, you can have the output of a query to be a text value.  Use your first parameter in a query, create the text string you need, and then use that query instead of the parameter instead of the 2nd parameter.

Regards,

Pat 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


@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. 

EndDt Text.PNG

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? 


 

 

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

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

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

 

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors