Chayanonboo

Tag: Project

  • 10 Method for Power BI to build your reports and dashboard

    10 Method for Power BI to build your reports and dashboard

    This project explores the use of Power BI to create dashboards that provide insights for improved business planning and decision-making within organizations. It also demonstrates how structured data can be presented in a clear and accessible manner through data storytelling.

    Table of Content

    1. References, Resources & Files
      1. Data Files
      2. Practice Files
    2. Navigate the power BI interface and import data
      1. Learning Objective
      2. Your Role : Data Analyst
      3. Start Power BI
    3. Transform data using Power Query
      1. Transform data process
      2. Remove column “Rush Shipment”
      3. Replace Value
    4. Insert and format a visualization
      1. Visual
      2. General
    5. Practice Data Visualization in Power BI
      1. Relationship
    6. Add more visuals to enhance storytelling
      1. Card Chart
      2. Table
    7. Publish the report to the Power BI workspace
    8. Cumulative Activity Scenario
      1. Products
      2. Inventory
    9. Create Sales reports
      1. Full Sale Report
    10. Create management reports
      1. Full Management Reports

    References, Resources & Files

    This project contains the following three files:

    Data Files

    dataDownload
    • This is an Excel file containing cookie shipping data that use to create reports have 2 sheets.
    • Sheet 1, named “Orders” shows the following data.
    1. Order ID : The cookie shipping process based on each order.
    2. Customer ID : The order of 5 customer IDs connected to the “Customers” sheet.
    3. Rush Shipment : These shipments send express or regular.
    4. Cookies Shipped : These are cookies that have been shipped to customers.
    5. Revenue : Revenue from cookie sales
    6. Cost : Costs of cookie sales
    7. Order date : The date of shipment
    • Sheet 2, named “Customers” shows details of customers who purchased products through 5 specific customers.
    1. Customer ID : Ordering of 5 customers with ID numbers linked to the “Order” sheet.
    2. Customer name : Show the information of the shipping company.
    3. Phone : The company phone number.
    4. Address : The company address
    5. City : City name
    6. State : State name
    7. Zip : Zip Number
    8. Country : Country of shipment

    The data files contain raw, uncleaned data.

    Practice Files

    practiceDownload
    • A dataset of chocolate product information for a company that use to create reports have 2 sheets.
    • Sheet 1, named “Orders” contains the following data:
    1. Order ID :The chocolate chip product distribution process based on orders.
    2. Order_date : The chocolate chip product shipping dates by order.
    3. Retail_number : List of retailers connected to the “Retailers” sheet.
    4. Quantity : The quantity of the product.
    5. Unit Price : Price per unit.
    6. Total Amount : Quantity * Unit Price
    7. Product : Chocolate Chip
    • “Sheet 2, named ‘Retailer_Names‘, shows the following data:”
    1. ID : list of retailers linked to the ‘Orders’ sheet“.
    2. Retailer_Name : The name of the retail company
    3. Country : The countries of shipment

    The practice files contain raw, uncleaned data.

    Final Files

    finalDownload
    • This data file is for Emma’s coffee shop capstone task that use to create reports have 5 sheets.
    • Sheet 1, named “Orders” contains the following data:
    1. Order ID : Show the order list of Coffee shop
    2. Customer ID :Show the customer sequence connected to the ‘Customer ID’ sheet.
    3. Product ID : Show the product sequence linked to the ‘Customer ID’ sheet.
    4. Quantity : The quantity of coffee.
    5. Unit Price : The price of coffee per unit.
    6. Order date : The date of the coffee sale.
    • Sheet 2, named “Feedback” displays the following data:
    1. Feedback ID : Order of displaying customer feedback and suggestions.
    2. Customer ID : Show the customer order based on the “Orders” sheet.
    3. Rating : Coffee Rating
    4. Feedback : Text displaying customer feedback.
    5. Feedback date : Date of coffee shop reviews.
    • Sheet 3, named “Inventory” displays the following data:
    1. Product ID : Show the product sequence from the “Orders” sheet.
    2. Stock Level : Stock levels of coffee
    3. Reorder Level : The stock level at which a new order should be placed.
    4. Supplier : Who buys products for another person.
    • Sheet 4, named “Customers ID” displays the following data:
    1. Customer ID : Show the customer list connected to the “Orders” sheet.
    2. Customer Name : Customer’s name of Coffee Shop.
    3. Email : Email of customer’s Coffee Shop.
    • Sheet 5, named “Product ID” displays the following data:
    1. Product ID : Show the product list connected to the “Orders” sheet.
    2. Product Name : Name of product in Coffee shop.
    3. Category : Category of product in Coffee shop.
    4. Price : Price of product in Coffee shop.
    • Understand the basics of spreadsheets.
    • Familiarity with data cleaning and IF/SUM functions in spreadsheets is required.
    • The Microsoft account used to publish reports to Power BI.

    Learning Objective

    • Explore additional Power BI features like filtering publishing and graphs customization.
    • Build report with visualizations
    • Manage data relations and transform data with power Query

    you could build a data model and start to build some visuals and add some filters to our data. Then, we’ll look on how to add a theme and share your report and dashboards with others.

    Your Role : Data Analyst

    You are a Data Analyst for Cookie Bliss, your mission is to create a Power BI report to help visualize key metrics more effienctly and then publish the dashboard to share it with your teammates, providing them with sales insights for better decision-making

    Start Power BI

    • Then, open Power BI.
    Blank Report
    • Then, Choose Blank Report
    Visualizations Pane

    The side column of this table highlighted in red above the picture has four rows call “Visualizations Pane“.

    Visualizations Pane TypeDefinition
    Report ViewThe Dashboard Report
    Table ViewDataset Details
    Model ViewData Relationship Model
    DAX Query ViewDAX Query Editor
    • To create a dashboard, data must be selected.

    Get Data –>Excel Workbook –>Select the file you want to use to create a dashboard.

    Get Data

    start from export file “data“

    • and can add data can be exported from various common data sources.
    • –> Choose file “data”
    • –> Choose Customer2 table and Order1 table then click load.
    Load Data

    Transform data using Power Query

    how to transform data before starting to build our report.

    • can check the Data and Visualization areas.
    Check Data and Visualization Zone
    • Picture above show the Data and Visualization areas to see how each data element should be displayed.

    Transform data process

    Let’s start in the Table view.

    1. In the Table view, the Rush Shipment column appears to be unnecessary from Order1 Table
    Rush Shipment Column

    2. In the Table view, the Customer2 table has a column with both “United States” and “US” as values, which should be standardized to one name.

    Customer2 table with United States and US

    Remove column “Rush Shipment”

    —> Choose orders 1 Table —> Choose Column Rush Shipment —> click Remove

    Remove Column Rush shipment
    • After following the steps in the image, It will get the desired columns as follows:
    • The Rush Shipment column has been removed.
    Rush Shipment column has been removed

    Then, clicking “Close & Apply” will result in a cleaned data table.

    Replace Value

    • Click Transform Data to Replace values.
    Transform Data to replace values.
    • In the Customer 2 table, replace all “US” values in the Country column with “United States.” and then click OK
    to change US to be United States
    • As a result, Country column successfully replaced value as below.
    successfully replaced value

    Insert and format a visualization

    • learn how to visualize data from multiple tables and customize graph formatting options.
    • create a line chart.
    • Use the up arrow to change the data scale (day, month, and year)
    AxisColumn of Data File
    Y-axisSum Cookies shipped
    X-axisOrder Date
    Line Chart

    Visual

    • can adjust the visual appearance of the data through the following Visual Format options.
    Visual
    • can edit Values.
    • can choose Data label to show number in graph.

    General

    • can adjust the font size.
    • can adjust the graph’s position.
    • can adjust the graph’s color.
    • can change the title.
    General

    Practice Data Visualization in Power BI

    • You are a data analyst working with a chocolate factory and would like to help them visualize their data.
    • The data is presented in the readings under “practice.xlsx” You need to import this data to Power BI, do the necessary transformation and data cleaning using Power Queries and then build the relationship betweendiffrerent columns. Finally, I need you to create a line chart that shows the evolution of sales over time.
    • start from export file “practice“
    export file “practice“
    • upload Orders table and Retailer table —> click load as below
    Orders table and Retailer table
    • Choose the Retailer Table to use Replace Value.
    • Change “UK” to “United Kingdom“.
    Change “UK” to “United Kingdom”.

    Then close and apply, and return to the dashboard.

    Relationship

    • To create a relationship between orders and retailers in Power BI
    relationship between orders and retailers.
    • drag id to retailer_number
    • then click save to connect new relationship between Retailers id and Order retailer number
    connect between Retailers id and Order retailer number
    • Now that the data is cleaned and the relationship is created.

    to create Line Chart between Order_date and Quantity

    Line Chart between Retailers id and Order retailer number
    • then click line chart
    AxisColumn Data Files
    X-AxisOrder date by day
    Y-AxisQuantity

    Now I can see the data by day of the quantity of chocolate bars sold. Now that you have a better handle of importing and preparing data into PowerBI and inserting your first visual.

    Add more visuals to enhance storytelling

    • show how to add additional graphs and pages to a report, as well as explore filtering options using maps, tables and bar graph.

    Card Chart

    • to show the total revenue that we earned here at the Cookie Bliss.
    how to create card chart
    • Choose card
    • Choose fields as sum of revenue of Orders1 Table.
    card chart with revenue

    then it show revenue value

    Table

    • Show table of customer name
    customer name
    • Drag Customer name column then it show table of customer name.
    Customer name Table

    Then it can show the interaction between Customer Name and Revenue value on the dashboard.

    Map

    • Choose map and then select country column
    • then it show maps of country
    Map

    Publish the report to the Power BI workspace

    • then can change theme of dashboard by this theme.
    Change theme
    • add the customer id and the phone number is good choice
    customer id and phone number
    • then add this title name as “Cookie Bliss Sales dashboard”
    Cookie Bliss Sales dashboard
    • publish to Power BI —> My workspace
    • When it have success It finish publish Power BI
    Publish Power BI

    Cumulative Activity Scenario

    • then get data final files to upload and do visualization
    Choose Final File

    then choose all 5 tables Customers, Feedback, Inventory, Orders and Products

    Products

    • In Product table must clean data first
    Replace Value Product table
    • Value to find “Cofe” replace with “Coffee”
    • to make category have Coffee and Pastry

    Inventory

    • In Inventory table must clean data first
    Replace Value Inventory table
    • Value to find “BeanWorld” replace with “Bean World”
    • to make supplier have BeanWorld and PastryPro

    Create Sales reports

    • Create Title Sales Report
    • Create Table
    Create table in Sale Report
    • Select Customer name and Email to create table
    • Create line chart
    Create line chart with order date and Quantity
    AxisColumn Final Files
    X-AxisOrder Date
    Y-AxisQuantity
    • Create stacked bar chart
    Create stack bar chart with Category, Product Name and Quantity
    TypeColumn Final Files
    Y-AxisCategory
    X-AxisSum of Quantity
    LegendProduct Name

    to see coffee that people love eat it.

    Full Sale Report

    Sale Report

    Create management reports

    • Create Title as management reports
    • Create card of average rating
    card of management report
    • Select Feedback table with Rating column and value with average of rating.
    • Create table with customer name and emails
    table with customer name and emails
    • Select Column as customer name and Email
    • Create stack bar chart
    Create stack bar chart with category and Stock level
    AxisColumn Final Files
    Y-AxisCategory
    X-AxisStock Level
    • Create clustered column chart
    clustered column chart with Reorder Level and Supplier
    AxisColumn Final Files
    Y-AxisCount of Reorder level
    X-AxisSupplier

    Full Management Reports

    Management Report

    Summary

    I hope this project helps users create dashboards in Power BI, improve their proficiency with the tool, and learn effective techniques for using titles, colors, and themes.

    Coursera : https://www.coursera.org/account/accomplishments/verify/YWAXF815EXNK

  • 5 Steps with Project introduction SQL

    5 Steps with Project introduction SQL

    • SQL (Structured Query Language) is a powerful programming language used for managing and manipulating relational databases. It allows users to create, retrieve, update, and delete data efficiently within a database system. SQL is widely used across industries for tasks ranging from data analysis to database management.
    5 Method Of Introduction SQL
    1. Database
    2. Tables
    3. SQL Data Types
    4. Introducing Queries
    5. Writing Queries

    Table of Content

    1. Relational Database
      1. Database
        1. Patrons Table
        2. Database Benefits
      2. Tables
        1. Definition
        2. Create Table Name
        3. Record and Field
          1. Records
          2. Fields
        4. Table manner
        5. Assigned seats
        6. Create books table
      3. SQL data types
        1. String
        2. Integers
        3. Floats
        4. Schema
    2. Querying
      1. Benefits of SQL
      2. Keyword
      3. Selecting multiple fields
      4. Selecting all fields
      5. Aliasing (Rename Column)
      6. Selecting Distinct Records
      7. Distinct with multiple fields
      8. Using views
      9. Viewing your query
      10. SQL flavors
      11. Two popular SQL flavors
      12. Comparing PostgreSQL and SQL Server

    Relational Database

    • A relational database is a type of database that organizes data into structured tables (relations) with rows and columns.

    Database

    purpose of introduction with SQL

    1. Understand databases and their structure
    2. Extract Information from databases using SQL
    Table show relation patrons, books and checkouts

    Patrons Table

    Column (field name)Definition
    card_numcard number
    namename
    member_yearthe year the patron became a library member.
    total_finethe total overdue

    Relational Database – -> relation between tables of data insider the database

    Database Benefits

    • Database have more storage than spreadsheet application.
    • Many users can write queries to gather insights from the data at the same time.
    when a database is queried, the data stored insider the database not change.

    Tables

    Definition

    • databases are organized into tables, which hold related data about a particular subject.
    • tables are organized into rows and columns.
    • in the world of databases, rows are often referred to as records and columns as fields.
    relation between patrons table and checkouts table connect with card_num column,
    book table and checkouts table connect with id column.

    Create Table Name

    • lowercase
    • no space and – in table name (use underscores instead)
    • plurals

    Record and Field

    Records

    • Laying the table : records
    • A record is a row that holds data on an individual observation.
    records pf patron table

    Fields

    • Laying the table : fields
    • A field is a column that holds one columns of data for all records.
    fields of patrons table

    Table manner

    Qualification
    Singular name
    No lowercase
    No space
    be different from other field name
    be different from the table name
    restrict of create name

    Assigned seats

    • A unique identifier is used to identify records in a table.
    • Distinct and often number.
    unique identifier

    Create books table

    • A database has been set up for this course and the books table is available here.
    • Run the code to explore what data books holds!.
    SELECT * FROM books;

    SQL data types

    • When a table is created, a data type must be indicated for each field. The data type is chosen based on the type of data that the field will hold a text and number.
    String data type : field name, Integer data type : field member_year, Floats data type : field total_fine
    SQL Data typeAttribute
    Stringletters or punctuation
    Integerswhole number
    Floatsfractional number

    String

    • String is a sequence of characters such as letters or punctuation.
    • VARCHAR is a flexible and popular string data type in SQL.
    String field : field name

    Integers

    • Integers is whole number
    • INT is popular integer data type in SQL.
    Integer field : field member_year

    Floats

    • Float store numbers that include a fractional part
    • NUMERIC is popular float data type in SQL
    Float field : field total_fine

    Schema

    A schema shows a database’s design, such as what tables are included in the database and any relationships between its tables.

    Schema show database’s design

    Querying

    Introducing queries

    Benefits of SQL

    • use SQL to find which books James checked out from the library in 2022.
    relation between card_num checkouts tables and patrons table.
    • use SQL queries to uncover trends in website traffic, customer reviews, and product sales.
    Question
    Which products had the highest sales last week?
    Which products get the worst review scores from customers?
    How did website traffic change when a feature was introduced?

    Keyword

    Keyword is word for operations. Common keywords : SELECT, FROM

    • The SELECT keyword indicates which fields should be selected
    • The FROM keyword indicates the table in which these fields are located
    Keyword SELECT and FROM
    SELECT name FROM patrons;

    Selecting multiple fields

    • Can select field to that want show data example card_num and name
    SELECT card_num, name FROM patrons;
    • It will show field that select first as picture below.
    SELECT card_num, name vs SELECT name, card_name

    Selecting all fields

    • if you want to show all data use asterisk(*) to select all four field name.
    SELECT * FROM patrons;
    SELECT * FROM patrons;

    Writing queries

    Aliasing (Rename Column)

    • Use aliasing to rename column.

    Use SELECT name AS first_name to change field name from name to be first_name.

    SELECT name AS first_name, year hired
