PRACTICAL DATA ANALYTICS USING EXCEL VBA & POWER BI WITH DAX

This is an introduction to the course presenter Dr Alida Herbst. Teaching and sharing knowledge as well as making life easier through automation of processes is her passion. Alida loves working with data and used VBA, SQL, Python and Microsoft power BI extensively. Throughout her life she has written numerous applications using SQL and VBA. She has worked at numerous financial institutions as a data scientist, fund manager, risk analyst and researcher. She has also been privileged to teach at the University of Pretoria in South Africa. She obtained her PhD in statistics in 1994 on modelling non-linear growth. She won 2 governors and numerous team awards at the South African Reserve Bank (SARB) through automation of processes in the Financial Markets Department (FMD). She loves life and is passionate about everything she does. Her motto is to maximize and go with the flow..

Introduction to the 9 chapters for the course Practical  Data Analytics using Excel VBA and Power BI,

Chapter 1  EXCEL AND VBA BASICS

This chapter will cover the concepts, principles, and terminology of programming languages.  A quick overview of the programming language of Microsoft Excel called Visual Basic for Applications (VBA) including declaring variables, message and input boxes, iterative loops, popular statements and benefits are also covered. Data analytics and the different types are also taught. How to make the developer tab visible is demonstrated

Chapter 2  IMPORTING DATA AND USING VBA FOR PRE-PROCESSING

The importing of data into EXCEL from a webpage, that will then be used throughout the course to analyse and present the data, is demonstrated. The programming language VBA, with an ActiveX command box, is used to prepare the data in the correct format for importing into the Power BI application of Microsoft. The VBA program makes use of variable declaration, a message box, arrays and iterative loops. The case study using GDP (Gross Domestic Product which is a country’s total economic output) and population for more than 266 countries from 1960 to 2020 obtained from the world bank is introduced.

Chapter 3 POWER BI WHERE, WHY AND WHEN

What is business intelligence (BI) as well as where and why it is used. BI strategies, tools, techniques, trends, software and systems are covered. The software Microsoft power BI parts, concepts and dashboard with its features are discussed. The power BI report, data and model views are covered.

Chapter 4  VISUALS, IMPORT, LINKING OF TABLES AND CREATING FIELDS

Standard Power BI visuals and best practices are covered. How to import the case study into power BI is shown in a video. The power BI query is introduced in a video where new fields for the case study are created using the power query editor and M-language. How to link tables are demonstrated.

Chapter 5 DAX AND THE M-LANGUAGE

The two languages of Power BI namely the M-language and DAX are discussed. The power query used for the M-language is shown along with its interface. The differences between the M language and DAX are pointed out as well as when to use M, and when do I use DAX.

Chapter 6  M-LANGUAGE CALCULATIONS

The key points about power query and the M-Language as well as the M-language syntax is discussed in this chapter. Some of the M-language functions are covered. Calculations using the M-language for the case study is demonstrated in a video.

Chapter 7 DAX CALCULATIONS

What DAX is, its key points and importance is discussed. DAX calculations for measures, columns, tables and row-level security is covered. DAX functions and statements are demonstrated. DAX calculations using Case study is done through a video.

Chapter 8  BASIC STATISTICS AND VISUALIZATIONS

Exploratory data analysis (EDA) statistical functions including measures of central tendencies and dispersion along with shape properties kurtosis and skewness are discussed. Practical examples of the standard visualizations in power BI are given along with the formatting of the visuals. How to calculate a DAX measure using the concatenate function is also covered

Chapter 9  COMPARATIVE ANALYSES USING THE SLICER

GDP growth comparative analyses of BRICS countries (Brazil, Russia, India, China, South Africa) is demonstrated through videos. Comparisons for BRICS countries using box plots on GDP per capita is illustrated for any chosen year from 1960 to 2020 . The line graphs, boxplots and scatter visualizations are all done using a slicer for choosing different countries or years.

This chapter will cover the concepts, principles, and terminology of programming languages. What is a syntax and an expression as well as how to develop program solving skills are discussed. A quick overview of the programming language of Microsoft Excel called Visual Basic for Applications (VBA) including declaring variables, message and input boxes, iterative loops, popular statements and benefits are also covered. Data analytics and the different types are also taught. How to make the developer tab visible is explained in this chapter.

The importing of data into EXCEL from a webpage, that will then be used throughout the course to analyse and present the data, is demonstrated.  The case study using GDP (Gross Domestic Product which is a country’s total economic output) and population for more than 266 countries from 1960 to 2020 obtained from the world bank is introduced.

In this  section of chapter 2 how to write a VBA application to pre-process the case study in the right format for importing into Power BI is demonstrated.

Chapter 3 discusses what is business intelligence (BI), where, why and when it is used. BI strategies, tools, techniques, trends, software and systems are covered. The software Microsoft power BI parts, concepts and dashboard with its features are discussed. The power BI report, data and model views are covered.

Standard Power BI visuals and best practices are covered. How to import the case study into power BI is shown in a video. The power BI query is introduced in a video where new fields for the case study are created using the power query editor and M-language. How to link tables are demonstrated.

The two languages of Power BI namely the M-language and DAX are discussed. The power query used for the M-language is shown along with its interface. The differences between the M language and DAX are pointed out as well as when to use M, and when to use DAX.

The key points about power query and the M-Language as well as the M-language syntax is discussed in this chapter. M-Language calculations using Case study are demonstrated in a few videos. Some of the M-language functions are covered.

More calculations are done on the case study using the M-language in the power query of BI. These calculations need to be done to prepare and transform the data for analyses in the next chapters.

What DAX is, its key points and importance is discussed. DAX calculations for measures, columns, tables and row-level security is covered. DAX functions and statements are demonstrated. DAX calculations using Case study is done through a video.

Exploratory data analysis (EDA) statistical functions including measures of central tendencies and dispersion along with shape properties kurtosis and skewness are discussed. Practical examples of the Line graph along with a slicer using case study power BI are given along with a discussion on how to  format visuals.

Import Box and whisker plot

Plot Box and whisker.

Use concatenate function to create a measure.

Scatter plot and cards with quick measure correlation.

GDP growth comparative analyses of BRICS countries (Brazil, Russia, India, China, South Africa) is demonstrated through videos. Comparisons for BRICS countries using box plots on GDP per capita is illustrated for any chosen year from 1960 to 2020 . The line graphs, boxplots and scatter visualizations are all done using a slicer for choosing different countries or years. Comparisons on regions and income groups for different years are also covered.