Data Analysis by Excel, SQL, Python and Power BI

Analyze a retail store case study to answer questions on max/min sales and profit, segment income, and shelf counts, using SQL and Python with Excel practice and Power BI visuals.

Format, clean, and analyze data in Excel using charts and graphs for clear visualization. Master data connections, formatting rules, data quality checks, and descriptive statistics to derive insights.

Discover data connections in Excel, importing data from local files, csv formats, databases, Azure, and web sources, and learn loading, transforming, and preparing data for analysis.

Format data in Excel with sort, filter, and slicer controls, and refine font style, size, colors, case, alignment, and borders to improve readability and prepare dashboards.

Explore data cleaning techniques in Excel, addressing missing data, outliers, inconsistent and invalid data, duplicates, and data type issues to prepare clean, analyzable datasets.

Explore descriptive statistics in Excel, covering mean, mode, median, quartile, percentile, deviation, standard deviation, and variance, using sum if, count ifs, max, and min, via the data analysis tool.

Discover how to enable Excel's data analytics toolkit by activating the Analysis Toolpak VBA add-in through File > Options > Add-ins, then refresh to access the analysis section.

Explore how the Excel data analysis tool delivers descriptive statistics, including mean, median, mode, variance, and standard deviation. Learn to select data and output results on sheet with 95% confidence.

Master using VLOOKUP in Excel to locate values in large datasets, using a lookup value, table array, and a column index with exact or approximate matches.

Explore how a pivot table in Excel analyzes and summarizes data, showing sales by ship mode and class, with customizable value field settings (sum, count, average).

Create and analyze pivot tables in Excel by placing segments in rows and regions in values, then customize layout, totals, and charts to reveal regional and segment insights.

Format pivot tables by turning raw labels into descriptive row headers, switch to tabular form via the design tab, and customize grand totals.

Explore correlation as the relation between two numerical variables, using scatter plots to identify positive, negative, or no relation, and demonstrate Excel’s correlation tool with sales data.

Merge multiple data tables in Excel with Power Query Editor to combine sales, customer, and product data into one worksheet using the primary key.

Merge product, customer, and sales data in Excel, then apply insert slicer to filter by ship mode or same day, updating all related records in a table design.

Learn how to generate random numbers in Excel with rand and rand between to create dummy data for testing and analysis, and control automatic updates and dataset creation.

Explore how to create and interpret charts and graphs in Excel, including line, scatter, histogram, box, bar, and pie charts, with pivot tables to summarize data for stakeholder insights.

Explore the SQL section of data analysis, covering commands and operators, SQL server interface, import, primary and foreign keys, clauses, group by, joins, unions, subqueries, and aggregate and comparison operators.

Install SQL Server to enable data analysis with SQL, Excel, Python, and Power BI, establishing end-to-end analytics workflows across popular tools.

Explore the SQL Server interface, connect to the server, and navigate the toolbar, object explorer, and notebook in Azure Data Studio to manage databases and imported data.

Learn to import data into a SQL Server database by creating a new database, using the import flat file task, browsing data, and configuring options for a successful import.

Learn how the select and from commands retrieve data from a database, returning a result set and enabling queries on product data and customer data in SQL Server.

Learn how to use the select distinct statement in SQL Server to return unique values from a column, and count how many distinct values exist.

Learn how to use logical operators and, or, not, and between in data analysis with Excel, SQL, Python, and Power BI, especially within the where clause to handle complex conditions.

Explore SQL comparison operators—equals, not equals, greater than, less than, and their use in the where clause to filter numeric data like order_line and sales.

Understand how aggregate functions produce single values per group using min, max, count, sum, and average, and apply them with group by to analyze sales data.

Discover how the where clause filters records by conditions, combines them with and and or, and uses count as an aggregate function to analyze furniture categories.

Explore how the where clause filters records using logical and comparison operators to build precise queries. See how to filter furniture category and count chairs.

Apply the where clause with logical operators such as between, like, and equals to filter data in the city column, using examples like Los Angeles to illustrate ranges and matches.

Learn how to use the group by statement in SQL Server to group rows with the same values and apply aggregate functions such as count, max, minimum, sum, and average.

Explore how to apply group by on the sales table to summarize total sales, quantity, discount, and profit by ship mode, including data type changes.

Learn how the order by statement sorts results in ascending or descending order in SQL Server, and apply it to sort total sales with the descending keyword alongside group by.

Learn how to use the having clause with group by, comparing it to where, with examples using total sales, ship mode, and aggregate functions.

Understand the differences between where and having clauses, including how where filters rows and can be used without group by, while having applies after a group by to grouped data.

Explain the difference between primary keys and foreign keys, and how a foreign key refers to a primary key to link parent and child tables.

Explore how inner joins connect sales, customer, and product tables in SQL Server, using primary and foreign keys, and aliases to combine data for analysis.

Learn how the union operator combines result sets from multiple select statements, requiring matching column count, data types, names, and the same order, and see practical examples with city data.

Explore inner and outer queries, including subqueries, joins, and aggregations. Learn to write inner and outer queries with where and group by, and analyze sales and customer data.

Apply SQL Server commands and clauses to a retail case study using sales, customer, and product data. See how a manager analyzes problems and evaluates results.

Import data from a flat file into SQL Server, merge sales order, product, and customer tables, and solve case study questions with joins, subqueries, and group by for sales insights.

Learn to analyze sales data with joined tables, computing regional revenue using group by and max, sum, filtering with where between 2020 and 2022, and exploring discount patterns by segment.