FROM employees;
    SELECT name AS first_name

    Selecting Distinct Records

    • if you select year_hired it will show result duplicate year 2020 and 2021
    • we can add the DISTINCT keyword before the year_hired that make data show 4 year distinct.
    SELECT DISTINCT year_hired
FROM employees;
    SELECT DISTINCT year_hired FROM employees;

    Distinct with multiple fields

    • add the DISTINCT keyword before the fields to select
    • the department id and year_hired fields still have repeat values individually, but none of the records are the same
    SELECT DISTINCT dept_id, year_hired
FROM employees;
    SELECT DISTINCT dept_id, year_hired FROM employees;

    Views

    • A view is a virtual table that save SQL SELECT statement
    • When accessed, views automatically update in response to updates in the underlying data.
    CREATE VIEW employee_hires_years AS
SELECT id, name, year_hired
FROM employees;

    CREATE VIEW, then the name will create the new view.

    Using views

    • we can query it just as we would a normal table by selecting FROM the view.
    SELECT id, name
FROM employee_hire_years;
    SELECT id, name FROM employee_hire_years;
    -- create the view:
CREATE VIEW library_authors AS
SELECT DISTINCT author AS unique_author
FROM books;

-- Select all columns from library_authors
SELECT * FROM library_authors
    sample of CREATE VIEW use case.

    Viewing your query

    • You have worked hard to create the below SQL query:
    SELECT DISTINCT author AS unique_author
