The use of MS Excel is your bread and butter? Do you want to learn how to create advanced analysis without programming? Do you want to look beyond the horizon and use Excel to its maximum with the cooperation of other applications? Do you [...]
  • MSEXNL
  • Duration 3 days
  • 0 ITK points
  • 6 terms
  • Praha (12 000 Kč)

    Brno (12 000 Kč)

    Bratislava (510 €)

  • Intermediate

The use of MS Excel is your bread and butter? Do you want to learn how to create advanced analysis without programming? Do you want to look beyond the horizon and use Excel to its maximum with the cooperation of other applications? Do you analyze large volumes of data and wait for the recalculation of formulas with VLOOKUP function? Do you download data using MS Query tool , but the amount of JOIN operations in queries from time to time betrays you? Using a pivot table, but their expressive power is not good enough for you? It is high time to use Excel IN DIFFERENT WAY. Our new training is designed for all experienced users who want to use the analytical capabilities of data-oriented Microsoft server products from MS Excel 2013. Excel itself is a powerful analytical tool. But when using MS Excel as the client tools for MS SQL Server, the possibilities are gaining a new dimension. When traveling outside the Excel await PowerPivot, Power Query or DAX (Data Analysis Expressions).

»

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.

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

  • 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
    • Current offer
      Training location
      Course language

      The prices are without VAT.