Power BI

INTRODUCTION TO POWER BI:

• Why Use Power BI?
• Power BI Desktop
• Explanation of ETL process (Extract, Transform, Load)
• Data Types

EXTRACTING & TRANSFORMING DATA:

• Extracting data from various sources:Text/CSV files, Excel worksheets, Multiple Files in a Folder, and more Transforming data using the Power Query Editor:
• ETL = Extract, Transform, Load
• ELT = Extract, Load, Transform

DATA TRANSFORMATION:

• Use First Row as Headers
• Remove Empty rows (null data)
• Split Column By Delimiter
• Applied Steps
• Merge Queries
• Append Queries
• Fill Down
• Transpose
• Pivot
• Column profiling for data quality check

CREATING POWER BI REPORTS, AUTO FILTERS:

• Understanding Power BI Report Designer
• Report Canvas, Report Pages: Creation, Renames
• Report and Dashboard Difference
• Report Visuals, Fields
• Experimenting Visual Interactions, Advantages
• Reports with Multiple Pages and Advantages
• Pages with Multiple Visualizations.
• Data Access PUBLISH Options and Report Verification in Cloud
• “GET DATA” Options and Report Fields, Filters
• Data Types - Numbers, String, Date and Time
• Report View Options: Full, Fit Page, Width Scale
• Stacked bar chart, Stacked column chart Clustered bar chart, Clustered column chart
• Adding Report Titles. Report Format Options
• Focus Mode, Explore and Export Settings

CHART AND MAP REPORT PROPERTIES:

• Chart report types and properties
• Stacked bar chart, stacked column chart
• Clustered bar chart, clustered column chart
• 100% stacked bar chart, 100% stacked column
• chart Line charts, area charts, stacked area charts
• Line and stacked row charts line and stacked column charts
• waterfall chart, scatter chart, pie chart Field Properties: Axis, Legend, Value, Tooltip
• Field Properties: Color Saturation, Filters Types
• Formats: Legend, Axis, Data Labels, Plot Area
• Data Labels: Visibility, Color and Display Units
• Data Labels: Precision, Position, Text Options
• Analytics: Constant Line, Position, Labels
• Working with Waterfall Charts and Default Values
• Modifying Legends and Visual Filters - Options
• Map Reports: Working with Map Reports
• Hierarchies: Grouping Multiple Report Fields
• Hierarchy Levels and Usages in Visualizations
• Using Field Hierarchies with Chart Report

REPORT VISUALIZATIONS and PROPERTIE:

• Power BI Design: Canvas, Visualizations and Fields
• Import Data Options with Power BI Model, Advantages
• Data Fields and Filters with Visualizations
• Visualization Filters, Page Filters, Report Filters
• Conditional Filters and Clearing. Testing Sets
• Creating Customized Tables with Power BI Editor
• General Properties, Sizing, Dimensions, and Positions
• Alternate Text and Tiles. Header (Column, Row) Properties
• Grid Properties (Vertical, Horizontal) and Styles
• Table Styles & Alternate Row Colors - Static
• Dynamic Sparse, Flashy Rows, Condensed Table Reports. Focus Mode
• Total Computations, Background. Borders Properties
• Column Headers, Column Formatting, Value Properties
• Conditional Formatting Options - Color Scale
• Page Level Filters and Report Level Filters
• Visual-Level Filters and Format Options
• Report Fields, Formats and Analytics
• Page-Level Filters and Column Formatting, Filters
• Background Properties, Borders and Lock Aspect

HIERARCHIES and DRILLDOWN REPORTS:

• Hierarchies and Drill Down Options
• Hierarchy Levels and Drill Modes - Usage
• Drill-thru Options with Tree Map and Pie Chart
• Higher Levels and Next Level Navigation Options
• Aggregates with Bottom/Up Navigations. Rules
• Multi Field Aggregations and Hierarchies in Power BI

BUILDING/DESIGN A REPORT:

• Adding a Title
• Adding a Logo
• Adding a Background (Solid Color or Photo)

SLICERS:

• What Slicers Are
• Adding & Using a Slicer

RELATIONSHIPS:

• Relationship importance
• Understanding types of relationship
• Creating relationships
• Cross filter relation: Single or Both

DAX & MEASURES:

• Create Measures
• Using Measures in a Data Visualization
• Calculated Columns
• Using DAX to create a Calculated Column
• Navigating Related Tables using DAX

SQL(Structured Query Language)

SQL

• SQL Introduction:
• SQL Database:
• SQL Table:
• SQL Constraints:
• SQL Data Types:
• SQL Operators:
• SQL Wildcard Characters:
• SQL Joins:
• SQL Aggregate Function:
• SQL Views:
• SQL Dates:
• String Functions in SQL Server:
• SQL Server Date Functions:
• SQL query:

Advanced Excel

Working with Advanced Formulas:

• Using Named Ranges in Formulas
• Naming a Single Cell and Range of Cells
• Naming Multiple Single Cells Quickly
• Using Formulas Spanning Multiple Worksheets
• Using the IF Function
• Using AND/OR Functions
• Using the SUMIF, AVERAGEIF, and COUNTIF Functions
• Working with the LOOKUP, VLOOKUP and HLOOKUP Functions
• Understand various Financial Functions

Working with Lists:

• Converting a List to Table
• Removing Duplicates from a List
• Sorting Data in a List
• Filtering Data in a List

Working with Illustrations:

• Advantages of using Illustrations
• Working with Clip Art
• Using Shapes
• Working with SmartArt

Data Visualization – Advanced:

• Overview of Data Visualization
• Advantages of Data Visualization
• Understanding and working with Charts
• Creating a Custom Chart Template
• Inserting Charts
• Adding and Formatting Objects
• Inserting Text in Charts
• Working with Advanced Features
• New Chart Formats -Tree maps, Sunburst, Histogram, Waterfall, Box and Whisker, Pareto

Advanced Formatting in Excel:

• Understanding and Applying Conditional Formatting
• Using and Working with Styles

Working with Pivot Tables:

• Understanding Pivot Tables
• Creating Pivot Tables
• Working with Column Labels
• Working with Row Labels
• Formatting a Pivot Table
• Inserting Slicers
• Working with Pivot Tables
• Inserting Pivot Charts

Introduction to Power Query:

• What is Power Query
• Enabling Power Query Add-on
• Working with Power Query to Import Data from Various Sources
• Managing Data using Power Query

Macros:

• What is a Macros
• How to use and apply Macros