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 stop gap for companies without automatic HR integration.
Create Excel Sheet in SharePoint
- Create an excel document.
- List user emails with the grouping that you'd like for them. You can make as many columns as you like.
- Save file to Sharepoint and note where you saved it, we'll need that later.
Connect SharePoint folder in Power BI
- Complete everything before step 4 in this article: Mixing tyGraph with Other Sources in Power BI : Support
- Click More in the "Get Data" section
- Click Add Local model
- Search for SharePoint
- Select "SharePoint folder"
- Press Connect
- Press Okay
Connect to Folder
- Enter your folder name from part one
- Press okay
- This will show all files in the folder. Press Transform Data
- Find your file and press the orange "Binary" item in the content column.
- Rename the table, columns, and perform any other translations that you'd like.
- Press close and apply
Relate New Table To PBIX
- Switch to the relationship view.
- Relate Email to User Email
- Many to one
- Both directions
- Press Okay
You can now use the attributes from your excel file in the data model. You can use them in a variety of ways including:
- As a filter dimension
- As a visualization category.
- Once finished you can publish your work to the Power BI service.
- Then navigate to the dataset settings
- Authenticate as Using OAuth2 using your own account.
- Schedule the refresh.
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.