Excel Power BI and Data Modelling

FIRST DAY
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

SECOND DAY
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