Microsoft Data Insight Summit 2017
First I like to thank the community for attending MDIS 2017. It has been a great sucess and it was amazing to see MDIS sold out again (and double the amount of attendees from last time). The last 3 days have been amazing. We, the Excel team, are so greatfull we got to spend time with all of you to hear your stories and get feedback. I wanted to post a quick recap of the event and the Excel session you can now see ondemand. The speaker will be checking the posts as can answer your questions, we will also share resources for each session where relevant. General Overview Excel and Power BI Better Together Whirlwind Tour of the Modern Excel Tips and Tricks for Working with Data in Excel Dashboard, Reports and Design Visualizing Complex Data in Excel Developer Make your App a Native Part of Office with Office Add-ins Working with web services in Power Query/Excel and Power BI Administration and Governance Spreadsheet Management And Compliance Enable BI & Productivity with Office Online and Office Online Server (On-premise) Advanced Analytics Create Advanced Forecasting Models using Excel & Machine Learning BI Power Hour Business Applications for Connected Data Transforming Knowledge Work with Office 365 Data Prep and Modeling Data Modeling in Excel using Power Pivot Working with Parameters and Functions in Power Query/Excel and Power BI Connect and Transform Data of Any Shape and Size using Power BI Desktop and Excel To see all sessions from MDIS 2017, check out the YouTube Channel.Have an Excel question?
Ask the Excel team anything. Get your questions ready for the Excel team! Join our Reddit AMA this Thurs. November 17th at 12pm EST. https://www.reddit.com/r/excel/comments/5aonww/microsoft_excel_product_team_ama_november_17th/ Related Excel Resources: Besides this community we also have a large set of related resources for Excel. From training videos to Hands on labs, from support to feature request. Here are the top links for Excel: Excel Basics Guided Learning: Excel LinkedIn Learning Videos Excel Documentation More Excel Learning YouTube Videos Excel Intermediate-Advanced Guided Learning: Data Analysis using Excel at edx.org Analyzing and Visualizing Data with Excel at edx.org Excel Hands On Labs Analyzing Data in Excel Hands On Labs Get and Transform Data Hands On Labs Excel Data Model Hands On Labs Support Documentation: Excel Documentation What is new in Excel Product and bug support: Excel Support Community: Excel Community Request a Feature: Excel Feature Requests Blog: Excel Blogs Demo Files: Excel Demos Download trial version of Excel Try Power BI for pre-built dashboards On Demand Excel session from Microsoft Ignite: Excel sessions at Microsoft Ignite 2016Get & Transform - solving all sorts of Excel issues
Some may see Get & Transform (Power Query) simply as a brilliant way of connecting to multiple data sources and loading that data into a Microsoft Power BI development environment (Excel Power Pivot or Power BI desktop). Having spent 25 years using Excel / Lotus 123 functions and macros to clean up crappy data and automate manual tasks I find it is so much more. It is a breath of fresh air. There are lots of great people out there giving great advice on how to get the most out of Get & Transform and I salute them all. A big thank you. Get & Transform can solve all sorts of problems for the Excel user who is wrangling with data. Consolidating, Splitting, Merging, Replacing, Filtering, "UNPIVOTTING!"... the list is comprehensive and seems to be ever growing. Here's just one small and very unusual example. It's the "unusual-ness" that prompted this article. It hopefully goes to show that if there's a problem that you don't know how to solve using "normal" Excel then Get & Transform may well be the answer. The Problem A table of data had been entered in Excel but rather than a unique row being used for each value the user has entered data using the Alt+Enter trick to transfer data onto the next line in the same cell. This makes it impossible to create totals (OK not impossible but you need an array formula and some "out-there" thinking to get it done). Get & Transform to the rescue.... 1.Turn the original set of data into a Table (highlight and Ctrl+T) 2. Get & Transform > From Table 3. Then click on the Split Column > Split by Delimiter And select the Custom Option followed by ticking the box Split using special characters and pick Line Feed (this picks up the Alt+Enter separator) (note I've put 100 columns to split, this allows for 100 items to be entered in each cell, there are better but more complex ways of doing this) 4. Right Click on Types of Uniform and Unpivot Other Columns 5. Right Click on and Remove the Attribute Column 6. Right Click on the Types of Uniform and select Group By... 7. New Column name = "Total Sold" Operation = Sum Column = Value 8. Click Home > Close and Load To.... then select table and load the data into Excel Done! Simply Right Click and Refresh the green table whenever required Ideally we'd avoid all this and explain to the user why using Alt+Enter, in this case, is not a particularly good idea. The aim of this unusual example is just to get you thinking that Get & Transform may well be able to help you ways you never thought were possible.
Excel Tower Defense
Here's an updated version of my Excel Tower Defense game as mentioned in the recent Excel AMA on Reddit. Backstory: Shortly before Office 2010 shipped, the Excel test team was instructed to take a couple days to make solutions that could stress test some of the new features. Because I enjoy playing tower defense style games I decided to make one in Excel using a mixture of many of the new features in that release (Slicers, cross-sheet dependent Conditional Formatting, the AGGREGATE function, and Sparklines). As an added challenge given my familiarity with calc in Excel, I wanted to see how much of the game could be written without using any macros. I spent roughly 4 work days making the first playable version, including significant time spent discovering, reporting, and working around a number of product bugs along the way so that I could complete the game and also get the product bugs fixed before shipping. After the initial version was up and running, I gave it another 8-16 hours of hobby time adding features and fixing bugs in my solution, such as adding localizability and making mouse UI more robust. In the years since then, I’ve probably only looked at it for an hour or so per year, fixing small issues and adding support for sheet zoom levels and non 96dpi Monitors. Due to the internal popularity, one of our PMs at the time offered to write up a blog post about it and another Missile Command game created by a coworker around the same time. You can find that blog post at https://www.microsoft.com/en-us/microsoft-365/blog/2010/07/27/how-its-made-tower-defense-a-game-in-excel-2010/. Since the images appear broken you may have more success reading this copy from archive.org (https://web.archive.org/web/20100730035318/http://blogs.msdn.com:80/b/excel/archive/2010/07/27/how-it-s-made-tower-defense-a-game-in-excel-2010.aspx). We also published a couple YouTube videos going behind the scenes of how they work. The video for Tower Defense can be found at: https://www.youtube.com/watch?v=SFzi0Xs5_vw While you may notice that the file contains macros, these are only used to enable mouse UI (hover and click). To play the game without enabling macros, simply Unhide the “Game” sheet and then after switching to it, use the keyboard to select cells. Pressing F9 to recalculate will trigger the next frame, treating the active cell as a mouse click. For best results, please make sure to close any other books before opening this one and don’t open any other books at the same time.Excel and Power BI Better Together
In this demo-rich session, you will learn how to use Excel's unique strength in Power BI to boost your business analytics and insights. Learn how to easily collect existing organizational data from Excel files into Power BI, create visually rich reports to monitor your business, and securely share it with your colleagues. Use the extensive Excel capabilities with Power BI to get valuable business insights and also drill down to the last bit of your data.
Whirlwind Tour of the Modern Excel
Hundreds of millions of users regularly perform data analysis in Excel. Over the last few years, Excel has modernized every step in the data analysis journey using technology shared with Power BI: you can build powerful data import logic, and advanced data models using the exact same tools that you’ll use when you start building dashboards and reports in Power BI. Come learn about all the advancements we’ve made in advanced analytic features like Power Query, Power Pivot, as well as core spreadsheet capabilities like multi-user collaboration, new functions, and new visuals.
