Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Power Bi dashboards?

Richard Rymill SBP
Richard Rymill SBP ✭✭✭✭✭✭
edited 12/09/19 in Archived 2016 Posts

Hi Community

i'd be interested to know how useful members are finding the Smartsheet linkage with Power BI to create some excellent Graphical dashbards possibly as a supplement to the more Numerical View achieved in Sights?


Could any of you describe your Use case?

Could you describe how easy or Difficult it was to set up?

Could you describe what feedback you got from your users of those dashboards?


Have a Joyful Xmas to you all. Keep up the great input on this excellent community. 


From RichardR and all the team here at Smarter Business Processes, in the UK, EU and USA.   


  • Shaine Greenwood

    Hi Richard,


    I'm trying to grab someone from our Customer Success team that might be able to answer this. 


    If you have a Customer Success contact at Smartsheet, you might reach out to them to see if you can gather a use case.

  • Reid Kilwine

    Hi Richard,


    I am a member of the Customer Success team here at Smartsheet and I have spent some time connecting Smartsheet to Power BI and have showed it to a handful of customers.


    Hopefully I can provide some answers to your questions.


    Q: Could any of you describe your Use case?

    A: Your earlier description is a good start. The use case would be for users who'd like to create dynamic visuals/graphs using their Smartsheet data. Ex: Visualing a sales, or opportunity pipeline that is being stored and edited in Smartsheet. Power BI also alows you bring together data that is independently maintained from a variety of locations. Ex: You could compare your Smartsheet data to data stored in SQL Server.


    Q: Could you describe how easy or Difficult it was to set up?

    A: The connection to Smartsheet is very simple. You sign search for a new data source in Power BI, find Smartsheet, sign into your account and select the sheet, or report, that contains the data you'd like to model. That's essentially it! It is important that your data is set up in a table format (no hierarchies, row labels, etc.). If you have data that you'd like to model but the sheet does include some of the aforementioned formatting, you can use a report to extract the rows you'd like to model. 


    Everything else is Power BI product knowledge. Power BI has excellent support resources: https://powerbi.microsoft.com/en-us/guided-learning/. Probably the most important element on that page would be the section on modeling your data, i.e. telling Power BI that a string of text is actually an address.


    Q: Could you describe what feedback you got from your users of those dashboards?

    A: It depends on the user. I have had customers who get it and love it as soon as they see it. Typically these users are familiar with these types of products or are early adopters. A good indicator that a customer will be successful is if they are adept in using Power Pivot in Excel, as Power BI's visual largely overlay on Pivot tables with in Power BI.


    Customers like that Power BI works off of a "freemium" model and that the connection comes at no additional cost. 


    Customers have become frustrated when they want the end result without interest in learning an entirely seperate product. Depsite Power BI being intuitive and easy to use, there is a fair amount of leg-work needed to set up your visualizations.


    Please let me know if you have additional questions.




  • Treb Gatte-Tumble Road.com
    edited 12/23/16

    Hi Richard,


    I'm Treb Gatte and I manage a Power BI consultancy, Tumble Road, based in the Seattle area. I do a number of speaking engagements on Power BI so I thought I'd share what we are seeing in the marketplace. 


    Many companies are drawn to Power BI due to it's graphical nature, fairly low barrier to entry to try it out and ability to meld together information from disparate systems easily. If they adopt, the cost is generally cheaper than competitors. 


    The ability to tell a great digital story can be done in Power BI using a vast array of built in visuals and the custom visuals available at https://app.powerbi.com/visuals/ However, this typically uncovers a training deficit. Most users have never had formal training in structuing data or in data presentation. I can recommend several resources for this need.


    I'm attaching a screen shot that illustrates the types of visualizations possible. This dashboard provides three levels of detail. The top provides overall proportions of the portfolio for specific statuses. The middle provides key roll ups, based on specific criteria. The third level provides project specific data. Since everything is clickable, clicking on the Red portion of Project Status bar will filter everything to show only data relevant for projects with that status. Also, note the double link icon next to each project. This dynamic link enables you to tie the visuals to your normal workflow. In this case, if I see a project in trouble, I can click and go directly to the project schedule. Also, everything ties to Excel. You can import the data from a specific visual or use the report as a data source for pivot table reporting.



    The mobile and Cortana integration story in Windows 10 is really impressive. A PMI roundtable here in Seattle uncovered that Executives love the Mobile Power BI app. Many orgs are seeing over 50% adoption rate among Executives. Power BI also enables you to control the mobile reformatting of the reports and dashboards, providing a much better experience.


    **To the local SmartSheet folks, I'd love to do a similar demo as follows using SmartSheet as the source data.** Power BI's Cortana voice integration can be significantly faster than working with web page interfaces. Case in point, here's a short demo of doing a resource query using Cortana, Power BI and Windows 10 timed against the same query using a web interface. This was done using data from another product but you can do it over any data. https://youtu.be/txHpocAqI5M Note, there's some additional work that needs to be done to enable this but it's more tedious than difficult to do. This work also makes Power Q&A much richer as well.


    Note, the iOS version of the Mobile App allows you to monitor key metrics on your Apple Watch. I have our website metrics on my watch at all times.


    The true power of Power BI is the ability to create logical views over data from different sources. I have clients who are tying together various project and task management tool data with Salesforce and various financial packages. Excel based data is also treated as a first class data citizen.  The client can focus on the answer to their questions rather than the source systems of the data.


    I hope this helps. I'll monitor this thread for any subsequent questions.




    Marquee Portfolio Detail.jpg

  • I just want to go on record as saying that Power BI is a terrific tool to use in conjunction with Smartsheet!  I'm not a data professional or super-techie, and it took me a little while to figure out how to get what I wanted out of Power BI, but - wow! - it's terrific once you get it figured out. I'm sure that there are much more sophisticated uses for it than I've managed to implement, but if you are looking for a graphical representation of what you have in Smartsheet, I think Power BI is an easy winner. It will be interesting to see how the graphical representations that Smartsheet comes out with will compare to the Power BI stuff. I sure hope that the integration with Power BI doesn't go away once Smartsheet comes out with their own. 

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    I really like how easy PowerBI was to set up. A customer and I did a complete make over on her weekly reports to management. Prior to PowerBI, it was half of her day on Monday. Now, 15 minutes (and that's due to the poor API from a different 3rd party tool she uses).

    Time for design, implementation, and testing? 8 hour and most of that was on the Smartsheet side getting the data ready for viewing.






  • Richard Rymill SBP
    Richard Rymill SBP ✭✭✭✭✭✭

    Thanks Craig and for the feedback on this from all of you.

    We are constantly looking for ways to extend Smartsheet capability with tools that satisfy the ever challenging demands of our clients.

    We also found out recently that Power Bi can, not only source it's data from a mix of Smartsheet and Excel but also, there is a workaround to use data from Google sheets to. 

    While most of us find that interrogating Data in a Graphical format is the Easiest and Fastest way to consume data, the lovers of Numeric Results do love Sights too.  

    Looking forward to the new Graphics from Smartsheet to add to the mix of tools available? 

    Regards to all. 




  • Hi Richard,

    Yes, I have a blog post on how to access Google Sheets data without making the data public. Details can be found here: https://tumbleroad.com/2016/07/07/free-marquee-google-sheets-template/ 

    There's over 60 different data sources which can be incorporated into a data model, as well as the ability to mix both on-premises and cloud data.

    Also, you can make your BI automatically actionable by incorporating Microsoft Flow. A flow can be triggered from a data condition, making it where situations can now be easily escalated automatically. 

    There's much more coming in the next three months. 

    Let me know if you ever want to discuss.




    Treb Gatte MBA, MCP, MCTS, Business Solutions MVP 

    Managing Partner


    Tumble Road, enabling better decision making, using Microsoft technology.

    Microsoft Power BI Showcase Partner

    7829 Center Blvd SE STE 277

    Snoqualmie, WA, USA 98065

    +1 425-654-3437 (USA)

    +61 7 3040 3093 (Australia)





    Get ready for Power BI Certification.  https://powerbicertification.com

  • Richard Rymill SBP
    Richard Rymill SBP ✭✭✭✭✭✭


    Thanks for those words of encouragement and details of how to connect Google sheets data to Power Bi, which for some might sound a little odd? But to explain, as a general principle we encourage our clients to use the best tool for the job especially if it involves Automation and great Collaboration solutions, which both Google sheets and Power Bi do (to supplement Smartsheet). 

    Yes, many of our clients prefer to stick with Either Microsoft or Google but just as many use whatever is best for them. So we need to know how to stitch all this together to make great workflows. We do love PowerBi and are surprised more Smartsheet users don't use it? 

    You live near Snoqualmie falls? ... it came a great 2nd place to Friday Harbour and watching Humpback whales swimming freely as part of our Conference trip.... Sorry nothing to do with the subject under discussion but that name triggered happy thoughts of the Engage Conference trip. 

    Maybe you could help us with some of our clients? 




  • Hi Richard,


    Sorry for the delay. Yes, we would love to work with you. Feel free to send me an email at treb.gatte@tumbleroad.com




  • I introduced Smartsheet to the organisation where I work now having discovered it after reviewing the market for a good project collaboration tool and selecting it for use at my last employers. So I am a strong Smartsheet supporter. My current employer also use Power BI and are increasing their use of this equally powerful tool.

    We have however found one challenge in that we can create dashboards that work great using Smartsheet as a data source when using the cloud version of the Power BI reporting service. However, the data will not refresh in the dashboards when we use the internal (on premise) Power BI reporting service.

    Does anyone know if there is some way of being able to refresh the data for our on premise Power BI reporting service to access?

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    I haven't used on-premise Power BI, so my questions won't necessarily help, but here goes.

    I assume your are using the Schedule Refresh option?

    Is Smartsheet one of the allowed Gateways or are you using ODBC? It needs to be in the list of items in Get Data.

    Are you getting any errors or warnings? Does Refresh Now work?

    Good luck


  • Assuming you have the latest version of Power BI Report Server, you can set up scheduled refresh using the following procedure. 


    Report Server is generally updated every three-four months so it's a good idea to upgrade often.

    Hope this helps!

    --Treb, Data Platform (Power BI) MVP


This discussion has been closed.