Power Query: Extract and transform data to Excel and Power BI
Efficient and fully automated data preparation for Excel and Power BI users
.svg)
Machine Learning Engineer
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.
In your online learning environment, you will find useful information, downloads and extra services for this training course once you have registered.
- By using Power Query professionally, you will be able to improve your work efficiency in the time-consuming tasks of data preparation in Excel and Power BI.
- You will learn how to automatically import external data from various sources into your evaluations.
- You will learn how to change external data during the import process so that you can carry out your evaluations with the prepared data in just a few simple steps.
- You will get to know the extensive toolbox of Power Query - from simple data changes, such as splitting data into several columns, to complex data transformations.
- You will get to know Power Query using many practical examples and receive numerous tips and tricks. These will help you to use Power Query in a targeted manner for your own projects after the training .
Presentation, practical examples on the PC, numerous practical exercises, sample solutions.
You will need your own laptop to participate.
Please observe the following installation instructions:
Microsoft Excel from version 2013 or higher and with integrated Power BI from 2020.
employees and specialists who work in controlling or finance and would like to integrate external data into their Excel and Power BI evaluations. Basic knowledge of Excel or Power BI is helpful, but not essential for participation in the training. No prior knowledge of Power Query is required.
This training is aimed at both Excel and Power BI users.
The free Microsoft Power Query add-in is an integral and fixed component of Power BI desktop and, since version 2016, also in Excel. Excel users of version 2013 can install Power Query free of charge, if necessary with the support of their IT department. Excel users of version 2010 do not have access to Power Query.
.svg)