Power BI for Data Stewards

Day 1
Get Data

• Introduction to Power Query
• Query Editor
• Get Data from Web
• Basic Transformations
• Get Data from Excel
• Use First Row As Headers / Use Headers as First Row
• Get Data from SQL Server

Data Types and Data Structures

• Base data structures in Power Query
• Get Data from JSON
• Transforming Table, Record, and List
• Data Types in Power Query
• Query Operations
• Enable Load; Performance Boost
• Query Operations; Duplicate, and Reference

Combine Queries

• Dimensional Modeling; Designing the data model
• Append, creating a single big query of the same structure
• Merge; Joining queries when the structure is different
• Join types in Merge
• Tips to consider after Merge or Append

Better Power Query Editor Experience

• Groups; Folders in Query Editor
• Steps Operations
• Splitting query steps
• Moving steps up or down
• Add as new query / Drill Down
• Be Careful of Actions; Undo!

Reducing Number of Rows

• Filtering
• Row Operations; Removing rows
• Row Operations; Keeping rows
• Remove/Keep Errors
• Remove/Keep Duplicates
• Using Remove/Keep combination for troubleshooting report
• Filtering based on Individual values
• The dilemma of the basic filtering
• Advanced Filtering
• Sorting

Column & Table Operations

• Scripting and Group by; First and Last item in each group
• Transpose; rows to columns and reverse
• Pivot; changing the name-value structure to columns
• Unpivot; changing the budget column structure to rows

Text Transformations

• Split Column by Delimiter
• Split Column by number of Characters
• Split into rows instead of columns
• Merge (Concatenate)
• Format
• The difference between Clean and Trim
• Parse (XML or JSON)
• Extract part of the text

Numeric Transformations

• Standard transformations
• Divide, Integer-divide, Multiply, Add etc.
• Scientific transformations; logarithm, power square, etc.
• Statistics transformations;
• Rounding
• Information functions; Is Even, Is Odd, and Sign.
• Dealing with faults in Numeric calculations

Structured Column & Add Column Transformations

• What is a Structured Column?
• Expand
• Aggregates
• Expand and Aggregate: Performance Consideration
• Add Column vs. Transform?
• Add Column with a Transformation
• Index Column: Row Number
• Conditional Column
• Add Column by Example; When you don’t know which transformation to use
• Add Custom Column: Generic

Day 2

Error Handling

• Keep/Remove Errors; Troubleshooting report
• Count Rows
• Reference/Duplicate
• Replace Errors
• Data Type considerations

Date and Time Transformations

• Date Transformations (Year, Month, Quarter, Week, etc.)
• Extending Fiscal Date Column
• Time Transformations (Hour, Minute, Second, etc.)
• Adding Time/Date banding
• Duration Transformation and Data Type
• Age Calculation
• Local Date or Time
• Time zone consideration for Power BI

Functions and Parameters; Dynamic Power Query

• Defining Parameters
• Using Parameters in an existing query
• Advanced GUI for parameters
• Creating Function from a query
• Invoking the sample function
• Add Column Transform: Invoke Custom Function
• When the advanced GUI does not exist

Power Query Formula Language: M

• What is M? and the importance of learning M
• M Syntax
• Variable Names
• Special Characters
• Escape Character
• Step by Step Coding
• Literals
• Function Call
• Comments
• A real-world example

Working with Data Structures in M

• Cunning
• Record
• Table
• Function
• Navigating through List and List functions
• Navigating through Record and Record functions
• Navigating through Table and table functions
• Concatenating lists and record

Advanced M Scripting

• #Shared Keyword; function library of Power Query
• Parameters in the code
• Custom Functions through scripting
• Generators in Power Query: Implementing Loop Structure
• EACH: singleton function
• Sample Custom Function: Day Number of Year Custom Function

Performance Tips and Tricks for Power Query

• Enable Load; Simple, but Efficient
• Reducing Number of Columns
• Query Folding
• Grouping and Aggregation; Performance Consideration
• Merge; Before and After, things to Consider Use Cases
• Date Dimension with Power Query; building the base table
• Adding Fiscal columns to the Date dimension
• Getting public holidays live and merging to the date dimension
• Looping through files in a folder with Power Query