locked
Pass two dates dynamically in Power Query executing an SP RRS feed

  • Question

  • Hi, 

    I try to execute a stored procedure of Northwind: 

    [dbo].[Employee Sales by Country] that takes two arguments @Beginning_Date Date, and @Ending_Date.

    I have a function fnGetParameter from the book "M is for (data) Monkeys". This function reads a table in Excel named Parameters like this:

    Parameter     Value

    Start Date     01-01-1997

    End Date      31-12-1998

    The function is:

    -------------------------------------

    (ParameterName as text) =>
     let
         ParamSource = Excel.CurrentWorkbook(){[Name="Parameters"]}[Content],
         ParamRow = Table.SelectRows(ParamSource, each ([Parameter]=ParameterName)),
         Value=
    if Table.IsEmpty(ParamRow)=true
    then null
    else Record.Field(ParamRow{0},"Value")
    in
        Value

    ---------------------------------------

    What I have in Advance Editor is:

    -------------------------------------

    let
        startdate = fnGetParameter("Start Date"),  
        enddate = fnGetParameter("End Date"),  
        Source = Sql.Database("Laptop-NP", "Northwind", [Query="EXEC [Employee Sales by Country] startdate , enddate"])
    in
        Source

    --------------------------------

    It gives the following error:

    --------------------------------------------

    DataSource.Error: Microsoft SQL: Error converting data type <g class="gr_ gr_469 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling" data-gr-id="469" id="469">nvarchar</g> to date.
    Details:
        DataSourceKind=SQL
        DataSourcePath=laptop-np;Northwind
        Message=Error converting data type nvarchar to date.
        Number=8114
        Class=16

    --------------------------------------------

    Could you please help?

    Thank you

    Nick

    Monday, October 22, 2018 3:33 PM

Answers

  • I think I found a solution. Maybe it's not the best, but, at least it's a solution. The problem was how to enter the two dates in the cells.

    I put the two dates in Excel like:

    ''19970101'. This appears in the cell like '19970101'

    ''19981231' . This appears in the cell like '19981231'

    The quote, in the beginning, is not a double quote, but two single quotes.

    And, in Advance Editor:

    Source = Sql.Database("Laptop-NP", "Northwind", [Query="EXEC [Employee Sales by Country] "& startdate &","& enddate])

    Thank you

    Tuesday, October 23, 2018 5:54 PM

