virtual classroom
Contact
Contact
virtual classroom cart
Show training

Microsoft Power BI – Advanced data transformation with Power Query editor

training code: PBI_02_QUERY / ENG AA 2d / EN

When working with applications like SAP, Excel, Access and others its common to spend a lot of time to edit, transform and prepare date to use it for analyses. Basic tools don’t have enough flexibility to be able handle all problems. Power Query editor allows you to use many built in functions to fix most problems with no more than few clicks. Experienced user will also be able to create their own functions which can allow to handle even more complex data quality problems.

For more information, please contact the sales department. For more information, please contact the sales department.
1,700.00 PLN 2,091.00 PLN with TAX

This training is design for people that on daily basis work with different data sources with variety of data quality problems. Training if focused on methods that will allow them to automate process needed to import and improve data quality. It also provides a lot of information about how to modify and create code in M language.

  • You will know how to use build in features for transformations
  • You will learn how to automate importing data from folders, www and files
  • You will see how important it is to prepare data before using it in Power BI data model
  • You will be able to combine data form separate files
  • You will learn how to use fuzzy match to be able to match similar values
  • Youl will see how to use M language to create even more complex transformations
  1. Why and when to use Power Query Editor
  2. Checking and detecting data quality
    1. Checking data quality before applying transformations
    2. Data quality statistics
    3. Role of list tables
    4. Calendar table (dates tables)
    5. Data normalization and denormalization
  3. Build in transformations
    1. Merging / splitting columns
    2. How to handle row transformations
    3. Data types
    4. Text – Extract, clean, trim, capitalize words
    5. Pivot / Unpivot
    6. Transpose
    7. Conditional column, index columns, column from examples, custom column
    8. Group by
    9. Fill Down/Up
    10. Date and time transformations
    11. Transformations with regional settings
  4. Appending and Merging data
    1. Flat files (txt, excel,…)
    2. Relational databases
    3. PDF files
    4. Automatic appending files with consistent structure
    5. Appending data with non-conform structure
    6. Merging tables
    7. Query dependencies
  5. Working with queries
    1. Loading
    2. Refreshing
    3. Enabling load
    4. References
    5. Duplicating
    6. Grouping
    7. Parameters
  6. Konwerting queries to functions
    1. Using variables
    2. Using query as a parameter
    3. Repetitive transformations – loops
  7. M language
    1. Language structure and elements
    2. Advanced editor
    3. Most useful functions
    4. Custom solutions

Basic knowledge of Microsoft Power BI or completion of the PBI_01 training.

The training is conducted through practical exercises as well as lectures.

– English

    Contact our consultant