Power Query: Extract and transform data to Excel and Power BI

Import data

• What is an ETL process (Extract, Transform & Load)?
• Import individual data from Excel files, CSV files, etc.
• Import multiple files from folders or SharePoint document libraries.
• Import data from databases.

Basic transformation steps

• Reduce the amount of data by removing columns and rows.
• Check and adjust data types.
• Transfer missing values to other lines by filling them in.
• Automate column transformations with the help of artificial intelligence.
• Case study: Using data cleansed with Power Query in a sales analysis in Excel.

Add columns and grouping characteristics

• Split or merge columns to create grouping characteristics.
• Extract values from columns (analogous to text functions in Excel).
• Add conditional columns and create columns from examples.
• Case study: Further processing of data cleansed with Power Query in pivot tables.

Adjustment of the table structure

• Append several queries to each other.
• Unpivot tables.
• Grouping data versus calculations in the pivot table.
• Case study: De-pivot pivoted data (e.g. SAP reports) and display in a pivot chart.

Merge contents of two queries via joins

• Inner Join.
• Left / Right outer Join.
• Full Join.
• Case study: Carry out account reconciliations of previous and current months or create a target/actual comparison.

Tips and tricks

• Find and correct import errors.
• Import column names dynamically.
• Create self-referencing table for entering data in Excel.

Please bring your own laptop to the training . You can find more detailed installation instructions under Methods.