Thank you to everyone that attended my webinar with the PASS Excel BI Virtual Chapter today! I’d also like to thank Thomas LeBlanc for having me to present. I had a great time presenting on the newest features of Power BI and what features you will see in the near future. Continue reading What’s New in Power BI Webinar Recording is Available
Tag Archives: excel
Refreshing Excel Power Query & Pivot Tables with SSIS and Task Factory
With SSIS 2014 and earlier there is currently not native way to refresh an Excel workbook which include Power Query queries. Now that functionality is rumored to be included with SQL Server 2016 but if you’re currently running SQL Server 2014 or 2012 you are out of luck. But that’s why Pragmatic Works put together the Excel Power Refresh component for SSIS.
Configure the Excel Power Refresh Task in SSIS
Configuring the Excel Power Refresh Task is pretty straightforward. There’s not a lot of complexity to this component, which is a good thing.
First create a Connection Continue reading Refreshing Excel Power Query & Pivot Tables with SSIS and Task Factory
Here’s the New #Excel 2016 Chart Types!
The Office 2016 Public Preview is now available for download! Included in the preview of Excel 2016 are a handful of new chart types and since I’m a huge fan of awesome data visualizations, I thought I’d take a few moments to play around with them and share my experience with you so you can have a better idea of what to expect in the next version of Excel. But to be honest, if you’re a data & visualizations nerd like me, you’re probably pretty excited! Continue reading Here’s the New #Excel 2016 Chart Types!
PowerPivot 101 Training Webinar with Q&A
To watch the free recording of my webinar called PowerPivot 101, just head over to PragmaticWorks.com, create a free login and you’re all set! Please let me know if you have any questions.
Once again, thanks to everyone that attended my session on PowerPivot. Here are my responses to some of the questions I received during my session but did not have time to answer until now.
1. Does the current version of PP support drill through?
The current version of PowerPivot does support drill through. All you have to do is right-click a measure in your spreadsheet and select Show More Details or alternatively you can double-click the measure.
2. Is PowerPivot add-in required for SharePoint?
PowerPivot for SharePoint is required if you wish you share Excel workbooks that contain PowerPivot data via SharePoint. For more information on installing and setting up PowerPivot for SharePoint, check this out.
3. Where will a replay of the PowerPivot 101 webinar be available?
Head over to the Pragmatic Works Learning Center to view the recording of this webinar as well as the recordings of all the previous webinars!
4. Book/website recommendations?
Definitely check out PowerPivotPro.com run by Rob Collie. It’s a great website with tons of cool PowerPivot-y stuff, plus Rob is a cool dude.
Check out these books!
PowerPivot for Excel 2010: Give Your Data Meaning
Marco Russo & Alberto Ferrari
Practical PowerPivot & DAX Formulas for Excel 2010
Art Tennick
5. What is the name of the component/function in SharePoint that would make the “live” data connections possible in published PowerPivot?
I assume the question is directed at utilizing a live connection to a data source, such as SQL Server. To my knowledge, this is not natively supported with PowerPivot. The data in your PowerPivot workbook must be refreshed.
There are two ways to refresh data in a PowerPivot workbook. You can refresh it manually anytime you want or you can schedule a refresh through SharePoint like I showed during the webinar. For more information on refreshing data in a PowerPivot workbook, check out MSDN.
6. I thought DAX was only supported using the Tabular mode of SSAS. My understanding is that PowerPivot is built on the Multidimensional model. I wouldn’t think DAX would be supported. Can you comment?
DAX (Data Analysis Expression language) is the formula language for PowerPivot and Tabular modeling and is used to specify calculations and to create new columns. It’s basically an extension of the Excel formula language. For more information related to DAX in regards to PowerPivot and Tabular modeling, read this and this.
7. Can you blog about having a calculated field that I can put on a slicer to filter by number of records (i.e. products with more than 100 records).
To filter records where a measure is greater than or less than a certain amount is pretty easy. In your workbook, just click the drop down arrow above the attribute you wish to filter, go to Value Filters, and select Greater Than, Less Than, or whichever option you desire.
Then you can select which measure you want to use and what quantity to use.
Thanks for all the great questions everyone. Don’t forget to check the recording of the webinar in case you missed anything!