HOW TO: - Hierarchy using the category, subcategory & product fields (columns “Product Category” “Product SubCategory”, & “Product Name”). - Group the values ​​of the column "Region" into 2 groups, alphabetically, based on the name of each region.

1. Display a table, which shows, for each value of the product hierarchy you created above, the total amount of sales ("Sales") and profitability ("Profit").
2. The same information as the previous point (2) in a bar chart illustration.
3. Display columns with the total sales amount ("Sales") for each value of the alphabetical grouping of the Region field you created. The color of each column should be derived from the corresponding total shipping cost (“Shipping Cost”). In the Tooltip of the illustration all numeric values ​​should have a currency format.
4. The same diagram as above (3), with the addition of a data filter at visual level filter that will display only the data subset related to sales with positive values ​​for the field "Profit".
5. The same diagram with the above point (3), with the addition of a data filter at visual level filter that will display only the subset of data related to sales with negative values ​​for the field "Profit".
6. Map showing the total amount of sales (size of each point), as well as the total profitability (color of each point). Change the dimensions of the image

Adventure Works 2022 dataset

How this Dataset is created?

On the official website the dataset is available over SQL server (localhost) and CSVs to be used via Power BI Desktop running on Virtual Lab (Virtaul Machine). As per first two steps of Importing data are executed in the virtual lab and then resultant Power BI tables are copied in CSVs. Added records till year 2022 as required.

How this Dataset may help you?

this dataset will be helpful in case you want to work offline with Adventure Works data in Power BI desktop in order to carry lab instructions as per training material on official website. The dataset is useful in case you want to work on Power BI desktop Sales Analysis example from Microsoft website PL 300 learning.

How to use this Dataset?

Download the CSV file(s) and import in Power BI desktop as tables. The CSVs are named as tables created after first two steps of importing data as mentioned in the PL-300 Microsoft Power BI Data Analyst exam lab.

Warning: Large file size (over 1GB). Each monthly data set is large (over 4 million rows), but can be viewed in standard software such as Microsoft WordPad (save by right-clicking on the file name and selecting 'Save Target As', or equivalent on Mac OSX). It is then possible to select the required rows of data and copy and paste the information into another software application, such as a spreadsheet. Alternatively, add-ons to existing software, such as the Microsoft PowerPivot add-on for Excel, to handle larger data sets, can be used. The Microsoft PowerPivot add-on for Excel is available from Microsoft http://office.microsoft.com/en-gb/excel/download-power-pivot-HA101959985.aspx Once PowerPivot has been installed, to load the large files, please follow the instructions below. Note that it may take at least 20 to 30 minutes to load one monthly file. 1. Start Excel as normal 2. Click on the PowerPivot tab 3. Click on the PowerPivot Window icon (top left) 4. In the PowerPivot Window, click on the "From Other Sources" icon 5. In the Table Import Wizard e.g. scroll to the bottom and select Text File 6. Browse to the file you want to open and choose the file extension you require e.g. CSV Once the data has been imported you can view it in a spreadsheet. What does the data cover? General practice prescribing data is a list of all medicines, dressings and appliances that are prescribed and dispensed each month. A record will only be produced when this has occurred and there is no record for a zero total. For each practice in England, the following information is presented at presentation level for each medicine, dressing and appliance, (by presentation name): - the total number of items prescribed and dispensed - the total net ingredient cost - the total actual cost - the total quantity The data covers NHS prescriptions written in England and dispensed in the community in the UK. Prescriptions written in England but dispensed outside England are included. The data includes prescriptions written by GPs and other non-medical prescribers (such as nurses and pharmacists) who are attached to GP practices. GP practices are identified only by their national code, so an additional data file - linked to the first by the practice code - provides further detail in relation to the practice. Presentations are identified only by their BNF code, so an additional data file - linked to the first by the BNF code - provides the chemical name for that presentation.

1. Data Import and Quality Check:
2. Utilize Power Query to import the dataset.

3. Conduct a thorough examination of the dataset to identify any anomalies or inconsistencies.

4. Duplicate Removal:

5. Identify and remove duplicate rows within the dataset.

6. Ensure data integrity by eliminating redundant entries.

7. Standardization of Marital Status and Gender:

8. Replace 'M' with 'Married' and 'S' with 'Single' in the Marital Status column.

9. Standardize gender data by replacing 'M' with 'Male' and 'F' with 'Female'.

10. Commute Distance Standardization:

11. Modify "10+ Miles" to "Above 10 Miles" for uniformity.

12. Arrange Commute Distance in ascending order to facilitate analysis.

13. Age Group Classification:

14. Introduce an additional column named "Age Group" for age categorization.

15. Calculate ages from existing data, categorizing: - Below 30 years as "Young Adults". - Between 31 and 45 years as "Middle-aged Adults". - Above 45 years as "Old-aged Adults".

6.Verification and Data Loading: - Validate all transformations to ensure accuracy and coherence. - Load the refined dataset back into Excel for further analysis.