FROM books;

    SQL flavors

    • Both free and paid
    • All used with relational database
    • Vast majority of keywords are the same
    • All must follow universal standards
    PostgreSQLSQL Server
    Free and open-source relational database system.Has free and paid version
    Created at the university of California, BerkeleyCreate by Microsoft
    “PostgreSQL” refers to both the PostgreSQL database system and its associted SQL flavorT-SQL is Microsoft SQL flavor, used with SQL Server databases

    Comparing PostgreSQL and SQL Server

    --PostgreSQL:
SELECT id, name
FROM employees
LIMIT 2;
--SQL Server:
SELECT TOP(2) id, name
FROM employees;
    PostgreSQL vs SQL server

    SQL Server using the TOP keyword instead of LIMIT. Notice that this keyword is the only difference between the two queries!

    Summary

    “I hope the foundational knowledge for advancing projects in the following five areas will be helpful for everyone:

    1. Database
    2. Tables
    3. SQL Data Types
    4. Introducing Queries
    5. Writing Queries”

    link from : https://www.datacamp.com/completed/statement-of-accomplishment/course/105fbc7d0c0243f35770512f02a1ad437c184bd7

    Introduction to SQL course

  • 5 Steps of Data Analysis By Using Microsoft Excel

    5 Steps of Data Analysis By Using Microsoft Excel

    Data analyst is an essential tool that enables organizations to gain deeper insights into their data. Utilizing Microsoft Excel for efficient data processing facilitates accurate and prompt decision-making while identifying trends and uncovering new business opportunities.

    Table of content

    1. The 5 steps for analyzing the sales_data_analysis.xlsx file in Microsoft Excel 365 are as follows
      1. Upload a document using the free online version of Microsoft Office 365
      2. Perform data analysis using sorting and filter tools.
      3. Perform data mining using the IF Function
      4. Create references between tables and search for information with VLOOKUP
      5. Perform data analysis using Pivot Table
        1. Pivot table
    • Excel can help data analysis
    • Excel work with a prepared spreadsheet that contains sale

    The 5 steps for analyzing the sales_data_analysis.xlsx file in Microsoft Excel 365 are as follows

    Upload a document using the free online version of Microsoft Office 365

    One drive to upload excel file

    Click add new → File upload → then upload → sales_data_analysis_23.10.2024

    • Go to Insert → Table to create a table that uses the header in the first column to filter data. → Click OK.
    • To Create a table to filter data, see the picture below.
    Create Table in microsoft excel 365
    • Then filter the data shown in the picture below.
    can filter data by columns such as Num, Date, Month, Sales Rep, Region, Customer ID, etc.

    Set it up so that when you scroll down to view data in the rows below, the first column remains visible. This makes it much easier to reference the headings.

    Perform data analysis using sorting and filter tools.

    Which column should be prioritized for sorting data to make it more effective?

    representative header and then select sort A to Z to sort it in alphabetical order

    Sort the Sales Rep column from A to Z.
    • after click it has been rearrange by alphabetical Sales Rep
    • then to make it back to select sort in column date again
    • can sort the Region by North.
    Filter by North Region.
    • To remove the filter, click the ‘Select All‘ checkbox to display sales from the North, South, and West regions, and then click.
    • Then filter the Sales Rep column by the name David Garcia.
    Calculate in the bottom right corner.
    What you can see from data?
    Average of $7,893
    Count of 9
    Sum of $71,040
    • you can see aggregate value in the bottom right corner.

    This is how to use the sorting and filtering tools to rearrange your data.

    Perform data mining using the IF Function

    • The idea behind data mining is to take the data you already have and create new or additional data from it.
    • The IF function is frequently used.

    Samples show that when an order includes 20 chairs or more, the client receives a 5% wholesale discount.

    Discount Column 3 Method
    1. Create a discount column to the right to reflect this.
    2. In the column, use ‘Y‘ for orders with a quantity ≥ 20.
    3. In the column, use ‘N‘ for orders with a quantity ≤ 20.

    Code for column Discount

    =IF(J5>=20,"Y","N")
    Create a discount column based on the quantity in the number column.
    • Final Price column

    Code for column Final Price

    =IF(J5>20,0.95*L5,L5)
    Create a Final Price column based on discount and number column.
    • column of Discount with Y is number ≥ 20 price is discount 5% final is 95% from total column
    • column of Discount with N is number ≤ 20 price is same as the total

    Create references between tables and search for information with VLOOKUP

    Goal is to insert the company name using the client ID.

    1. Create column Company Name between Customer ID and Color
    2. Create column Company Name Representative between Company name and model

    The explanation of the variables used in VLOOKUP.

    VLOOKUP(lookup_value, table_aray, col_index_num, [range_lookup])

    Create Company name column

    =VLOOKUP(F5,'Customer Info'!$A$4:$C$12,2,FALSE)

    Create Company name representative

    =VLOOKUP(F5,'Customer Info'!$A$4:$C$12,3,FALSE)
    Customer Info sheet data

    to connect data between sales data and customer info sheet

    Type of VLOOKUP
    Create column Company name and Company Name Representative column in sale data sheet

    can adding a dollar sign in front A4 and C12 to make data not change

    Perform data analysis using Pivot Table

    Method to start

    • Typing control A and it will automatically highlight all the data in the table
    • Then insert ———> Pivot table ———> click okay
    Create Pivot table in new worksheet
    • Recommend selecting new worksheet so it placement does not affect the other data that already exists

    Pivot table

    • define categories as either filters, columns, rows, value
    TypeDefinition
    Filter2 Category
    ColumnCategory can specified as a column to fit with data
    RowCategory can specified as a row to fit with data
    ValuesMust use with number
    • 1. drag Final Price to Value of pivot table
    • 2. drag Sales Rep to columns of pivot table
    • 3. drag Month to row of pivot table
    Table prepare month with Sale Rep

    how many chairs of each model were sold in each month?

    Pivot table many chairs of each model per month.
    • Finally you have learned how to create pivot tables to summarize and look at comparisons within your data.

    Access full Microsoft excel through below link:

    sales_data_analysis_23.10.2024_chayanon_boonthanomDownload

    Coursera Project Network Certificate

    https://www.coursera.org/account/accomplishments/verify/2F13S7V2LEZF

    Summary

    Data analysts help organizations gain valuable insights from data. Using Microsoft Excel enhances decision-making by processing data efficiently and identifying trends and opportunities.