• Books & Other Products RSS Feed

    by Published on 2020-02-19 11:14 PM
    Article Preview

    Monkey Tools Add-in

    Monkey Tools is an Excel add-in containing a set of tools to help you build better data models more quickly and using data modelling best practices. They also help you audit what has been done in your data model.

    The philosophy of Monkey Tools is simple: Build better, faster. It is targeted at modelers and analysts who work primarily in Excel, but also push their models into Power BI, as we do here at Excelguru.

    Our aim is to provide you with a good set of tools that help you, as a business intelligence author, to:
    • Build models more rapidly
    • Follow recommended practices
    • Document your work
    • Audit files that you receive

    Monkey Tools Features

    Monkey Tools adds a broad set of new features collected into two general collections: Monkeys and Sleuths. As you'll see below, the Monkeys make things happen, and the Sleuths figure out what has happened. You'll find the tools on a new Monkey Tools ribbon in Excel, and include:

    • QueryMonkey (for inserting useful new queries into your data model)
    • DestinationSleuth (to provide information on query load destinations)
    • QuerySleuth (helping understand your actual queries)
    • TimeSleuth (to benchmark query refresh times)
    • PivotSleuth (helping you diagnose Pivot Table field issues)
    • DAXSleuth (tools especially for working with DAX measures)
    • ModelSleuth (reporting on the properties of your queries and data model)
    • And more!

    A high level view of each of these tools is detailed below.

    DAXSleuth (Also works on Power BI models!)

    We believe that measure dependencies are just as important as query dependencies, and this is the reason we built the DAXSleuth. This form:

    • Displays a dependency/precedent tree view of your DAX measures
    • Provides a full colour display of Implicit and Explicit measures (with or without children), as well as Calculated Columns
    • Shows your DAX measures with colour highlighting and in an indented format
    • Allows you to indent, un-indent, flatten, duplicate, and even update measures without leaving the DAXSleuth
    • For Excel-based models, it also exposes all locations a DAX measure has been used (PivotTables, Pivot Charts, OLAP Formulae, and Named Ranges), and even allows you to select those objects right from the DAX Sleuth!

    It sounds like a lot, and it truly is. Have a look at the Amount measure for the model below. Not only can you tell it has two precedent measures, but you can see the formula and know just how many times it has been used:


    And don't forget that you can select those Excel objects and be taken right to them!

    QueryMonkey

    The Query Monkey gives you the ability to insert key queries like:



    • A “From Folder” setup that works with local and/or SharePoint hosted files


    • Dynamic calendar tables based on your data (for custom calendars, it even provides the option to insert the "periodicity" columns for Rob Collie's GFITW DAX pattern!)



    • Insert a Measures table, and more!

    Heard enough? Download the free trial!

    DestinationSleuth

    If you've worked with Power Query in Excel, you'll know that you can load queries to an Excel table, to the Power Pivot data model, to both the data model and a table, or as "Connection Only". The challenge is that when you mouse over the preview pane, you're either told that the query loads to Connection Only, or as "x Rows Loaded". But it doesn't tell you WHERE they've been loaded. Well guess what? DestinationSleuth knows, and will even let you filter the list to show only queries that load to specific destinations!


    Not only that, but DestinationSleuth will even let you change multiple load destinations at once, unlike the default Excel user interface for Power Query.

    QuerySleuth (Also works on Power BI models!)

    The QuerySleuth is a must have for every Power Query user. This form is packed with useful information and features such as:

    • A dependency/precedent tree view layout
    • Full colour display based on load destination
    • Colourful and INDENTED M code
    • The ability to modify the M code and write it back to the editor WITHOUT LOCKING YOUR EXCEL User Interface!
    • A variety of configurable options so that you can display the code the way you want to see it

    Developed based on a real business need, we believe this form will add magic to your ability to trace query dependencies and precedents. Have a look at the form in action:


    As you can see, it provides a TON of useful information. It's also worth noting that everything except for the ability to do query writeback works on Power BI models as well! ...

    by Published on 2017-12-20 08:49 PM

    DIY BI eBook Series:
    Tips, Tricks and Techniques for Building Your Own BI Solutions


    This free eBook series is available to our Excelguru email newsletter subscribers. Each book contains ...
    by Published on 2015-10-22 06:42 PM     Number of Views: 91591 

    M is for (Data) Monkey:
    The Excel Pro's Definitive Guide to Power Query

    Add to Cart

    It may have a funny title, but this will be one of the most important Excel books you ever buy in your career.

    The Excel book that will change YOUR life...
    Written BY Excel pros FOR Excel pros, this book has been designed to guide you through learning how to master the new "Get and Transform" data experience in Excel. Released as a free add-in from Microsoft for Excel 2010 and 2013, Power Query technology is now built in to Excel 2016 and the Power BI Desktop application. This technology is a game changer, and will revolutionize the way you work with your data forever.

    Way back when this book was nothing more than a concept, we knew that it needed to be approached in a specific way. It had to speak to Excel users, the problems that they face on a daily basis, and ways to solve those problems both effectively and efficiently. It had to be written to keep you engaged, and to take you on a guided journey, learning from someone who understands you as an Excel pro, how you work, and what you face on a daily basis.

    We're proud to say that we believe that we've delivered on that goal, starting with common tasks you perform, and back filling them with real world issues, inspired by a wide variety of real world data sets. Packed with downloadable files and step by step instructions, this book is built to help you master Power Query so that you can use it in your own work, with your own issues. This book was designed from the beginning to get you up to speed quickly, and to give you the knowledge to build awesome Excel solutions ...
    by Published on 2017-12-20 09:15 PM

    Magic Tricks for Data Wizards:
    Tips & Tricks for Power Query in Excel and Power BI



    In the online Power Query workshops that we deliver as part of our Power Query Training project, we commonly talk about how data professionals can often perform “Magic” ...
    by Published on 2016-10-17 10:08 PM     Number of Views: 19691 

    Learn to Write DAX:
    A Practical guide to learning Power Pivot for Excel and Power BI


    Add to Cart

    Power Pivot has been called “the best thing to happen to Excel in 20 years.” It is a revolutionary piece of software that brings everything that is good about enterprise-strength business intelligence (BI) tools directly to you right inside Excel, and without the negative time and cost impacts you would normally expect from big-scale BI projects. Analyses that you would never have considered viable in the past are now easily doable tasks within the current business cycle.

    But to unleash the enormous power of Power Pivot, you will need to learn how to write DAX. Power Pivot is very learnable, but it also very different from Excel so you need some structured learning if you want to use the tool effectively. This book is written specifically with the Excel user in mind, geared towards the way Excel ...
    by Published on 2016-10-17 08:32 PM     Number of Views: 19799 

    Power Pivot & Power BI:
    The Excel User's Guide to DAX, Power Query, Power BI and Power Pivot in Excel 2010-2016


    Add to Cart

    Excel pros are used to working with unstructured data. This ability is what has made Excel so successful, but it’s also this freedom that makes learning Power Pivot challenging. Why? Because Power Pivot requires structured data, something we’re just not used to.

    It’s for this reason that we HIGHLY recommend Power Pivot and Power BI as an essential part of the learning journey. In fact, had it not been for the material in this book, Ken says he wouldn’t be equipped to teach courses on Power Pivot.

    Learning how tools work is great, but if you can’t figure out how to apply it to your own work, it doesn’t help. And that, right there, is why this book is so essential. Not only does it teach you the concepts of how to build your DAX measures, this book clearly works through how DAX ...

  • MVP Logo
  • View Cart