Excel & Power BI Data Analysis Complete Class in One Video - 365 MECS 04

ExcelIsFun・2 minutes read

Video four in Microsoft 365 Excel covers tools like sort, filter, and power query across a three-hour, 43-minute long video with PDF notes, emphasizing the importance of proper data sets for effective data analysis and the use of various Excel tools. Examples include learning tricks for sort, filter, flash fill, using pivot tables for survey results, and utilizing power query for data transformation, with a focus on creating interactive dashboards for reports.

Insights

  • The video in Microsoft 365 Excel introduces tools like sort, filter, flash fill, power query, pivot table, and more, emphasizing their importance in data analysis and visualization.
  • Power Query in Excel allows for importing, cleaning, and transforming data from various sources, facilitating data analysis and loading into Excel or Power BI Desktop for further insights.
  • Data models created in PowerPivot can be transferred to Power BI Desktop for interactive dashboards, enabling efficient data visualization and reporting.
  • Proper data sets are crucial for effective data analysis, highlighting the significance of sorting, filtering, and flash fill tools in Excel for accurate and insightful results.
  • Power BI Online and Power BI Desktop offer powerful features for creating interactive reports, visualizations, and dashboards, allowing for easy sharing and access to data models for enhanced collaboration and insights.

Get key ideas from YouTube videos. It’s free

Recent questions

  • What tools are covered in Microsoft 365 Excel video four?

    The video covers tools like sort, filter, flash fill, power query, pivot table, charts, power pivot, and power bi. It offers an introduction to all these tools, emphasizing their importance in effective data analysis and reporting.

  • How can Power Query be used in Excel?

    Power Query in Excel is a tool to import and clean data from various sources like text files, databases, and more. It allows for data transformation and loading into Excel or Power BI Desktop, making data analysis more efficient and accurate.

  • What are some key features of Power BI Desktop?

    Power BI Desktop offers interactive and shareable visuals for reports, including line charts, matrices, and clustered columns. It enables interactive filtering, highlighting of data, and additional features like tool tips, filters, slicers, and cards for total sales display.

  • How can relationships be established in Power Pivot for Excel?

    In Power Pivot for Excel, relationships between tables in the data model can be created to ensure accurate data modeling. By establishing these relationships, users can effectively analyze and report on data across different tables for comprehensive insights.

  • How can gross profit be calculated in Power BI Desktop?

    Gross profit can be calculated in Power BI Desktop by creating DAX measures for revenue, cost of goods sold, and gross profit. These measures help in determining profit margins and analyzing trends in profitability over time, providing valuable insights for decision-making.

Related videos

Summary

00:00

"Master Excel Tools for Data Analysis"

  • Video number four in Microsoft 365 Excel covers various tools like sort, filter, flash fill, power query, pivot table, charts, power pivot, and power bi.
  • The video is three hours and 43 minutes long, offering an introduction to all tools.
  • A time hyperlink table of contents is available below the video for easier navigation.
  • PDF notes with 32 pages are provided for each video in the class.
  • Examples in the video include learning tricks about sort, filter, and flash fill, using pivot tables for survey results, and utilizing power query for data transformation.
  • Power query can convert multiple tables into a single table for easier analysis.
  • The XLOOKUP function and DAX formulas are introduced for more advanced data analysis.
  • Data models created in PowerPivot can be transferred to Power BI Desktop for interactive dashboards.
  • The video demonstrates importing seven million rows of data from an SQL database for dashboard reports.
  • The video emphasizes the importance of proper data sets for effective data analysis and the use of sorting, filtering, and flash fill tools in Excel.

18:57

Excel: Transform Data with Power Query

  • To change the aggregate calculation in Excel, right-click in the values area and select "Summarize Values By" to choose different calculations like count.
  • Use the "Show Values As" feature to rename calculations, such as changing "Count" to a more specific name.
  • For a second calculation, select "Summarize Values By" as count, then choose "Percent of Column Total" to calculate the percentage of responses.
  • Power Query in Excel is a tool to import and clean data from various sources like text files, databases, and more, allowing data transformation and loading into Excel or Power BI Desktop.
  • In Power Query, access data sources through the Data ribbon tab, then navigate to "Get and Transform Data" and "Queries and Connections" to connect to different data sources.
  • Power Query can import, clean, and transform data from websites, like student assignment scores, by downloading CSV files, importing, transforming, and loading data into Excel for further analysis.
  • CSV files store data as comma-separated values, facilitating data transfer between systems, and can be opened in Excel but are not Excel files.
  • Before importing CSV data into Excel using Power Query, ensure all files are closed, manage folder paths for easy access, and use the Power Query Editor to clean and transform data before loading.
  • Power Query's applied steps automatically record data transformation actions, allowing users to view and edit these steps, and the M code language underpinning Power Query simplifies data manipulation.
  • Explore applied steps, M code functions, and advanced editor in Power Query to understand and modify data transformation processes for efficient data analysis and reporting.

