Power query and stored procedure with parameters

2
votes

I use sql stored procedure as the source in power query. Stored procedure has two parameters both numbers (integer). In the Excel sheet I defined parameter table with two parameters. I would like the user to enter the dates as values of parameters (eg. StartDate, EndDate). But to sql stored procedure I would like to pass the numbers that Excel present as dates. For example the date 2009-01-01 is Excel number 39814. So I would like the user to enter the date 2009-01-01 into parameter table but pass to the stored procedure 39814. Could anyone show me necessary conversions in power query to get required results?

2
excelpowerpivotpowerquery

2 Answers

3
votes

The Power Query function Number.From transforms dates to Excel numbers.

Number.From(Date.From("2009-01-01"))

returns 39814

0
votes

Rather than try to handle Excel's date encoding in a different program (not the most fun thing for many reasons), why not just make a hidden column next to your excel parameter table that casts the entered dates to numbers?