Updating your Excel and CSV datasets to point to SharePoint Online


(Removing and Automatic refresh and OneDrive from the title as just had somebody point out that i didn't include the OneDrive directions and actually didn't walk through that path for this effort.)

 

 

My primary role is to ensure a great customer interaction through our website, UserVoice, Webinars, MVPs, User Groups and our two feed back programs (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.

image

This blog post will hopefully help you get out of some of those fire drills so all you need to do is have the gateway or the refresh button update your reports

For even easy way to make this happen check out using OneDrive  and Automatic refresh in the 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!

image

 

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)

image

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

image

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

image

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

image

 

 

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:\Users\chass> 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:\diad\auditlog.csv

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


Comments (0)

Skip to main content