Beyond Excel I.

Who is the course for

Training for all Excel users with advanced knowledge and experience (Excel 3), who want to use Excel more efficiently in combination with other applications, with main focus on PowerPivot. Suitable training for business users, who need to perform advanced analysis in Excel and create reports. Everything without programming.

Required skills

Good knowledge of MS Excel in the scope of MSEX313 course. Basic knowledge of databases concepts are suitable, but not required.

Teaching methods

Professional explanation with practical samples and examples.

Teaching materials

PowerPoint handouts

Course outline

• PowerPivot addin
  • Purpose of addin
  • Introduction
  • Technical limits
• Excel Datamodel
  • Building blocks
    • fact tables
    • dimensions
    • KPI – Key Performance Indicators
    • hierarchies
      • parent-child, recursive
      • intertable hierarchy
      • time, geographic, product, …
  • Relations
  • datamodel accessing
  • Family of CUBE functions
  • linked tables
• basic DAX functions
  • DAX functions structure, rules
  • comparison to Excel worksheet functions
  • Family of Path functions
  • RELATED, RELATEDTABLE
  • LOOKUPVALUE
• Measures – aggregations

• Power Query addin
  • Purpose of addin
  • Introduction
  • Technical limits
• Datasources
  • File data sources
    • Textfiles TXT, CSV, PRN, XML
    • Excelu workbooks
    • DB files (Access, DBF, …)
  • Web queries
  • Filesystem, reading of all files in folder
  • Custom Query functions
  • Introduction to M language
• Data manipulations
  • Text values operations
  • Datetime values operations
  • Data cleanup
  • Unpivot tool
• Merge and append queries