• 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!

  • MVP Logo
  • Recent Forum Posts

    DUSTWUN

    Nested if help

    I have a tracker that keeps up with people's tasks.

    A1= date task is completed in YYYYMMDD
    A2=adds 365 days to show next due date...

    DUSTWUN Today, 01:12 AM Go to last post
    Heyjoe

    Table of Contents Doesn't work.

    In Review Shoe Final it does not show up. If I go to Review then Show Final with Markup it shows up. So I think we have to accept and reject tracking...

    Heyjoe Yesterday, 07:34 PM Go to last post
    AliGW

    Table of Contents Doesn't work.

    You'll need to provide the offending document....

    AliGW Yesterday, 06:15 PM Go to last post
    Heyjoe

    Table of Contents Doesn't work.

    When I "Insert Table of Contents" all that I get is the title of the book. This is formatted as Book title. I don't get the chapters. The...

    Heyjoe Yesterday, 05:29 PM Go to last post
    Heyjoe

    Track changes

    Thank you. That worked pretty well after I figured out that you were referring to Track Changes Options....

    Heyjoe Yesterday, 05:19 PM Go to last post