37:46

Efficient Power Query Data Transformation Techniques

  • In Power Query, the first argument of the next function is always the name of the previous step, with a comma at the end.
  • The absence of a comma in the last step indicates to Power Query that it's the final value, such as a table with promoted headers and changed data types.
  • To efficiently work on the last step, select "Change Type" to remove unnecessary columns like "Quarter" and "Course Number."
  • Use the "Choose Columns" option in the drop-down menu to easily remove unwanted fields, especially in tables with numerous columns.
  • Renaming fields in Power Query involves double-clicking on the field name and entering the new name, with the function acting on the previous step.
  • Changing data types efficiently involves selecting multiple columns, right-clicking, and choosing the desired data type, ensuring fewer steps for better efficiency.
  • Loading the transformed table to Excel involves selecting the desired loading option from the "Close and Load" drop-down menu, with options like loading as a table or to the data model.
  • In Power Query, the Queries and Connections pane displays the loaded query, providing information on load status and file path.
  • Editing a query involves opening the Power Query Editor, making changes like splitting columns, renaming fields, and sorting data before reloading the query.
  • Calculations like total score and percentage grade can be done in Power Query or using Excel formulas, with the option to add new calculated fields before loading to the worksheet.

56:14

"Combine and Analyze Monthly Sales Data"

  • To update data, drop new monthly files into a folder and click refresh.
  • In the example folder, there are nine text files with three fields: date, units, and price.
  • Use Power Query to combine these files and create a sales column.
  • Access data by going to Data > Get Transform > Get Data > From File.
  • Power Query can retrieve all files from a folder, ensuring they have the same structure.
  • Use M code to transform file extensions to lowercase for consistency.
  • Combine files by selecting the double downward pointing arrow button.
  • Remove attribute columns before combining files.
  • Create a new column by multiplying units and price.
  • Load the data to a pivot table cache, build a pivot table, and create a line chart for visualization.

01:14:14

Enhancing Reports with Power BI Desktop

  • Three tables needed for creating reports and visualizations.
  • Data modeling issue due to missing columns in tables.
  • Need to look up sales rep ID and product ID to bring over names.
  • Reports are more user-friendly with names rather than IDs.
  • Ways to solve lookup problems include using worksheet formulas.
  • Worksheet formulas suitable for smaller data sets in Excel.
  • Example 8 and 9 involve external tables imported using Power Query.
  • Power Pivot used to build relationships between tables for data modeling.
  • Power BI Desktop better for handling large data sets than Excel.
  • Power BI Desktop offers interactive and shareable visuals for reports.

01:32:25

"Building Data Model in Excel"

  • Load tables to data model, not pivot table cache
  • Create date dimension table for data model
  • Establish relationships between dimension tables and fact table
  • Import tables from Excel file using Power Query
  • Select and import specific tables from Excel file
  • Verify data types and names in Power Query Editor
  • Load tables to data model, selecting "only create a connection"
  • Access data model through Power Pivot for Excel window
  • Create relationships between tables in data model
  • Hide unnecessary fields in data model for reporting purposes

01:50:30

Creating Columnar Database for Efficient Data Analysis

  • The first step involves creating a columnar database to store compressed data behind the scenes.
  • A preview of tables can be viewed in either diagram or data view.
  • Relationships are established within the data model.
  • DAX formulas are created, including three DAX measures.
  • DAX measures are identifiable by an "f of x" icon in PowerPivot and show up in the pivot table field list.
  • Certain fields are hidden to prevent them from appearing in the pivot table field list.
  • Moving to an Excel worksheet, the first data model pivot table is created by selecting a cell, going to the insert tables group, and choosing pivot table from the data model.
  • The pivot table fields task pane is adjusted to display all tables in the data model and Excel tables in the workbook.
  • Reports are created for regions, sales reps, and products using DAX measures and appropriate field selections.
  • Filter context in DAX measures efficiently calculates values based on specific conditions, ensuring accurate results in pivot tables.

02:08:43

"Power BI Online: Sharing Reports and Data"

  • Power BI Online is a tool that requires a license and allows for easy sharing of reports, visuals, dashboards, and data models.
  • Entities often purchase Power BI Online for employees along with Microsoft 365 Office.
  • Power BI Online enables uploading Power BI Desktop files and Excel files with PowerPivot data models for sharing.
  • Data models uploaded to Power BI Online become universally accessible to assigned groups of workers or students.
  • Sharing in Power BI Online involves creating online workspaces for groups and adding emails of fellow workers or classmates.
  • Power BI Desktop allows importing PowerPivot data models from Excel files and offers a tour of its features.
  • Power Query in Power BI Desktop is similar to Excel's Power Query, allowing for data transformation.
  • Visualizations and reports can be built in Power BI Desktop, including line charts, matrices, and clustered columns.
  • Power BI Desktop enables interactive filtering and highlighting of data in visualizations.
  • Additional features in Power BI Desktop include tool tips, filters, slicers, and cards for total sales display.

