Overview

Save Big on Coursera Plus. 7,000+ courses at $160 off. Limited Time Only!

Grab it

Learn advanced Excel data analysis techniques using Power Pivot and Power BI in this comprehensive 58-minute video tutorial. Master big data analysis by working with real-world datasets, including Seattle's 911 calls and SQL databases. Explore Power Query for data import and transformation, create dimension tables, and develop DAX formulas for complex calculations. Dive into data modeling, relationship creation, and visualization techniques using various chart types, maps, and word clouds. Gain hands-on experience with filter contexts, calculated measures, and custom number formatting. By the end of this tutorial, acquire the skills to build powerful reports, understand the differences between ALL and ALLSELECTED DAX functions, and efficiently handle large datasets in both Power BI and Power Pivot environments.

Syllabus

) Intro.
) Overview of data and visuals we will do in Power BI.
) Overview of SQL data and reports we will build in Power Pivot.
) Start of Power BI Big Data CSV file Example.
) Download 911 Seattle data from web site: https://data.seattle.gov/Public-Safety/Seattle-Real-Time-Fire-911-Calls/kzjm-xkqj.
) Power Query to Import and transform csv file.
) Data Source Settings .
) Data Modeling in Power Query: create foreign key columns in Fact Table.
) Remove fields not needed to make Columnar Database smaller..
) Close and Apply data to Data Model.
) Create Date and Time Dimension Tables.
) copy GENERATE and ROW DAX Formula from last video to create the Data Table..
) GENERATESERIES DAX Function to create Hour Dimension Table.
) Create Serial Number Time DAX Calculated Field.
) Create Hour DAX Calculated Field.
) Sort Hour Field by Hour ID field.
) Sort Month Name in Data Table by Date field.
) Set Hour field to NOT sum.
) Create Relationships.
) Hide fields.
) Mark data table as Date Table.
) Create DAX Measure for counting using COUNTROWS DAX Function..
) Hide Foreign keys in Fact Table.
) Create Table Visuals to show counts.
) Sort fields in tables.
) Add Slicers.
) Create a Card Visual to show Measure result.
) Understanding Filter Context.
) Create DAX Measure for % of Grand Total using CALCULATE, ALL & DIVIDE functions.
) () Create DAX Measure for % of Filtered Grand Total using CALCULATE, ALLSELECTED and DIVIDE functions.
) Difference between ALL and ALLSELECTED DAX functions.
) “See” Filtered Total created by ALLSELECTED function.
) Create MAP Visual to show location of 911 calls.
) Create Word Cloud.
) ArcGIS Time Movie Map Visual.
) Update when new data arrives.
) Power BI Project conclusion.
) Start of Power Pivot Big Data SQL Database Example.
) Reporting Goal explained.
) SQL Database explained.
) Look at tables and data for project.
) Credentials to connect to the SQL Database.
) Navigator dialog box.
) Power Query Editor for the three SQL tables.
) Extra Columns from related tables.
) Look at Fact Table and adjust Data Types, including “Using Locale” option for dates with different settings.
) View Native Query inside Power Query Applied Steps to see which steps are sent back to SQL database (folding).
) Load tables to Power Pivot Data Model’s Columnar Database.
) Create Power Pivot Date Table.
) Create Relationships.
) Create DAX Measures.
) Compare DAX Calculated Columns to DAX Measures..
) Total Revenue Measure using SUMX DAX function.
) What is Revenue Discount? How to use in formula?.
) Use Filter Dropdown to see a unique list.
) What is Net Standard Cost, or Net Cost Equivalent? How to use in formula?.
) Total COGS Measure using SUMX DAX function.
) Gross Profit ($) DAX Measure.
) Gross Profit % DAX Measure.
) Average Daily Gross Profit DAX Measure. Discuss Pre-aggregating at the day grain before averaging the daily totals.
) Why are $ signs in Measure name, rather than using Number Formatting with dollar signs?.
) Hide Fields from Client Tool.
) Build Data Model PivotTable Gross Profit Report.
) Add Slicer to Data Model PivotTable.
) Create Line Chart to show Gross Profit over Year and Month.
) Create Custom Number Format in Chart.
) Change Chart type from Column to Line.
) Add series of numbers to Secondary Axis.
) Connect Chart to Slicer with Report Connections.
) Power Pivot Project Conclusion.
) Conclusion for whole video (both movies).