• Master Your Data with Power Query



    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 curated databases and often - even when it is - the data analyst doesn’t have access. Instead we have to piece together data provided in text files, Excel files, web pages and even the body of emails to build the business intelligence solutions we need. Until now this has been a painful process with a great deal of cut and paste effort, and updates have been tedious and error prone. That stops today!

    In this course, you’ll learn how Power Query can clean up, reshape and combine your data with ease – no matter where it comes from. Converting ASCII files into tables, combining multiple text files in one shot and even un-pivoting data is not only simple, but an investment in the future. Now your data can be refreshed with a single click the next time you need it, saving your tons of time and effort.

    Course at a Glance

    Review of Essential Tools:

    • The blueprint of “good data”
    • Working with Excel tables
    • Working with PivotTables
    • PivotTable layouts and formatting

    Importing Data:

    • Individual CSV, text and Excel files
    • Individual non-delimited text files
    • Multiple "flat" files at one time
    • Cleaning and manipulating data
    • Refreshing imports

    Appending and Merging Tables:

    • Append (stack) data from multiple tables
    • 7 ways to merge (join) data from multiple tables (with no VLOOKUPs!)
    • Many-to-many merges

    Pivoting, Un-Pivoting and Transposing Data:

    • Un-pivot tables with ease
    • Pivoting stacked data
    • Understanding the Transpose feature
    • Un-pivoting subcategorized data
    • Grouping data

    Conditional Logic:

    • Creating conditional columns
    • Manual IF and IFERROR tests
    • Creating columns from example

    Best Practices:

    • Query structuring
    • Query folding


    **If you're unsure if this course is right for you, or how to make the case to your boss to send you to it, check out our blog post about the course.

    Target Audience

    Anyone who needs to pull data into Excel, clean it up and/or consolidate it. Experience working with Pivot Tables is an asset, but not required.

    Software Requirements

    Power Query is built in to Excel 2016 and Excel 2019. For Excel 2010 and 2013, you'll need to ensure that you have the free Power Query add-in installed.*

    *Note that depending on your version of Excel 2013, you may not be able to connect to some "business" data sources like Microsoft Exchange, SQL Azure or Sharepoint. All other data sources demoed in the course are available regardless of the Office 2013 version you have.

    Time Commitment

    This course is a hands-on course and runs approximately 7 hours in length, depending on the class size and the experience attendees have in working with Excel.

    Registration Includes

    • A full day of hands-on, in-class instruction with Ken Puls, one of the world's top Power Query experts
    • Copies of Ken's slides, many of which contain handy recipes to walk you step-by-step through the techniques
    • Example files based on real-world issues (including completed versions for later review)
    • A chance to ask Ken about the challenges you are currently facing with your own data
    • A FREE digital copy of Ken's top-selling book with Miguel Escobar, M is for Data Monkey (first edition)
    • Coffee breaks and lunch
    • A certificate of attendance

    Registration Details

    The next session of this course has not yet been confirmed. Please subscribe to our newsletter to receive updates as more information becomes available.
    Date Location Price Registration Link
    TBC TBC TBC TBC

    Cancellation Policy:

    Cancellations received up to 3 weeks prior to the course date will be refunded in full less a 10% administration fee. Cancellations within 3 weeks of the event will not be refunded.

     

    I'm afraid that you must be logged in to comment or leave a testimonial. I wish it could be otherwise, but I'm trying to keep my site spam free for everyone's benefit. If you don't yet have an account it's completely free to sign up, and a very quick process. Simply click here to Register. Not only can you post a comment here, but it gives you full access to posts questions in our forum as well!

     

    If you already have an account, and just haven't logged in yet, what are you waiting for? Login Now!