The Data Skills Survival Kit

New This Week

• Turn spreadsheets into future dashboard designs

Introduction

• Series intro

1. Joining Data

• Creating unique identifiers between two data sets
• Joining with common data between two unrelated data sets
• Using anti-joins to search for data
• Working with full outer joins
• Using dates for joins

2. Data Designs

• Normalizing data to achieve a data design
• Model or Merge—and what's the difference?
• Scripting views for SQL databases
• Designing relationships for databases
• Creating a SharePoint back end from a database
• Designing a back end for your reporting solution using Microsoft 365 Copilot and Excel

3. Privacy and Protection Techniques for Data

• Using functions to mask data
• Creating aggregated datasets
• Auditing data and spreadsheets

4. Transforming Data and Cleaning Data

• Using Unpivot to build data sets from survey results
• Cleaning data from a PDF in Power BI
• Duplicate vs. reference in Power Query
• Cleaning data from a folder of files in Power BI
• Build conditional functions to make data
• Creating tables and joins to recode data
• Merging data and building basic calculations into a data set
• Mapping data into other software tools
• Creating subgroups for use in visuals and basic analysis
• Cleaning survey data for the purposes of analysis

5. Making Data Sets

• Using Remove Duplicates to create data sets
• Using union queries to bring together data sets in SQL
• Using append queries to combine spreadsheets
• Using make tables to normalize a denormalized data set
• Building date tables in Power BI/Power Query
• Using joins and update queries to create markers in the data
• Adding data leveraging cross joins
• Using date functions to create follow up data
• Using Remove Duplicates to find the latest or earliest record in Excel
• Leveraging the Advanced Editor in Power Query to share cleaning routines
• Building data sets inside of Outlook
• Defining a nomenclature for fields to reduce data issues

6. Visualize Data

• Edit visual interactions
• Build letter size for reports using Power BI Desktop
• Build paginated reports
• Use different pivot formats to share stories
• Building a brief for a data project
• Creating simple data sets with basic visuals for presentations
• Basic visualization of cohort data
• Visualizing data sets in Outlook
• Using Pivot Column to look at Text data in a pivot

7. Basic Analysis

• Perform basic analysis on datasets
• Using analysis commands built into popular tools
• Basic dates analysis
• Analyzing data using a matrix and conditional formats
• Using Case statements for conditional logic in SQL queries
• Establishing a cohort data set
• Adding additional data to your cohorts
• Analyzing top and bottom scenarios
• Understanding and communicating impacts
• Working with different group sizes
• Visualizing Subgroups when searching for insights

8. Leveraging Copilot for Data in Microsoft Excel

• Use Copilot in Excel to teach you about the data
• Use Copilot to build basic functions in Excel
• Use Copilot to do basic cleanup commands
• Use Copilot to see insights in your data
• Use Copilot in Word to build documentation about your data project
• Use Copilot to create a draft presentation about your data project

9. Designing Relational Databases

• Designing a basic system for data capture
• Diagramming data and processes
• Planning a basic system for data collection
• Testing data in your new database design
• Designing basic forms for data entry into your solution
• Testing data entry in data designs
• Designing user interfaces for data entry into your solution

10. Data Capabilities in SharePoint

• How to create a dataset in a SharePoint list
• Designing normalized data structures in SharePoint lists
• Visualizing SharePoint list data in Excel for dynamic reporting
• Automating workflows from a SharePoint list with Power Automate

11. Leveraging Copilot in PowerBI for Data Projects

• Conversational data exploration in Power BI with Fabric and Copilot