All replies

  • Hey Nick!

    Thanks for acquiring the book. On behalf of Ken and myself, we thank you for your kind words.

    The error that you're receiving it's because in the Query record for the Sql.Database function you're entering a string instead of referencing the variables that you created.

    The following code should work, but it might request you to set up the privacy levels of both sources (Excel and SQL Server):

    let
        startdate = fnGetParameter("Start Date"),  
        enddate = fnGetParameter("End Date"),  
        Source = Sql.Database("Laptop-NP", "Northwind", [Query="EXEC [Employee Sales by Country] "& Text.From(startdate) &","& Text.From(enddate)])
    in
        Source
    Monday, October 22, 2018 4:17 PM
  • Thank you for your quick response. You deserve the best critics for such an excellent book.

    I have progressed with what you wrote, but not yet until the data. I put your version of Exec.

    I fought with the privacy level, for now, I have put <g class="gr_ gr_169 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" data-gr-id="169" id="169">sa</g> and its password.

    Advanced Editor shows 

    ----------------------------

    let
        startdate = fnGetParameter("Start Date"),  
        enddate = fnGetParameter("End Date"),  
        Source = Sql.Database("Laptop-NP", "Northwind", [Query="EXEC [Employee Sales by Country] "& Text.From(startdate) &","& Text.From(enddate)])
    in
        Source

    ----------------------------------------

    and I have an error

    -------------------------------------------

    DataSource.Error: Microsoft SQL: Incorrect syntax near '/'.
    Details:
        DataSourceKind=SQL
        DataSourcePath=laptop-np;Northwind
        Message=Incorrect syntax near '/'.
        Number=102
        Class=15

    -----------------------------------

    In the worksheet, I had the dates in "d/m/yyyy". I thought it may produce a problem and I changed the two cells to "m/d/yyyy". The table now is like:

    Parameter Value
    Start Date 1-1-97
    End Date 12-31-98

    Monday, October 22, 2018 5:42 PM
  • Thank you for your quick response. You deserve the best critics for such an excellent book.

    I have progressed with what you wrote, but not yet until the data. I put your version of Exec.

    I fought with the privacy level, for now, I have put <g class="gr_ gr_169 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" data-gr-id="169" id="169">sa</g> and its password.

    Advanced Editor shows 

    ----------------------------

    let
        startdate = fnGetParameter("Start Date"),  
        enddate = fnGetParameter("End Date"),  
        Source = Sql.Database("Laptop-NP", "Northwind", [Query="EXEC [Employee Sales by Country] "& Text.From(startdate) &","& Text.From(enddate)])
    in
        Source

    ----------------------------------------

    and I have an error

    -------------------------------------------

    DataSource.Error: Microsoft SQL: Incorrect syntax near '/'.
    Details:
        DataSourceKind=SQL
        DataSourcePath=laptop-np;Northwind
        Message=Incorrect syntax near '/'.
        Number=102
        Class=15

    -----------------------------------

    In the worksheet, I had the dates in "d/m/yyyy". I thought it may produce a problem and I changed the two cells to "m/d/yyyy". The table now is like:

    Parameter Value
    Start Date 1-1-97
    End Date 12-31-98

    hey

    did that last change fix your issue?

    Monday, October 22, 2018 8:39 PM
  • Unfortunately, no. 

    It gives me this error with the "near '/'". I don't know why. I put exactly your code, as I explain above.

    Tuesday, October 23, 2018 8:09 AM

  • Is there any possibility for some additional help? It didn't run.

    If not, can you write an article in mrexcel.com with examples of calling a stored procedure from Power Query with some arguments, for example, two dates?

    Thank you

    Tuesday, October 23, 2018 4:51 PM
  • I think I found a solution. Maybe it's not the best, but, at least it's a solution. The problem was how to enter the two dates in the cells.

    I put the two dates in Excel like:

    ''19970101'. This appears in the cell like '19970101'

    ''19981231' . This appears in the cell like '19981231'

    The quote, in the beginning, is not a double quote, but two single quotes.

    And, in Advance Editor:

    Source = Sql.Database("Laptop-NP", "Northwind", [Query="EXEC [Employee Sales by Country] "& startdate &","& enddate])

    Thank you

    Tuesday, October 23, 2018 5:54 PM

  • Is there any possibility for some additional help? It didn't run.

    If not, can you write an article in mrexcel.com with examples of calling a stored procedure from Power Query with some arguments, for example, two dates?

    Thank you

    it's more about what your stored procedure is expecting rather than a demo of how it works.

    Glad that you were able to figure out what date format was expected by the SP and then concatenating all of that in a single string that gets passed to the Query field of the record in the Sql.Database function.

    Tuesday, October 23, 2018 7:31 PM
  • It reminds me of the dynamic SQL sentences and the problems with embedding parameters in quotes. 

    Is it only this SQL.Database function with the arguments inside double quotes and the difficulties that this means? Is there some other method of executing stored procedures with arguments without all this concatenation?

    To conclude, I have felt a strong innovative feeling only with a few books. For example, the first time I felt it was with the book "Microsoft Word for Windows Revealed" of Herbert L Tyson. It covered the WordBasic (before any VBA) giving us the capability to do fantastic support in Word.

    Also with the book "Excel Gurus Gone Wild" by Bill Jelen, where I felt I know nothing about Excel.

    And now your book "M is for (Data) Monkeys" with the same innovative feeling...

    Tuesday, October 23, 2018 7:57 PM