Bi Power World Wide Z Limited Export Import Data. Follow the Eximpedia platform for HS code, importer-exporter records, and customs shipment details.

*****Documentation Process***** 1. Data Preparation: - Upload the data into Power Query to assess quality and identify duplicate values, if any. - Verify data quality and types for each column, addressing any miswriting or inconsistencies. 2. Data Management: - Duplicate the original data sheet for future reference and label the new sheet as the "Working File" to preserve the integrity of the original dataset. 3. Understanding Metrics: - Clarify the meaning of column headers, particularly distinguishing between Impressions and Reach, and comprehend how Engagement Rate is calculated. - Engagement Rate formula: Total likes, comments, and shares divided by Reach. 4. Data Integrity Assurance: - Recognize that Impressions should outnumber Reach, reflecting total views versus unique audience size. - Investigate discrepancies between Reach and Impressions to ensure data integrity, identifying and resolving root causes for accurate reporting and analysis. 5. Data Correction: - Collaborate with the relevant team to rectify data inaccuracies, specifically addressing the discrepancy between Impressions and Reach. - Engage with the concerned team to understand the root cause of discrepancies between Impressions and Reach. - Identify instances where Impressions surpass Reach, potentially attributable to data transformation errors. - Following the rectification process, meticulously adjust the dataset to reflect the corrected Impressions and Reach values accurately. - Ensure diligent implementation of the corrections to maintain the integrity and reliability of the data. - Conduct a thorough recalculation of the Engagement Rate post-correction, adhering to rigorous data integrity standards to uphold the credibility of the analysis. 6. Data Enhancement: - Categorize Audience Age into three groups: "Senior Adults" (45+ years), "Mature Adults" (31-45 years), and "Adolescent Adults" (<30 years) within a new column named "Age Group." - Split date and time into separate columns using the text-to-columns option for improved analysis. 7. Temporal Analysis: - Introduce a new column for "Weekend and Weekday," renamed as "Weekday Type," to discern patterns and trends in engagement. - Define time periods by categorizing into "Morning," "Afternoon," "Evening," and "Night" based on time intervals. 8. Sentiment Analysis: - Populate blank cells in the Sentiment column with "Mixed Sentiment," denoting content containing both positive and negative sentiments or ambiguity. 9. Geographical Analysis: - Group countries and obtain additional continent data from an online source (e.g., https://statisticstimes.com/geography/countries-by-continents.php). - Add a new column for "Audience Continent" and utilize XLOOKUP function to retrieve corresponding continent data.

*****Drawing Conclusions and Providing a Summary*****

• The data is equally distributed across different categories, platforms, and over the years.
• Most of our audience comprises senior adults (aged 45 and above).
• Most of our audience exhibit mixed sentiments about our posts. However, an equal portion expresses consistent sentiments.
• The majority of our posts were located in Africa.
• The number of posts increased from the first year to the second year and remained relatively consistent for the third year.
• The optimal time for posting is during the night on weekdays.
• The highest engagement rates were observed in Croatia then Malawi.
• The number of posts targeting senior adults is significantly higher than the other two categories. However, the engagement rates for mature and adolescent adults are also noteworthy, based on the number of targeted posts.

This dataset represents a large tertiary-care hospital with 25 clinical departments and a total capacity of 5500 inpatient beds, including 1080 ICU beds. All data is fully synthetic and created for educational and analytical purposes. It is designed to support:

• hospital capacity dashboards,
• healthcare data analytics,
• emergency surge simulations,
• opеrational decision-making models,
• business intelligence (Power BI, Tableau, Looker) projects.

The dataset approximates real-world bed allocation patterns across major clinical specialties such as Emergency Care, Surgery, Pediatrics, ICU, Oncology, and Long-Term Care.

To maintain realism, departments have varying occupancy levels: - some are under low load (free capacity), - some operate under normal/medium load, - several are intentionally modeled as overloaded/high occupancy, to reflect real hospital dynamics.

All metrics simulate plausible hospital operations.

Analyzing sales data is essential for any business looking to make informed decisions and optimize its operations. In this project, we will utilize Microsoft Excel and Power Query to conduct a comprehensive analysis of Superstore sales data. Our primary objectives will be to establish meaningful connections between various data sheets, ensure data quality, and calculate critical metrics such as the Cost of Goods Sold (COGS) and discount values. Below are the key steps and elements of this analysis:

1- Data Import and Transformation:

• Gather and import relevant sales data from various sources into Excel.
• Utilize Power Query to clean, transform, and structure the data for analysis.
• Merge and link different data sheets to create a cohesive dataset, ensuring that all data fields are connected logically.

2- Data Quality Assessment:

• Perform data quality checks to identify and address issues like missing values, duplicates, outliers, and data inconsistencies.
• Standardize data formats and ensure that all data is in a consistent, usable state.

3- Calculating COGS:

• Determine the Cost of Goods Sold (COGS) for each product sold by considering factors like purchase price, shipping costs, and any additional expenses.
• Apply appropriate formulas and calculations to determine COGS accurately.

4- Discount Analysis:

• Analyze the discount values offered on products to understand their impact on sales and profitability.
• Calculate the average discount percentage, identify trends, and visualize the data using charts or graphs.

5- Sales Metrics:

• Calculate and analyze various sales metrics, such as total revenue, profit margins, and sales growth.
• Utilize Excel functions to compute these metrics and create visuals for better insights.

6- Visualization:

• Create visualizations, such as charts, graphs, and pivot tables, to present the data in an understandable and actionable format.
• Visual representations can help identify trends, outliers, and patterns in the data.

7- Report Generation:

• Compile the findings and insights into a well-structured report or dashboard, making it easy for stakeholders to understand and make informed decisions.

Throughout this analysis, the goal is to provide a clear and comprehensive understanding of the Superstore's sales performance. By using Excel and Power Query, we can efficiently manage and analyze the data, ensuring that the insights gained contribute to the store's growth and success.

This is a synthetic dataset generated to mimic real-world e-commerce return management scenarios. Since actual return data is often confidential and unavailable, this dataset was created with realistic assumptions around orders, products, customers, and return behaviors.

It can be used for:

Predictive modeling of return likelihood (classification problems).

Business analytics on profitability loss due to returns.

Sustainability analysis (CO₂ emissions and waste impact from reverse logistics).

📌 Dataset Features (Columns)

Order_ID → Unique order identifier.

Product_ID → Unique product identifier.

User_ID → Unique customer identifier.

Order_Date → Date when the order was placed.

Return_Date → Date when the product was returned (if returned).

Product_Category → Category of the product (e.g., Clothing, Electronics, Books, Toys, etc.).

Product_Price → Price of the product per unit.

Order_Quantity → Number of units purchased in the order.

Discount_Applied → Discount percentage applied on the product.

Return_Status → Whether the order was Returned or Not Returned.

Return_Reason → Reason for return (e.g., Damaged, Wrong Item, Changed Mind).

Days_to_Return → Number of days taken by customer to return (0 if not returned).

User_Age → Age of the customer.

User_Gender → Gender of the customer (Male/Female).

User_Location → City/region of the customer.

Payment_Method → Mode of payment (Credit Card, Debit Card, PayPal, Gift Card, etc.).

Shipping_Method → Chosen shipping type (Standard, Express, Next-Day).

Return_Cost → Estimated logistics cost incurred when a return happens.

Profit_Loss → Net profit or loss for the order, considering product price, discount, and return cost.

CO2_Saved → Estimated CO₂ emissions saved (if return avoided).

Waste_Avoided → Estimated physical waste avoided (in units/items).

💡 Use Cases

MBA & academic projects in Business Analytics and Supply Chain Management.

Training predictive models for return forecasting.

Measuring sustainability KPIs (CO₂ reduction, waste avoidance).

Dashboards in Power BI/Tableau for business decision-making.

Quick Start Example:

Import libraries

import pandas as pd

Load dataset (replace with Kaggle path)

df = pd.read_csv("/kaggle/input/synthetic-ecommerce-returns/returns_sustainability_dataset.csv")

View first 5 rows

print(df.head())

Summary of columns

print(df.info())

Check return distribution

print(df['Return_Status'].value_counts(normalize=True))

Example: Average return rate by product category

category_returns = df.groupby('Product_Category')['Return_Status'].mean().sort_values(ascending=False) print(category_returns)

Data Import and Table Selection: Import Excel data into Power BI. Select specific tables (Calendar, Customer, Product, Sales, Terriority). Data Modeling: Design star schema architecture in Model view. Establish relationships between tables. Data Transformation: Filter Calendar table for years 2017 and 2018. Remove unnecessary columns from the Calendar table. Utilize Power Query Editor for data manipulation. DAX Measures: Create measures for analyzing sales data. Use DAX functions to calculate total sales, tax amount, total orders, distinct product count, etc. Add comments to DAX measures for clarity. Visualization: Create matrices to display summarized data. Format measures (e.g., change to currency). Utilize visual elements like icons and tooltips for better understanding. Drill-Down Analysis: Implement drill-down functionality to explore data hierarchically. Additional Measures: Calculate total customers and percentage of distinct customers. Analyze product-related metrics (e.g., max price, weight values). Data Quality Analysis: Identify and analyze empty cells in specific columns. Multiple Sheets and Visuals: Create multiple sheets with different matrix tables. Utilize slicers for interactive filtering. Implement visual filters for dynamic data exploration. Advanced DAX Functions: Utilize SUMX function for calculating total sales including tax. Calculate dealer margin using SUMX function. Conclusion: Summarize the project and its focus on measures, matrix tables, and advanced DAX functions. Overall, your project plan covers various aspects of data analysis and visualization in Power BI, from data import to advanced calculations and visualization techniques, providing a comprehensive guide for analysis and decision-making.