Advanced Data Analytics Course: Power BI & Python

• Combine / Append Data from Multiple Workbooks

• Combine All Files in a Folder (with Excel Tables)

• Import All Sheets BUT Data Starts from Different Rows

• Import from Folder – Mixed Letter Case Headers & Trailing Spaces

• Better Collaboration – File Path as Parameter in Power Query

• Overcome Potential Errors when Combining Sheets

• Using Power Query Parameters

• Assign Unique Number to Group

• Create a Calendar Table From Start to End Date

• Defining & Invoking Custom M Functions

• Error Handling – Bulk Replace Lookup with Try Otherwise

• How to Use List Functions in Custom Column

• Advanced Pivot & Unpivot

• Unpivot & Consolidate Data From Multiple Sheets (with Custom Function)

• IF Then & Lookup Operators to Lookup Values in Previous Row

• Lists & Records and how to Reference a Specific Cell in a Table

• M Language – How M Thinks (let Expression & Values)

• Merge Values from Rows into One Cell

• Power Query Text Functions (Text.Contains, Text.Replace)

• Query Folding

• Rank by Group (Nested Rank) with Power Query

• Running totals by Month (Invoking functions)

• Speed up Queries – Table.Buffer & How to Test Impact (Hidden Tip)

• Understand Each Keyword & the Purpose of _ (underscore)

• Introduction to Python

• Setting Up Anaconda: Your Python Environment

• Intro to Pandas and NumPy libraries

• Mastering Dictionaries and Dataframes in Python

• Essential Dataframe Functions for Data Manipulation

• Combining and Filtering Data in Python

• Data Visualization: Unleashing the Power of Python

• Creating Python-Enhanced Reports

• Introduction to String Functions

• Mastering User-Defined Functions in Python

• Building Cleaning Functions for Data Preprocessing

• Visualizing Cleaned Data: Unveiling Patterns and Insights

• Building an Interactive Heatmap Dashboard in Python

• Lists and For Loops: Harnessing the Power of Iteration

• Exploring Three Ways to Harness Python Scripting in Power BI

• What is a Data Model?

• Principles of data normalization

• Understanding data tables vs. lookup tables

• Creating table relationships in Power BI Desktop

• Design a data model that uses a Star schema

• Snowflake schemas in Power BI

• Managing & editing table relationships in Power BI Desktop

• Managing active vs. inactive relationships

• Understanding relationship cardinality

• Understanding filter flow

• Two-way filters in Power BI Desktop (USE WITH CAUTION!)

• Hiding fields from the Power BI report view

• Table and column properties

• Common date table (using DAX and Power Query)

• Hierarchies and data granularity

• Role playing dimensions

• Power BI data model best practices

• Basic DAX operators

• Intro to DAX calculated columns

• Intro to DAX Measures

• Implicit vs. Explicit Measures

• Understanding Filter Context

• Logical, statistical, mathematical and text functions

• Iterator (X) functions

• Information, Filter & Value and Time Intelligence functions

• Earlier and Earliest functions

• LASTNONBLANKVALUE function

• RANKX function with Slicer

• RANKX, TOPN, CROSSFILTER, TREATAS functions

• WINDOW, INDEX and OFFSET functions

• CALCULATE with or without FILTER

• SELECTEDVALUE and SELECTEDMEASURE functions

• Calculation Groups

• DAX Query View

• DAX INFO.VIEW Functions

• AI Documentation with TMDL

• Exploring the Report view in Power BI Desktop

• Visualize Data in the Form of Various Charts, Plots, and Maps

• Applying slicing and filtering

• Sorting

• Synch slicers

• Using themes

• Drillthroughs and drill downs

• Bookmarks

• Page navigation

• Edit interactions

• Anomaly detection 

• Decomposition trees 

• Custom Visuals

• New Button Slicer

• New Card Visual

•  Study relationships between a dependent variable and one or more independent variables.

• Use cases: predicting sales, revenue, customer behavior, or other key business metrics.

• Simple (one independent variable) vs Multiple Regression (multiple independent variables).

• When to use regression for business insights and decision-making.

•  Overview of implementing regression using built-in visuals, DAX.

• How to interpret regression outputs: coefficients, R², residuals, and predicted vs actual value

•  Logistic Regression – used for categorical outcomes (e.g., fraud detection, purchase/no purchase).

• Key Metrics for Logistic Regression (Accuracy, Precision & Recall, F1 Score, Confusion Matrix, ROC Curve & AUC (Area Under Curve))

•  Introduction to Time Series

• Smoothing Techniques

•  Trend and Seasonality Modeling: Additive and Multiplicative Models

Machine Learning & Predictive Analytics with Python

• Introduction to Machine Learning for Analytics.

• What ML is and why it matters for data-driven decisions.

• Key types of ML: supervised vs unsupervised learning, with business-focused examples.

Regression Models as ML Techniques

• Linear regression: modeling relationships between variables.

• Polynomial regression: handling non-linear patterns.

• How regression fits into the broader ML landscape.

Model Evaluation & Insights

• Metrics: R², RMSE, MAE, MSE for regression performance.

• Interpreting results for business decision-making.

Integration with Power BI

• Import Python ML outputs into Power BI dashboards.

• Visualize predicted vs actual values for actionable insights.

• Demographic, geographic, behavioral, psychographic, and Recency, Frequency, Monetary (RFM) based segmentation.

• Includes clustering methods such as K-Means for data-driven grouping.

Market Basket Analysis (Frequently Bought Together Items) with Python and Power BI

• Understand the importance of data storytelling for decision-making and stakeholder communication.

• Learn sentiment analysis basics: extracting positive, negative, and neutral sentiment from textual data.

• Use Python in Power BI to calculate sentiment scores.

• Build interactive dashboards that convey insights clearly using charts, KPIs, and narrative elements.

• Highlight trends, sentiment changes over time, and key insights for business actions.

• Understand the purpose of ABC-XYZ analysis in classifying products or customers based on value and demand variability.

• Learn how combining ABC (value-based) and XYZ (stability-based) methods supports smarter inventory and sales decisions.

• Categorize products or customers using sales volume, revenue, or frequency metrics to reveal key business drivers.

• Identify stable, irregular, and unpredictable demand patterns to optimize forecasting, stock levels, and resource allocation.

• Build automated ABC-XYZ classification dashboards using DAX and Power Query for segmentation and monitoring.

• Visualize performance by category to highlight high-value, high-stability items and guide inventory or marketing actions.

• Understand what a cohort is and how cohort analysis helps reveal behavioral patterns over time.

• Learn why cohort-based metrics provide deeper insights than overall averages for churn and retention.

• Analyze customer groups based on their first purchase or signup to measure long-term retention and churn rates.

• Identify trends in customer engagement and retention using time-based cohort comparisons.

• Build cohort and retention dashboards in Power BI using DAX, date tables, and calculated measures.

• Visualize retention matrices and curves to uncover actionable insights for customer retention strategies.