Power Query is a great way to access data and Power View is a great way to view a report.
Some details of Power Query can be found here,
http://hodentek.blogspot.com/2013/12/what-is-microsoft-power-query.html
http://hodentekhelp.blogspot.com/2013/12/what-is-difference-between-ms-query-and.html
Power View is covered in detail in my book,
This post shows how you may connect to SQL Server 2012 Express and query the databases therein using Power Query.
Step 1. If you have not installed the add-in do it now. Get it from this link here,
http://www.microsoft.com/en-us/download/details.aspx?id=39379
Step 2. Launch MS Access 2010 ( or your version). You should see in the splash screen that the add-in is also loaded.
Step 3. Anyway verify it from the Excel Options window that you can access from File menu as shown.
Step 4. Click menu item Power Query followed by From Database as shown.
Step 5. Click From SQL Server Database. In the window that pops-up type in the server name as shown. There is no browse feature here, you have to manually enter as shown.
Step 6. Click OK after entering SQL Server name and the following window pops-up.
Step 7. You can choose encrypted or unencrypted for you connection. Here Unencrypted is chosen.
Step 8. Click Save. The query window appears with a default name Query1 as shown. Here Northwind has been expanded to show the tables.
Step 9. Click on Categories as shown. The table content gets shown on the right side.
Step 10. You can further process the query using the built-in controls which can be displayed by right clicking the corner of the table as shown.
Step 11. The chosen options updates the steps, each time you make a choice a step is added as shown. Note that this next query is from AdventureWorksLT2012 database.
This will be saved to the workbook which you may name appropriately by completing this window. Right now there is no link to publish this result except saving it to the SkyDrive and use it later.
Enjoy
Some details of Power Query can be found here,
http://hodentek.blogspot.com/2013/12/what-is-microsoft-power-query.html
http://hodentekhelp.blogspot.com/2013/12/what-is-difference-between-ms-query-and.html
Power View is covered in detail in my book,
This post shows how you may connect to SQL Server 2012 Express and query the databases therein using Power Query.
Step 1. If you have not installed the add-in do it now. Get it from this link here,
http://www.microsoft.com/en-us/download/details.aspx?id=39379
Step 2. Launch MS Access 2010 ( or your version). You should see in the splash screen that the add-in is also loaded.
Step 3. Anyway verify it from the Excel Options window that you can access from File menu as shown.
Step 4. Click menu item Power Query followed by From Database as shown.
Step 5. Click From SQL Server Database. In the window that pops-up type in the server name as shown. There is no browse feature here, you have to manually enter as shown.
Step 6. Click OK after entering SQL Server name and the following window pops-up.
Step 7. You can choose encrypted or unencrypted for you connection. Here Unencrypted is chosen.
Step 8. Click Save. The query window appears with a default name Query1 as shown. Here Northwind has been expanded to show the tables.
Step 9. Click on Categories as shown. The table content gets shown on the right side.
Step 10. You can further process the query using the built-in controls which can be displayed by right clicking the corner of the table as shown.
Step 11. The chosen options updates the steps, each time you make a choice a step is added as shown. Note that this next query is from AdventureWorksLT2012 database.
This will be saved to the workbook which you may name appropriately by completing this window. Right now there is no link to publish this result except saving it to the SkyDrive and use it later.
Enjoy
No comments:
Post a Comment