Microsoft Power Query for Excel is an Excel add-in that enhances the self-service Business Intelligence experience in Excel by simplifying data discovery and access (Refer the link given at the end of this blog). We could use Power Query to consume SQL Azure data using Excel as a client tool. Power Query is very powerful tool for BI data discovery because it we could create a customer query, be able to consume online tables it uses a JSON parser and it could work with PowerPivot.
Let us see how we could use Power Query with SQL Azure in action:
Step 1: First Download Power Query if you don’t have one from http://www.microsoft.com/en-us/download/details.aspx?id=39379 if you already installed Power Query you may skip this step.
Step 2: Go to SQL Azure management portal and grab your database connection address. If you already have this address you could skip this step
Step 3: Click on Power Query tab then click on From Database button on the ribbon and choose From Windows Azure SQL Database.
Step 4: On the dialog box paste or type your SQL Azure address for Server Name then click on OK
Step 5: In the next dialog box enter you credentials then click on Save
Step 6: In the next dialog box verify everything is done properly then click on Done
Step 7: On the right hand side you will see the status of your data refresh and if you prefer to interrupt it you may click on Stop.
On the data you refreshed you could do the regular data manipulations such as create pivot tables.
Read more:
http://office.microsoft.com/en-us/excel-help/microsoft-power-query-for-excel-help-HA104003813.aspx
