PIVOT TABLES: TOOLS & TECHNIQUES
- The 19 Rules
- Design Rules
- Designing Pivot Tables
- Number formatting techniques
- Designing report layout
- Sorting in ascending, descending and more sort options
- Filtering labels and values
- Expanding and collapsing reports
- Analytics Rules
- Summarize values by sum, average, minimum, maximum, count
- Show values as % of total and % of
- Pivot table options
- Inserting formulas
- Date analysis
- Copying pivot tables
- Visualization Rules
- Creating pivot charts
- Dynamic chart labeling
- Mastering the slicer
- Showing report filter pages
- Linking pivot tables with PowerPoint
- Conditional formatting with pivot tables
- Extracting data using the ‘GetPivotData’
POWER PIVOT
Consume and Transform Data by Using Microsoft Excel
- Import from Data Sources
- Connect to and import from:
- Databases
- Files
- Folders
- Connect to Microsoft SQL
- Access databases
- Excel files
- Html
- Power Query
- Create and Optimize Data Models
- Manage data relationships
- Optimize models for reporting
- Manually enter data
- Perform Get & Transform
- Create automatic relationships
- Create manual relationships
POWER PIVOT (Continued)
- Create Calculated Columns, Measures, and Tables
- Create DAX queries
- Create DAX formulas
- Create Excel formulas
- Create Performance KPIsCalculate the actual value
- Calculate the target value
- Calculate actual to target values
- Create and Manage Pivot Tables
- Format PivotTables and Pivot Charts
- Format calculated measures
- Filter data
- Group and summarize data
POWER QUERY
- Get Data from Different Data Sources
- Identify and connect to a data source
- Change data source settings
- Select a shared dataset or create a local dataset
- Select a storage mode
- Choose an appropriate query type
- Identify query performance issues
- Use the common data service (CDS)
- Use parameters
- Clean, Transform, and Load the Data
- Resolve inconsistencies, unexpected or null values, and data quality issues
- Apply user-friendly value replacements
- Identify and create appropriate keys for joins
- Evaluate and transform column data types
- Apply data shape transformations to table structures
- Combine queries
- Apply user-friendly naming conventions to columns and queries
- Leverage Advanced Editor to modify Power Query M code
- Configure data loading
- Resolve data import errors
POWER BI
- Create Reports
- Add visualization items to reports
- Choose an appropriate visualization type
- Format and configure visualizations
- Import a custom visual
- Configure conditional formatting
- Apply slicing and filtering
- Add an R or Python Visual
- Configure the report page
- Design and configure for accessibility
- Configure automatic page refresh
- Create Dashboards
- Set mobile view
- Manage tiles on a dashboard
- Configure data alerts
- Use the Q&A feature
- Add a dashboard theme
- Pin a live report page to a dashboard
- Configure data classification
POWER BI (Continued)
- Enrich Reports for Usability
- Configure bookmarks
- Create custom tooltips
- Edit and configure interactions between visuals
- Configure navigation for a report
- Apply sorting
- Configure Sync Slicers
- Use the selection pane
- Use drill through and cross filter
- Drilldown into data using interactive visuals
- Export report data
- Design reports for mobile devices
- Enhance Reports to Expose Insight
- Apply conditional formatting
- Apply slicers and filters
- Perform top N analysis
- Explore statistical summary
- Use the Q&A visual
- Add a Quick Insights result to a report
- Create reference lines by using Analytics pane
- Use the Play Axis feature of a visualization
