Course Description

The sad reality is that not all data we need for our reports is stored in nicely curated databases. While some of the data may be, the reality is that we also need to piece together data provided in text files, Excel files, web pages and even the body of emails in order to build the complete business intelligence solutions we need. Until now this has been a painful process with a great deal of cutting and pasting into Excel files. Updates have been tedious and error prone, if the solution isn’t abandoned outright. That stops today.

This course is about Data. It’s about getting, transforming, cleaning and reshaping your data. It’s about turning awkward data into shiny golden tables that are perfect for analysis.

You’ll learn how Power BI 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 refreshable with a single click. From merging tables to discover matches and mis-matches, to grouping and conditional logic, we will explore Power BI’s “Get Data” experience, and how to make it sing.

And the best part? While we’ll be focusing on these techniques in Power BI desktop, they are also 100% portable to Excel 2010 or higher via the Power Query add-in.

Course at a Glance

Quick review of Power BI report basics:

• Importing data into Power BI
• Creating a simple visual report
• Refreshing your data

Importing Basic Data:

• Individual CSV, text and Excel files
• Individual Non-delimited text files
• Importing multiple "flat" files at one time
• Cleaning and manipulating data

Appending and Merging Tables:

• Append (stack) data from multiple tables
• 6 ways to merge (join) data from multiple tables

Pivoting, Un-Pivoting and Transposing Data:

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

Conditional Logic:

• Creating conditional logic via the User Interface
• Manually creating advanced conditional logic
• Handling conditional logic errors
• Building columns from examples

Other Techniques:

• Understanding the formula firewall
• Structuring queries
• Grouping queries
• Understanding Query Folding

Target Audience

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

Software Requirements

Students should bring a laptop running (preferably) the most current version of Power BI Desktop available at https://go.microsoft.com/fwlink/?LinkId=521662&clcid=0x409.

Please Note: Versions of Power BI Desktop more than 3 months old may not be supported.

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 Power BI.

Registration Includes

• A full day of hands-on, in-class instruction
• Coffee breaks
• Lunch
• A certificate of attendance (for claiming Professional Development credits)
• Example files based on real world issues (including completed versions for later review)

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.

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.