T1937BI

Ladda ner som PDF

Power BI for Data Stewards

This training is designed for data wranglers, developers or data transformers, who want to transform raw data coming from one or more data sources and make that data ready for further analytics. The emphasis on this course is how to turn low quality data into something that will help you to deliver the business value and the business insights you need to make the right decisions.

Audience

This is a next level course for persons such as Data Stewards, BI developers, ETL developers, and end users who want get more, deeper understanding of Power BI.

Prior knowledge

You should have the following knowledge before taking this course:

- how to get connected to your data and pull it in
- how to build out a basic data model
- how to build some basic visuals
- some basic DAX knowledge

Topics

• Get Data Power Query
• Data Types and Data Structures
• Combining Queries with Merge & Append
• Better Power Query Editor Experience
• Reducing Number of Rows
• Column Operations & Table Transformations
• Text Transformations & Numeric Transformations
• Structured Column Transformations & Add Column Transformations
• Error Handling
• Date and Time Transformations
• Dynamic Power Query with Functions and Parameters
• Introducing Power Query Formula Language
• M Working with Data Structures in M Advanced M Scripting
• Performance Tips and Tricks for Power Query Use Cases

Outline - 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 Modelling; Designing the data model
• Append, creating a single big query of the same structure
• Merge; Joining queries when the structure is... Läs mer

Topics

• Get Data Power Query
• Data Types and Data Structures
• Combining Queries with Merge & Append
• Better Power Query Editor Experience
• Reducing Number of Rows
• Column Operations & Table Transformations
• Text Transformations & Numeric Transformations
• Structured Column Transformations & Add Column Transformations
• Error Handling
• Date and Time Transformations
• Dynamic Power Query with Functions and Parameters
• Introducing Power Query Formula Language
• M Working with Data Structures in M Advanced M Scripting
• Performance Tips and Tricks for Power Query Use Cases

Outline - 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 Modelling; 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
• Column Operations, Choosing and Removing Columns
• Data Type Change
• Locale consideration for the data type
• Replace Values
• Fill Down/Up; Very Useful for Excel
• Group By; Changing the granularity of the data table
• Group by Advanced
• 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
• Aggregate
• 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
• List
• Record
• Table
• Function
• Navigating through List and List functions
• Navigating through Record and Record functions
• Navigating through Table and table functions
• Concatenating lists and records

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

Kursfakta

Kurs-ID: T1937BI
Längd: 2 dag
Pris exkl moms: 18 900 kr
Inregistrering: 09.00
Kursstart: 09.30
Kursslut (ca): 17.00

Frågor om kursen?

Har du frågor om kursens innehåll, leveransdatum/ort eller behöver en företagsanpassad variant? Fyll i formuläret nedan!


Kan betalas med:
TRAINING CARD

Ort och datum

Stockholm
30 jan-31 jan
Boka nu!
23 mar-24 mar
Boka nu!
23 apr-24 apr
Boka nu!
4 jun-5 jun
Boka nu!
Göteborg
30 jan-31 jan
R
Boka nu!
23 mar-24 mar
R
Boka nu!
23 apr-24 apr
R
Boka nu!
4 jun-5 jun
R
Boka nu!
Cloud Access
i Läs mer

Delta på kursen från ditt hem, jobb eller annan plats.

30 jan-31 jan
Boka nu!
23 mar-24 mar
Boka nu!
23 apr-24 apr
Boka nu!
4 jun-5 jun
Boka nu!

Tipsa