02:26:34

"Creating Sales Reports in Power BI"

  • Start by clicking in the white area and use a slicer to select the year from a drop-down list.
  • Add a card to display the total sales measure, format it as a decimal value, and label it as "Colorado Boomerangs Sales Report Control A."
  • Filter the report by selecting 2020, then 2021, and erase to view the sales report.
  • Create a new page named "Region Report" and add a matrix with region, year, and month fields, total sales, and number of transactions.
  • Adjust the font size of the matrix to 12 and add a title at the top.
  • Insert two regional bar charts for products and sales reps, explaining the differences between stacked and clustered bar charts.
  • Customize the colors of the columns in the bar charts under the format option.
  • Enhance the charts by adding data labels and duplicating them to compare different variables.
  • Add a text box with the company logo and format the colors of the bars manually or using predefined themes.
  • Publish the Power BI desktop file and Excel workbook file to Power BI online, ensuring access with a Power BI Pro license or organizational email.

02:44:38

Dashboard creation and report pinning instructions.

  • To pin a report to a dashboard, create a new dashboard named "Colorado Reports" and pin the report to it.
  • Mobile layouts can be created by clicking on the mobile layout option and dragging to determine the appearance on mobile devices.
  • A web view is available along with the dashboard for viewing reports.
  • Reports pinned to the dashboard function like actual reports, accessible from the workspace.
  • Excel workbooks can be pinned to the dashboard, although the process may seem clunky.
  • The dashboard allows for combining various reports and workbooks, with options to save, print, and share.
  • Sharing the dashboard involves sending an email notification and granting access.
  • Accessing the dashboard on a cell phone requires downloading the app and logging in with an organizational email.
  • Power BI Online enables interactive viewing and filtering of reports on mobile devices.
  • Connecting to online data models in Excel and Power BI Desktop involves inserting pivot tables and selecting the desired data sets.

03:02:56

"SQL Database Access and Power BI Analysis"

  • To access an SQL database online, credentials for server, database, username, and password are required.
  • Open a new Power BI Desktop file to create seven measures for a gross profit page and plot gross profit globally using a map visual.
  • Start the SQL Power BI Desktop adventure by getting data from a SQL server database.
  • Enter credentials for the server (pawn.highline.edu) and the database (boomdata) for a direct import.
  • Transform data by selecting specific queries (d country, d product, f transactions) and ensuring correct data types.
  • Remove unnecessary columns and ensure correct data types for revenue and discount.
  • Create a date table using DAX formulas to contain a unique list of dates from the fact table.
  • Mark the date table as a date table to prevent automatic date table creation.
  • Add extra fields like month and year to the date table for reporting purposes.
  • Create DAX calculated columns for revenue and cost of goods sold in the fact table, utilizing related functions to access data from other tables.

03:21:15

"Calculating Revenue and Profit in Excel"

  • To calculate line or row revenue, use the dollar sign and equal sign to build a formula step by step.
  • Utilize the LOOKUP function to find the price for each row in a table by referencing the column name.
  • Apply filters to view a unique list of prices for different products in a large table.
  • Include the quantity or number of units purchased in the formula by multiplying the full column reference.
  • Subtract the discount, given in pennies per dollar, from the revenue amount to determine the final amount paid.
  • Round off any decimal values in the calculated revenue.
  • Create a measure named "Total Revenue" using the SUM function to aggregate the calculated column values.
  • Implement the one-step method using the SUMX iterator function to calculate total revenue without creating a calculated column.
  • Develop a measure for "Total Cost of Goods Sold" using the SUMX function to calculate the cost of goods sold for each line item.
  • Calculate "Gross Profit" by subtracting the total cost of goods sold from total revenue, providing a metric for profit margin.

03:40:29

Declining Christmas profits, global sales comparison in Power BI.

  • The gross profit percentage for all products during Christmas time has decreased over four years, dropping from about 44% to 40%, indicating a trend of diminishing profits which could impact covering expenses and product costs.
  • By creating a new page named "gpmap" in Power BI Desktop, a world map visualization can be generated using sales data with country information, allowing for a comparison of gross profit sizes across different countries, revealing variations such as the United States having a significant bubble compared to Australia, Germany, Japan, and France, with specific events like a world championship of boomerang throwing in France on August 20th highlighted.
Channel avatarChannel avatarChannel avatarChannel avatarChannel avatar

Try it yourself — It’s free.