Power Query & PowerPivot Reports
Microsoft Power Query for Excel is an Excel add-in that enhances the self-service Business Intelligence experience in Excel by simplifying data discovery, access and collaboration.
Access the following page:
Select the right file and click next:
After installing Power Query, you will see this:
IMPORTANT This feature isn’t available in Office on a Windows RT PC. Power View and Power Pivot are available in the Office Professional Plus and Office 365 Professional Plus editions, and in the standalone edition of Excel 2013.
Power Pivot in Microsoft Excel 2013 is an add-in you can use to perform powerful data analysis in Excel 2013. The add-in is built into Excel 2013 but is not enabled.
To enable PowerPivot for Excel 2013:
- Go to File > Options > Add-Ins.
- In the Manage box, click COM Add-ins> Go.
- Check the Microsoft Office Power Pivot in Microsoft Excel 2013 box, and then click OK. If you have other versions of the Power Pivot add-in installed, those versions are also listed in the COM Add-ins list. Be sure to select the Power Pivot add-in for Excel 2013.
- Select “Microsoft Office PowerPivot for Excel 2013“
- Click OK
After enabling PowerPivot, you will see this:
Refreshing a Report
After performing the 2 steps above, you will be able to refresh any report using those technologies, as soon as you have the right permissions and are signed in.
Accessing and Editing the Workbook
Access any report on SharePoint / Office 365 that uses Power Query and PowerPivot:
The link below will not work! It’s just a sample…
- Click on “EDIT WORKBOOK“
Validating the Queries / Connections
- Select the tab “POWER QUERY“
- Click on “Show Pane“
- All available connections/queries will be shown on the right task pane.
Refreshing the Data
- Select the tab “DATA“
- Click on the icon “Refresh All“
Signing In (Authentication)
- Select “Organizational account“
- Click on “Sign in as different user“
- Type your username and password
- Check “Keep me signed in“
- Click on “Sign in“
- After see “You are currently signed in“
- Click on “Save“
Validating the Data Refresh
- All queries defined to refresh will start refreshing
- You will see “Loading data…” when Excel is still working
- After the data refresh, you will see the current date and time
shown on top of each spreadsheet, if you have created like that! J