Rockford Lhotka's Blog

Home | Lhotka.net | CSLA .NET

 Wednesday, 28 March 2018
« A Bright Future for the Smart Client | Main | The Decline of the Client OS »

Power BI is a pretty amazing tool, even for data analysis novices like me. The ability to take data from an Excel spreadsheet and create useful and compelling dashboards as a power user is really wonderful!

However, I don't create such a dashboard all that often, and I find that each time I do it I fall over one particular issue: how to connect my PBI report to a dataset that can auto-refresh.

My dataset is an Excel file in O365 SharePoint (actually in a folder/site managed by Microsoft Teams). In theory it is easy to have PBI connect to such a spreadsheet, and when the report is published to PBI in the web it can be set to auto-refresh the data daily, weekly, etc.

In practice however, this is fraught with peril. The instructions commonly available from Microsoft and other blogs leave out a couple key issues that (I find at least) derail the process and lead to hours of frustration.

I use Power BI Desktop to create my report. So step 1 is to connect to the Excel file hosted in O365 SharePoint. And it is step 1 where things go wrong, because there are so many different ways to connect to a spreadsheet, and so many URL variations for a spreadsheet hosted in SharePoint - most of which won't actually work in the end.

But what's worse, is that all these myriad variations work from Power BI Desktop, so you are fooled into thinking all is well. When in reality, after you publish the report to PBI in the web it will fail! Very frustrating!

My colleague Scott Diehl and I spent some time this afternoon sorting through what works and what only appears to work, and I am documenting it in this blog post so next time I need to create a dashboard I'll have a place to find the answer without re-learning it.

So here's the thing. I have this spreadsheet in Teams.

And if I click on the "Open in SharePoint" link I can see it in O365 SharePoint via the browser.

If I click the "Copy Link" button here I get a link to the file, such as:

https://mysite.sharepoint.com/:x:/s/TechologyLeadershipTeam/EWToC-py8VVMrdQz-_yXfPoB6_qQky37CgRZyrOtX8FZQA?e=vKQUGF

And you can use that link to add a dataset to PBI Desktop and it work great.

BUT THIS LINK WON'T WORK WHEN YOU PUBLISH TO PBI WEB!

So instead what you must do is open the spreadsheet in actual desktop Excel. Not in Excel Online, not in O365 SharePoint, but in actual Excel.

Then go to the File tab in the ribbon and you should see something like this.

Notice the text I've highlighted. Left-click on this and choose "Copy Link to Clipboard". The result is a URL like this:

https://mysite.sharepoint.com/sites/TechologyLeadershipTeam/Shared%20Documents/General/2018/2018%20Activity%20Tracking.xlsx?web=1

This is just a different URL to the same exact file, but this URL is one that PBI Web will actually accept.

Note: You do need to remove the ?web=1 bit from the end of the URL before using this to create your dataset in PBI Desktop.

From here you can create your report and dashboard, and publish to PBI Web. Then in PBI Web you can edit the dataset to provide PBI Web with your organization credentials, and to set up a refresh schedule for the data:

One final note. If you do what I did, and create your report and dashboard using the first style of URL that works in Desktop but not Web, you are not totally out of luck. In that case, follow the above steps to get the functional URL from Excel, and then in PBI Desktop you can edit the dataset's source.

and

That'll allow you to replace the broken URL with the working URL, without having to recreate your entire report or lose your work.