Learn python basics, data cleaning, and analysis methods (mean, median, mode, standard deviation, correlations), then master data grouping, merging, binning with cut, cross tab, and visualization with matplotlib and seaborn.

Install the Anaconda distribution for Python to enable streamlined data analysis workflows within a suite that includes Excel, SQL, Python, and Power BI.

Explore how to launch python for data analysis using anaconda navigator, open and name jupyter notebooks, and work with code and markdown cells for data exploration.

Explore Python data types, including string, integers, float, complex, boolean, dictionary, list, tuple, set, and non types, and learn how their properties behave when applying variables in analysis.

Explore variables, values, and print statements in Python, including storing integers and strings, recalling values, and printing single or multiple variables in a Jupyter notebook.

Learn how Python casting converts values to different data types like str, int, and float, and explore case sensitivity with uppercase versus lowercase variable names.

Learn what a Python module is, a file containing data and code that can be imported to reuse dictionaries and variables, such as an info dictionary with a name key.

Compare modules and libraries, defining a library as a collection of modules. Explore built-in Python libraries such as numpy, pandas, matplotlib, and seaborn for data analytics.

Explore numpy and the array data type to convert lists into efficient numeric arrays, speeding computations in Python with import numpy as np.

Explore the pandas library for data analytics, showing how to convert dictionaries to series or data frames and explaining one- and two-dimensional structures and indexing.

Explore the matplotlib library in Python to visualize data with charts and plots. Label axes, add a title, and use legends and markers to compare multiple data series in Jupyter.

Learn how to use Seaborn, a matplotlib-based data visualization library, to create scatter, bar, and line plots with numerical and categorical data, and customize axes and titles in Jupyter.

Learn how to import data in Jupyter Notebook using pandas, including reading CSV files with pd.read_csv, handling file paths, and naming datasets for data analytics.

Explore data wrangling as a core step in data analytics, cleaning data, handling missing values and formatting errors, and merging and grouping datasets in Python for analysis.

Identify missing data, outliers, and inconsistent values, and clean them by dropping rows or imputing with moving averages or medians using pandas in a Jupyter notebook.

learn how to identify and handle missing data in Python using isna, count missing values with sum, and clean data with dropna, fillna, and moving average.

Apply numpy percentile to detect outliers in income data, identify upper and lower extremes, and replace or drop outliers to improve the mean and descriptive statistics for cleaner data.

Identify and fix inconsistent country names by converting to uppercase and correcting typos, addressing formatting mistakes, outliers, and missing values to improve data quality for analysis.

Learn to clean data by fixing typos in string columns like country names, permanently standardizing to uppercase, and handling abbreviations such as USA and UK to improve dataset quality.

Learn to clean invalid data in the age column by replacing 200 with a chosen number like 50, and compare methods for removing or imputing missing values.

Identify duplicate rows in a dataset and remove them to prevent biased results, using dataframe.drop_duplicates with inplace and the keep option to choose which duplicates to drop.

Learn to identify and fix data type issues in data frames by converting columns to proper types (int, float, date), handling missing values, and validating results with pandas tools.

Explore data analysis in Python: group by to aggregate sales, merge datasets with primary and foreign keys, and cross tabulate multiple variables in a Jupyter notebook.

Merge datasets with pandas to combine customer, sales, and product data using primary and foreign keys, choosing left or right joins and saving the merged result for analytics.

Explore cross tabulation in pandas to analyze how ratings relate to segments and shelves, using multi-column cross tabs to reveal occurrences and relationships across category pairs.

Learn to use pandas cut to segment data into bins, such as age into young one, middle one, and old one, with equal-width or manual ranges, and add bin column.

Explore core data analysis methods—mean, median, mode, standard deviation, and variation—using Excel and basic examples; learn how to compute, assess spread, identify outliers, and prepare for Python and Jupyter workflows.

Apply mean, median, mode, standard deviation, and variance to sales data in Python, using columns like sales quantity, discount, and profit.

Explore quartiles, percentiles, maximum, minimum, and count to locate where values lie in the data, using NumPy's quantile in a Jupyter notebook with a sales quantity column, and identify outliers.

Learn how to use pandas in Jupyter Notebook to inspect data with info, summarize numerical data with describe, and explore relationships with correlation across sales and profit.

Explore essential python data analysis methods, like head, tail, loc and iloc, sort, n unique, shape, dtype, and value_counts, to inspect and summarize datasets.

Explore graphs and charts in Python using Matplotlib and Seaborn to visualize income and expenses with histogram, bar chart, line plot, and scatter plot.

Explore case study in Python using Pandas in a Jupyter notebook to merge datasets, group by product, compute max/min sales and profit, and rank by revenue and export classes.

Apply pandas techniques to a case study: group by region to identify top revenue, use cut with bins for age groups, and aggregate, sort, and look up product quantities sold.

Install and configure Power BI Desktop to begin data analysis using Excel, SQL, Python, and Power BI workflows.

Power BI desktop interface; get data from SQL Server and Excel; transform data with Power Query editor; build visuals in report view to analyze data.

Import data from Excel, SQL Server, and Python scripts into Power BI, transform data in Power Query Editor, rename columns, set data types, and load results.

Design a Power BI dashboard from SQL Server queries, importing data from SQL Server, Python, and Excel, and visualize retail analytics with bar, line, area, waterfall, gauge, and map charts.

Learn to create and format visuals in Power BI by selecting chart types, mapping data to axes, and applying formatting options for axes, data labels, borders, tooltips, and titles.

Learn to create and edit relationships in Power BI, linking sales order and customer data via customer ID, understand many-to-one cardinality, and apply cross-table filtering in visuals.