Microsoft Excel

Follow these steps to start working with OData and LoUPE in Microsoft Excel.

OData Login

  1. Open the Data tab (or the Power Query tab in Excel 2010-2013) and select Get Data > From Other Sources > From OData Feed.

    ../../_images/microsoftExcel01.png

  2. You will be prompted with a dialog box called OData Feed and asked to enter a URL. Choose “Basic” and enter the URL for your LoUPE instance, making sure to specify port :4500 (the port that the OData feed is accessed on), and /odata.svc, the OData connector. A valid URL will be look like https://mycompany.loupe.tangent-labs.com:4500/odata.svc.

  3. This opens the OData Authentication window, where you must choose “Basic” and enter your username and password for LoUPE.

    ../../_images/microsoftExcel02.png

  4. Click on “Connect” to import data.

    ../../_images/microsoftExcel03.png

  5. A navigator window opens which displays the database tables of your LoUPE instance. Select tables depending on what data that you want to work with. It is possible to select and load multiple tables into the Power Pivot window where the data can be transformed and loaded into sheets.

  6. If data is to be loaded directly to Excel, the “Load” option or “Load to” option can be chosen. If you wish to perform data operations and modify the data, click on “Transform”.

    ../../_images/microsoftExcel04.png

  7. Clicking on “Transform” will launch the Power Query editor. It is ideal to optimize the data extraction by performing actions such as removing unwanted columns, merging other tables, changing data types to the required format etc. Initial operations such as data wrangling and data munging can be performed in the Power Query editor to get an ideal clean data model that can be utilized for reporting.

Power Query Editor

Power Query Operations

../../_images/microsoftExcel05.png

Operations that can be performed using the Power query editor include but aren’t limited to:

  • Adding and removing n number of rows.
  • Merging and appending queries based on foreign key relationships.
  • Adding a new column by invoking a custom function.
  • Transposing data sets.
  • Group by a specific column.
  • Extracting values from a list .
  • Changing datatypes of columns, renaming columns, sorting, filtering etc.
  • Creating a data model.

Microsoft provides a list of power queries that could be used to retrieve data in a specific format.

View Tab

When there are multiple queries, the dependencies can be viewed, to further refine the data model before being loaded to excel.

Query dependencies show all the queries and underlying relationships.

Advanced editor provides the entire text for the selected query in Power query M format.

../../_images/microsoftExcel06.png

Example: Creating a Shot Report

  1. Connect to OData by performing steps 1-4 from the OData Login instructions.

  2. Choose the shot table.

    ../../_images/microsoftExcel07.png

  3. Click on Transform data to launch the Power Query editor. Here, you can remove unwanted columns, keep your desired number of rows, etc.

    ../../_images/microsoftExcel08.png

  4. Click on “Close and Load” at the top left corner and your data will be loaded into an Excel worksheet.

    ../../_images/microsoftExcel09.png