• Rebekah

    by

    Microsoft Excel - Power Pivot


    COURSE DESCRIPTION

    Are you ready to turbocharge your PivotTables? Power Pivot is a free Excel add in that allows you to perform powerhouse data analysis and modeling to make intelligent business decisions.

    This Power Pivot online training, which Ken Puls has developed with a company called GoSkills, will help you step up your game and take your skills to new heights.

    If you aren't familiar with this amazing tool, here are four reasons you will want to learn Power Pivot:

    1. Power Pivot makes analysis easy. You can output data visually to
    ...
    by

    Microsoft Excel - Power Query


    COURSE DESCRIPTION

    So you’ve impressed your coworkers with your encyclopedic memory of formulae, become a Pivot pro, and started working IF statements into regular conversation. Now what? We say, Power Query. In a world where data is king, you should know how to work with and understand it – and this tool helps you do just that.

    In this Power Query online course Ken Puls has designed and built with a company GoSkills, you will learn the BI (Business Intelligence) process of importing data, appending and merging tables, conditional logic, data transformation, and organization. The course starts with the basics of Power Query, and works its way up. This means that you’ll be a pro in no time, even if you’re a beginner with the tool. ...
    by Published on 2020-10-29 10:51 PM

    Excel Dashboards


    COURSE DESCRIPTION

    Take your Excel skills to a whole new level by learning how to build dynamic Excel dashboards to show off your data. Ken Puls has worked with a company called GoSkills to help design and build and online course that will teach you the data visualization skills ...
    by

    Dimensional Modeling for the Excel and Power BI Pro

    Dimensional Modeling for the Excel and Power BI Pro

    COURSE DESCRIPTION

    What is Dimensional Modeling and why should you care?
    The classic Excel PivotTable did a great job of letting us quickly pivot and slice data for years. There was a ton of logic built into the tool in order to make it easy for end users, but that ease of use had a cost: it didn’t scale to multiple data source tables. And worse, it actually kept you from learning the terms to scale your own knowledge.
    ...
    by Published on 2020-02-20 12:14 AM
    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!

    TimeSleuth

    Have you ever hit the refresh button in Excel and then waited... and waited... and waited for your Power Queries to refresh? Want to know which query is causing the bottleneck? Then maybe you should check ...
    by Published on 2020-01-02 09:20 PM



    Everything you need to know about shaping, loading, and modelling data in Power BI and Modern Excel! This 2-day master class is developed by Matt Allington of Excelerator BI and Ken Puls of Excelguru.

    Course Description

    Join us for hands-on ...
    by Published on 2019-05-27 08:50 PM



    Prepare to change your company’s reporting game forever! We know that data is not usually stored in nicely curated databases and often - even when it is - the data analyst doesn’t have access to it. Instead, we must piece together data provided in text files, Excel files, web pages, and even the body of emails to build the business intelligence solutions we ...
    by Published on 2019-02-19 09:08 PM



    Transform the way you work with data forever! If you need to learn one skill in Excel today, it is how to work with Power Query. Not only will it change the way you "Get & Transform" data in Excel, but it's also the system used to collect data for Power BI Desktop, meaning these skills are transferable to other programs. Let us show you how to master your data in this small group hands-on session!

    Course Description

    The sad reality is that not all data is stored in nicely ...

    Sign up to receive the monthly(ish) Excelguru newsletter, featuring the latest updates for Excel and Power BI, upcoming training sessions, new products, and other information.

    As a "thank you" for subscribing, we are excited to share our FREE eBook series, 'DIY BI' Tips, Tricks & Techniques. Don't miss out - sign up today!








    Subscribe to the Excelguru Newsletter


    * indicates required

















    View previous campaigns.



    ...
    by Published on 2018-07-12 10:39 PM

    Whether you are looking to build a brand new solution, or need to fine-tune or update an existing one, the team of experts here at Excelguru has got your back! We offer both consulting and mentoring services to help you with your Excel, VBA, Power Pivot, Power Query, and Power BI needs. For smaller Excel jobs (those that will take four hours or less), we partner with Excel Rescue.

    Not sure if why you would want to hire an Excel expert, or can justify the cost? The real question to ask yourself is, "How much do you spend on repetitive tasks?" Read Ken's article on ...
    by Published on 2018-07-10 01:20 AM

    Installing Power Query
    The Power Query add-on is installed by default in Excel 2016 and can be found in the Get & Transform section of the Data tab. However, for Excel 2010 and 2013, the add-on needs to be downloaded and installed. You can download the most recent version from the Microsoft site.

    There are three things you should know about this download package:

    1. It takes admin rights to install.
    2. You need to get the correct version: 32bit (x86) or 64bit (x64). This MUST match your
    ...
    by Published on 2018-06-06 01:00 AM

    TERMS OF SERVICE

    General
    This site, excelguru.ca, and all its sub-areas (“Website”) is owned and operated Excelguru Consulting Inc. Excelguru makes no representation or warranty that the information displayed on the website is appropriate or available for use in other jurisdictions.


    Intellectual Property Rights ...
    by Published on 2017-12-20 10:15 PM

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



    In the online Power Query courses that we deliver as part of our Skillwave Training platform, we commonly talk about how data professionals can often perform “Magic” ...
    by Published on 2017-12-20 09: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

    Power BI


    COURSE DESCRIPTION

    Make your data come to life with beautiful, interactive reports in Power BI. Ken Puls has worked with a company called GoSkills to help design and build an introductory course which is offered in an online format.
    ...
    by Published on 2017-10-30 09:41 PM

    Excelguru Privacy Policy

    1. What information do we collect and what do we do with it?
    We collect the personal information you give us, including your name and email address, as part of the registration process when you subscribe to our newsletter email list, enroll as a student or subscriber to one of our courses, and/or email us directly with a question or comment.

    Email Marketing:
    If you subscribed to our email list, we may send you emails about our monthly newsletter, our site, current or new courses, or other information that we think may be of interest to you.

    If you registered for one of our courses, we may send you emails about our site and related course(s), registration, course content, your course progress, or other updates. We may also use ...
    by

    Microsoft Excel - Advanced


    COURSE DESCRIPTION

    Have some experience with Excel and want to improve your skills, or taken our Basic Excel course? This online course Ken Puls has built with GoSkills will help you learn intermediate and advanced Excel techniques.

    Supports Excel for Windows 2010, 2013, 2016, 2019, Microsoft 365. Also available: Excel Advanced for Mac.

    In 28 engaging lessons you will ...
    by

    Microsoft Excel - Macros & VBA


    COURSE DESCRIPTION

    Learn to automate your most repetitive tasks at the push of a button with Excel macros and VBA. In this online course that Ken Puls designed and built with a company called GoSkills, ...
    by

    Microsoft Excel - PivotTables


    COURSE DESCRIPTION

    New to Pivot Tables or want to get more out of them? Ken Puls worked with a company called GoSkills to design and build this online course to give you a solid understanding of Pivot Tables, taking you from novice to ninja!

    Pivot Tables are a powerful tool within Excel that can be used to analyze, sort, filter and present data in an understandable way.
    ...
    by

    Microsoft Excel - Basic


    COURSE DESCRIPTION

    New to Excel or need a refresher? Ken Puls has worked with a company called GoSkills to design and build a targeted Excel course which is offered in an online format.

    This online course is designed to give you a solid foundation in the basics of Excel.

    Supports Excel for Windows 2010, 2013, 2016, 2019, Microsoft 365. Also available: Excel ...
    Page 1 of 2 1 2 LastLast
  • MVP Logo
  • Recent Forum Posts

    Bob Phillips

    Please help!

    This array formula will add up the sums

    =SUM(IF(A2:A20"",(--LEFT(A2:A20,8)=--"2020-11-24")*SUBSTITUTE(MID(A2:A20,11,99),")","")))...

    Bob Phillips Yesterday, 08:46 PM Go to last post
    Bob Phillips

    Index/match with multiple match criteria

    When I said Q1, I meant Quarter 1, cell E31, not cell Q1.

    It most definitely does match your last upload, C8:C30 is the date which I parse,...

    Bob Phillips 2020-11-24, 06:44 PM Go to last post
    GLewis14

    Consolidate paired data sets

    hi all,

    I have a sheet which contains data sets (3 rows per data set), see Product Level sheet of attachment. I want to consolidate and total...

    GLewis14 2020-11-24, 02:46 PM Go to last post
    alansidman

    Please help!

    What is the criteria for counting the quantity. Would be helpful if you provided a sample workbook showing what you have and what your expected results...

    alansidman 2020-11-24, 06:21 AM Go to last post
    cprop76

    Please help!

    I am trying to count two separate pieces of data which appear in the same excel column. The columns will have a date followed by a quantity so look something...

    cprop76 2020-11-24, 05:52 AM Go to last post