My primary role is to ensure a great customer interaction through our website, UserVoice, Webinars, MVPs, User Groups and our two feed back program (In our division referred to as “Fire Starters” and “Advisors”).

The best part of this is interacting with YOU…the community.  The worst part is the reporting and the mini fire drills of getting the data updated for our team scorecard.

My typical reports start out something like the report below importing a local file, which of course to update requires me to update the PBIX and re-upload it to the service to it get it refreshed (or install and configure a Gateway).

This blog post will hopefully help you get out of some of those fire drills without needing to install or manage a Gateway using Sharepoint aka One Drive for Business and Automatic refresh

An alternative from our documentation:

Automatic refresh  – This means no user configuration is necessary in order for the dataset to be refreshed on a regular basis. Data refresh settings are configured for you by Power BI. For online service providers, refresh usually occurs once-a-day. For files loaded from OneDrive, automatic refresh occurs about every hour for data that does not come from an external data source. While you can configure different schedule refresh settings and manually refresh, you probably don’t need to.

Seems like it should be easy…Just upload the file to SharePoint/OneDrive for Business.  Then grab the link from SharePoint and update the data source…Unfortunately in SharePoint all the links come back looking like the following:

https://powercommunity.sharepoint.com/sites/PowerDemos/_layouts/15/guestaccess.aspx?guestaccesstoken=LotsofcharactersGuid12245etc&rev=1

Which will NOT work with Power BI! 

What Power BI is looking for is:

https://TenantName.sharepoint.com/sites/SiteName/Shared%20Documents/FileName.xlsx

The easiest way to get this is have Excel supply it as part of their sharing process. 

To get the correct Power BI data source link from SharePoint:

1. Open the file from SharePoint in Excel (Right Click > Open In Excel)

2.  Go to File > Share > Email > Send Link

3.  Grab the link from the email and DELETE the ?web=1 portion

4.  Open the Power BI Desktop File.  Go to File > Option and Settings >  Data Source Settings > Change source and paste in your SharePoint Link from above

I plan on doing another Blog post on this topic…but the data being looked at in this report the audit logs to see if the folks in my Feedback programs are logging in and if so what are they looking at.  The script I am using is the following:

>> PS C:Userschass> Set-ExecutionPolicy RemoteSigned

>> $UserCredential = Get-Credential

>> $Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://outlook.office365.com/powershell-liveid/ -Credential $UserCredential -Authentication Basic –AllowRedirection

>> Import-PSSession $Session

>> $result = Search-UnifiedAuditLog -StartDate 9/11/2016 -EndDate 9/15/2017 -RecordType PowerBI | Export-Csv c:diadauditlog.csv

(Big thanks to Adam Saxton from Guy in a Cube – YouTube who showed me the Exchange commandlet to do this!)