Wednesday, 28 March 2018 13:27:54 (Central Standard Time, UTC-06:00)  #    Disclaimer
On this page....
Search
Archives
Feed your aggregator (RSS 2.0)
September, 2018 (2)
August, 2018 (3)
June, 2018 (4)
May, 2018 (1)
April, 2018 (3)
March, 2018 (4)
December, 2017 (1)
November, 2017 (2)
October, 2017 (1)
September, 2017 (3)
August, 2017 (1)
July, 2017 (1)
June, 2017 (1)
May, 2017 (1)
April, 2017 (2)
March, 2017 (1)
February, 2017 (2)
January, 2017 (2)
December, 2016 (5)
November, 2016 (2)
August, 2016 (4)
July, 2016 (2)
June, 2016 (4)
May, 2016 (3)
April, 2016 (4)
March, 2016 (1)
February, 2016 (7)
January, 2016 (4)
December, 2015 (4)
November, 2015 (2)
October, 2015 (2)
September, 2015 (3)
August, 2015 (3)
July, 2015 (2)
June, 2015 (2)
May, 2015 (1)
February, 2015 (1)
January, 2015 (1)
October, 2014 (1)
August, 2014 (2)
July, 2014 (3)
June, 2014 (4)
May, 2014 (2)
April, 2014 (6)
March, 2014 (4)
February, 2014 (4)
January, 2014 (2)
December, 2013 (3)
October, 2013 (3)
August, 2013 (5)
July, 2013 (2)
May, 2013 (3)
April, 2013 (2)
March, 2013 (3)
February, 2013 (7)
January, 2013 (4)
December, 2012 (3)
November, 2012 (3)
October, 2012 (7)
September, 2012 (1)
August, 2012 (4)
July, 2012 (3)
June, 2012 (5)
May, 2012 (4)
April, 2012 (6)
March, 2012 (10)
February, 2012 (2)
January, 2012 (2)
December, 2011 (4)
November, 2011 (6)
October, 2011 (14)
September, 2011 (5)
August, 2011 (3)
June, 2011 (2)
May, 2011 (1)
April, 2011 (3)
March, 2011 (6)
February, 2011 (3)
January, 2011 (6)
December, 2010 (3)
November, 2010 (8)
October, 2010 (6)
September, 2010 (6)
August, 2010 (7)
July, 2010 (8)
June, 2010 (6)
May, 2010 (8)
April, 2010 (13)
March, 2010 (7)
February, 2010 (5)
January, 2010 (9)
December, 2009 (6)
November, 2009 (8)
October, 2009 (11)
September, 2009 (5)
August, 2009 (5)
July, 2009 (10)
June, 2009 (5)
May, 2009 (7)
April, 2009 (7)
March, 2009 (11)
February, 2009 (6)
January, 2009 (9)
December, 2008 (5)
November, 2008 (4)
October, 2008 (7)
September, 2008 (8)
August, 2008 (11)
July, 2008 (11)
June, 2008 (10)
May, 2008 (6)
April, 2008 (8)
March, 2008 (9)
February, 2008 (6)
January, 2008 (6)
December, 2007 (6)
November, 2007 (9)
October, 2007 (7)
September, 2007 (5)
August, 2007 (8)
July, 2007 (6)
June, 2007 (8)
May, 2007 (7)
April, 2007 (9)
March, 2007 (8)
February, 2007 (5)
January, 2007 (9)
December, 2006 (4)
November, 2006 (3)
October, 2006 (4)
September, 2006 (9)
August, 2006 (4)
July, 2006 (9)
June, 2006 (4)
May, 2006 (10)
April, 2006 (4)
March, 2006 (11)
February, 2006 (3)
January, 2006 (13)
December, 2005 (6)
November, 2005 (7)
October, 2005 (4)
September, 2005 (9)
August, 2005 (6)
July, 2005 (7)
June, 2005 (5)
May, 2005 (4)
April, 2005 (7)
March, 2005 (16)
February, 2005 (17)
January, 2005 (17)
December, 2004 (13)
November, 2004 (7)
October, 2004 (14)
September, 2004 (11)
August, 2004 (7)
July, 2004 (3)
June, 2004 (6)
May, 2004 (3)
April, 2004 (2)
March, 2004 (1)
February, 2004 (5)
Categories
About

Powered by: newtelligence dasBlog 2.0.7226.0

Disclaimer
The opinions expressed herein are my own personal opinions and do not represent my employer's view in any way.

© Copyright 2018, Marimer LLC

Send mail to the author(s) E-mail



Sign In