Power Query & PowerPivot Reports

Power Query

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.

Downloading

Access the following page:

http://www.microsoft.com/en-us/download/details.aspx?id=39379

Select the right file and click next:

After installing Power Query, you will see this:

Microsoft HELP

https://support.office.com/en-us/article/Microsoft-Power-Query-for-Excel-Help-2b433a85-ddfb-420b-9cda-fe0e60b82a94?CorrelationId=3cabfdd9-38e0-4616-bda8-6edd39745403&ui=en-US&rs=en-US&ad=US

Product Version

Power Pivot

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.

Enabling

To enable PowerPivot for Excel 2013:

  1. Go to File > Options > Add-Ins.
  2. In the Manage box, click COM Add-insGo.
  3. 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.

  1. Select “Microsoft Office PowerPivot for Excel 2013
  2. Click OK

After enabling PowerPivot, you will see this:

Microsoft HELP

https://support.office.com/en-us/article/Power-Pivot-Help-241aac41-92e3-4e46-ae58-2f2cd7dbcf4f?CorrelationId=fc83c9cf-c5af-418e-a1df-bc1dbe41c0a2&ui=en-US&rs=en-US&ad=US

Microsoft Troubleshooting

https://support.office.com/en-ca/article/Start-Power-Pivot-in-Microsoft-Excel-2013-add-in-a891a66d-36e3-43fc-81e8-fc4798f39ea8?ui=en-US&rs=en-CA&ad=CA

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

  1. Access any report on SharePoint / Office 365 that uses Power Query and PowerPivot:
    The link below will not work! It’s just a sample…

    https://yourCompany.sharepoint.com/sites/pwa/ProjectBICenter/_layouts/15/WopiFrame.aspx?sourcedoc={703C0D4D-A5BB-48E9-B68F-1091740DFB62}&file= CAPACITY_vs_ASSIGNMENTS(PROTOTYPE).xlsx&action=default

  2. Click on “EDIT WORKBOOK

Validating the Queries / Connections

  1. Select the tab “POWER QUERY
  2. Click on “Show Pane
  3. All available connections/queries will be shown on the right task pane.

Refreshing the Data

  1. Select the tab “DATA
  2. Click on the icon “Refresh All

Signing In (Authentication)

  1. Select “Organizational account
  2. Click on “Sign in as different user

  1. Type your username and password
  2. Check “Keep me signed in
  3. Click on “Sign in

  1. After see “You are currently signed in
  2. Click on “Save

Validating the Data Refresh

  1. All queries defined to refresh will start refreshing
  2. You will see “Loading data…” when Excel is still working

  1. 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