Support

Submit a ticket My Tickets Login
Welcome
Login
  1. Support
  2. Solution home
  3. Solutions
  4. Power BI

Connecting an Excel Document using SharePoint

In our Mixing tyGraph with Other Sources in Power BI article, we demonstrated that you can use a multitude of data sources to build on top of tyGraph. 


In this article, we will show how to integrate an excel document specifically. Excel docs are great for maintaining common lists of users or bringing in a local list of users as a stopgap for companies without automatic HR integration.


TABLE OF CONTENTS


Step 1: Create Excel Sheet in SharePoint

  1. Create an excel document.
  2. List user emails with the grouping that you'd like for them. You can make as many columns as you like.
  3. Save file to Sharepoint and note where you saved it, we'll need that later.


Step 2: Select SharePoint as Source

  1. Complete everything before step 4 in this article: Mixing tyGraph with Other Sources in Power BI : Support
  2. Click More in the "Get Data" section
  3. Click Add Local model
  4. Search for SharePoint
  5. Select "SharePoint folder"
  6. Press Connect
  7. Press Okay


 

Step 3: Connect to SharePoint Folder

  1. Enter your folder name from part one
  2. Press okay
  3. This will show all files in the folder. Press Transform Data
  4. Find your file and press the orange "Binary" item in the content column.
  5. Rename the table, columns, and perform any other translations that you'd like.
  6. Press close and apply
  7. Press Okay



 


Step 4: Relate New Table to the Model

  1. Switch to the relationship view.
  2. Relate Email to User Email
  3. Many to one
  4. Both directions
  5. Press Okay


Step 5: Using Your New Source

You can now use the attributes from your excel file in the data model. You can use them in a variety of ways including:

  1. As a filter dimension
  2. As a visualization category.

Step 6: Publish

  1. Once finished you can publish your work to the Power BI service.
  2. Then navigate to the dataset settings
  3. Authenticate as Using OAuth2 using your own account.
  4. Schedule the refresh.

Step 7: Updating Excel File

Now each time you update the excel file it will automatically be reflected in your reports the next time the dataset refreshes.