Excel Power Query

  • Hi-

    I am experimenting using Power Query in Excel 2010 and I would really love to use it to extract the results of a SQL Server stored procedure that I have. However, it looks to me that the only complete tables or views are available for the queries from Excel.

    Does anyone have any experience or some tricks that might work to utilize a stored procedure?

    Thanks,

    Scott

  • I couldn't determine a way to use my existing stored procedure in Power Query, so I converted it to a view. Works great that way.

  • Scott Arendt (8/13/2013)


    I couldn't determine a way to use my existing stored procedure in Power Query, so I converted it to a view. Works great that way.

    Good to know, thanks for posting the solution.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • While I can get the Power Query to work great for me, as soon as I send it to someone else, they are prompt to enter credentials to access the data.

    This would probably work great if the other users had accounts on the SQL Server, but they don't. I have tried setting the Excel Services Authentication Settings to None (which should use the connection string) and then saving the password in the connection string.

    Every Google search on Power Query or Data Explorer comes up with the same examples (most by Pragmatic Works - which is the reason I am trying this out in the first place) but no real information on setting up the connection properties or best practices.

    Any help would be greatly appreciated. BTW I am using Excel 2010 and SQL Server 2008

  • I believe you can somehow circumvent security by using the fast combine option.

    Not 100% sure, it's also new for me 🙂

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply