Excel Power BI and Data Modelling


In Frankfurt am Main

1.695 € zzgl. MwSt.


  • Tipologie


  • Niveau


  • Ort

    Frankfurt am main

  • Unterrichtsstunden


  • Dauer

    2 Tage


Power-BI, in particular the tools PowerQuery and PowerPivot enable you to convert your data analysis to a much more efficient and more userfriendly level. In this workshop you will learn to use time-saving PowerQuery-Functions to import raw data from various sources and to prepare it for further analyses. Then you create data models and dashboards with PowerPivot and apply new DAX-functions that facilitate your work tremendously.

Wichtige informationen


  • Course - Excel Power BI and Data Modelling.pdf




Frankfurt am Main (Hessen)
Karte ansehen


auf Anfrage

Zu berücksichtigen

-Automated Data Import and Data Cleansing -Basic and Advanced Technics in PowerPivot-Tables -Building Logical Data Relationships Instead of Lookup Formulas -Optimising Data Models with DAX-Functions -Creating Dynamic and Multivariate Financial Diagrams and Dashboards

This course is designed for English speaking Excel-Users that want to keep up with the Best Pratice of data analyses in Excel. To quote an astonished participant: "I feel like that my data analyses are from the Stone Age compared to all that which is possible now."

Fragen & Antworten

Teilen Sie Ihre Fragen und andere User können Ihnen antworten

Wer möchten Sie Ihre Frage beantworten?

Es wird nur sowohol Ihren Name als auch Ihre Frage veröffentlichen


  • Excel
  • Power Query
  • Power Pivot
  • Power Bi Desktop
  • Controlling
  • Reporting
  • Diagrams
  • Data Cleansing
  • DAX
  • Dashboards


Stephan Nelles

Stephan Nelles

Controlling-Lösungen mit Excel

Stephan Nelles ist Erfolgsautor, Entwickler von Excel-Tools und gefragter Trainer. Er berät Unternehmen beim Aufbau von Controlling-Lösungen mit Excel und entwickelt Self-BI-Tools. Hierbei steht der gesamte Prozess vom Zugriff auf externe Daten über die Modellierung von Lösungen bis zur Entwicklung von Management Reports und dynamischen Dashboards im Fokus seiner Tätigkeit. Seit 10 Jahren haben mehrere Tausend Fach- und Führungskräfte seine Excel-Seminare und Firmenveranstaltungen besucht. Stephan Nelles’ Buch „Excel 2013 im Controlling“ ist das meistverkaufte Excel-Nachschlagewerk im deut


09.00 Introduction • Introduction to the topics of the workshop • Short discussion with participants concerning their professional back- ground and their expectations
09.15 PowerQuery: Streamlining and Automatising the Import and Data Cleansing Process • Structure and basic features of PowerQuery • Accessing various data sources via PowerQuery (CSV, SQL, Excel) • Correcting typical data errors • Editing data formats and replacing values and errors • Encoding data for further data analyses

10.30 Coffee Break

11.00 Advanced Functions to Edit Data in PowerQuery • Edit PowerQuery commands and insert additional commands • Organise, combine and merge queries • Pivoting and unpivoting data with PowerQuery • Group and aggregate columns • Loading data from PowerQuery into a PowerPivot data model • Publishing PowerQuery queries on a SharePoint site

12.30 Lunch Break

13.30 Data Modelling with DAX-Functions in PowerPivot PowerPivot provides a local database server that allows its users to easily develop complex data models. This part of the workshop communicates the basic steps to connect data tables from various sources with one another. You also learn how to exclude data (filter, hide etc.) and how to add necessary additional calculations using calculated columns. • Introduction to the analysis with PowerPivot • Establishing logical relationships between fact and dimension tables of a data model • Introduction to the DAX formula library
Working with Calculated Colums About 100 powerful DAX-Functionen constitute the centerpiece of PowerPivot. In this part of the workshop you master the application of important new functions. Equipped with these you can access a terrific set of opportunities to improve your data analyses. • Enhancements of a basic PowerPivot data model: Integrating flat files and adding calculated columns • DAX essentials: Building, identifying and controlling the filter context • Adding slicers to the PowerPivot table • Conditional calculation with CALCULATE() • Manipulating the filter context with ALL(), ALLEXCEPT() and FILTER() • Calculating percentages and handling of error values

15.00 Coffee Break

Case Study: Data Modelling with PowerPivot • Loading data into the data model • Establishing relationships between tables • Aggregating data using DAX formulas (count customers, distinct count of transactions or contracts, regional or other conditional aggregations etc.)

17.00 End of Day 1

09.00 Time Intelligence with PowerPivot A mighty instrument are the DAX-Functions of the category „Date and Time“, the so called Time Intelligence-Functions that are used to easily compare different periods of time: • Introduction to Time Intelligence in PowerPivot • Integration of calendars in data models • Getting acquainted to default time and date formulas of the DAX library: DATEADD(), DATESBETWEEN(), TOTALYTD(), SAMEPERIODLASTYEAR(), CLOSINGBALANCEMONTH()

10.30 Coffee Break

11.00 Working with Individual Calendars • Application of customised DAX formulas for data comparison in user defined calendar environments (comparing fiscal quarters, years etc.) • Year-to-date and year-over-year calculation in PowerPivot

12.30 Lunch Break

13.30 Creating Multivariate Financial Dashboards Due to changes of the data base, reports usually need to be adapted in order to display accurate results. In this part of the workshop you will learn how to create dynamic reports with PowerPivot and additional functions. These reports don’t just adapt to data changes automatically, the content displayed can be controlled via slicers. • Controlling PowerPivot tables and charts with slicers and timelines • Visualising KPI’s with conditional formatting (traffic lights, icon sets etc.) • Integration of cube functions in dashboards and reports

15.00 Coffee Break

15.30 Dynamic Data Visualisation with „Power BI Desktop“ With „Power BI Desktop“ Microsoft has developed a fantastic Power BI Tool that streamlines the development of charts. • Loading workbook or PowerPivot data into Power BI Desktop • Introduction: How to enhance dashboards by new and impressive visuals • Outlook: Online Services and further development

17.00 End of Day 2

Zusätzliche Informationen

For further informatiion

Excel Power BI and Data Modelling

1.695 € zzgl. MwSt.