Power query and stored procedure with parameters

Question

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?

score 3 · Answer 1 · answered Dec 12 '15 at 22:33

3

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

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

returns 39814

answered Dec 12 '15 at 22:33

Carl Walsh

6,100
2
46
50

score 0 · Answer 2 · answered Dec 12 '15 at 14:37

0

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?

answered Dec 12 '15 at 14:37

greggyb

3,728
1
11
32

Power query and stored procedure with parameters

2 Answers2