If you’re not familiar, Power Query is an extract-transform-load (ETL) tool directly available inside Excel (and Power BI). If you’re not sure what ETL is — you’re in luck! If you want to learn how to actually find, let alone use Power Query — this is for you too.

This 30-day checklist provides a curated learning path through the best blog posts, videos and more across the web.

Sign up below for the checklist and access to my resource library: 

If you’re already subscribed, you’ll find this resource in the learning-guides-and-checklists folder of the library.

This 30-day learning checklist is your curated playlist to gain an understanding of Power Query from the ground up. Consider it a “best of” free web content on this majestic tool. You’ll learn, among other things:

• How Power Query serves as an extract-transform-load tool
• How to connect your first data to Power Query and navigate the interface
• How to sort, filter, aggregate and merge data, along with other common data manipulation tasks
• How to work with folders of files, websites and other data sources

From here, you’ll have a beat on the most reputable names in Power Query for Excel and know where to go for more help.

To get the checklist, subscribe below. This will give you access to my entire resource library, with other checklists, demos and more.

Get the checklist here

I look forward to seeing what you do in Power Query with Excel. Tell me about it in the comments, or share your favorite Power Query for Excel resources. Have ideas for other learning guides? I can take those too .

The post Free checklist: 30 days to Excel Power Query first appeared on Stringfest Analytics.

Using SQLAlchemy inside Python is such a case. This open-source tool allows you to interact with relational databases from inside Python. As such, SQLAlchemy crosses two slices of the analytics stack: databases and programming languages.

As the name implies, SQLAlchemy is based on the tenets of SQL, the traditional language for interacting with relational databases. The “Alchemy” is that by adding Python to the mix, magic happens.

Not only is it incredibly useful to pull information from databases into Python objects, the Python language adds versatility to traditional SQL querying.

Take a look at the below half-day workshop as a way to get started with this popular Python package. By the end of the workshop, learners will be able to build an end-to-end data pipeline, from a relational database for data storage and integrity to a pandas DataFrame for data analysis and visualization.

Lesson 1: Combining the powers of SQL & Python

Objective: Student can compare and contrast the uses of Python and SQL for data analysis

Description:

• Databases versus scripting languages
• Declarative versus procedural languages
• Object-oriented programming

Time: 20 minutes

Assets needed: none

Lesson 2: Connecting to a database and returning results

Objective: Student can connect to, retrieve from, and close a database

Description:

• Installing and loading the packages
• Connecting to a database
• Retrieving keys and items
• Retrieving records
• Closing the connection          

Time: 45 minutes

Assets needed: Baseball database

Lesson 3: Sorting & filtering results

Objective: Student can sort and filter query results

Description:

• Conditional logic
• Filtering results
• Ordering results

Time: 35 minutes

Assets needed: Baseball database

Lesson 4: Student can group by and create calculations from query results

Objective: Student can create a data manipulation pipeline

• Grouping results
• Creating aggregated calculations
• Creating calculated fields

Time: 35 minutes

Assets needed: Baseball database

Lesson 5: Joining tables

Objective: Student can join two or more tables

• Inner joins
• Left outer joins

Time: 35 minutes

Assets needed: Baseball database

Lesson 6: SQLAlchemy & pandas

Objective: Student can load query results into pandas DataFrames and analyze

• Loading results to a DataFrame
• Summarizing and analyzing in pandas

Time: 30 minutes

Assets needed: Baseball database

Lesson 7: Capstone: creating a data pipeline

Objective: Student can create an end-to-end data pipeline using Python, SQLAlchemy and pandas

• Connect to, retrieve and close a database
• Load results to DataFrame
• Analyze, inspect and interpret results

Time: 35 minutes

Assets needed: Flights database

The post Learning guide: Python + SQLAlchemy, half-day workshop first appeared on Stringfest Analytics.

I like to bring this up when explaining the benefits of using spreadsheet applications, primarily Excel. You really to get see the calculations unwind and the numbers crunch in a way not typically possible with a scripting language.

Analysts use financial models to determine investment periods, capital allocations, and price points. Financial modeling requires building assumptions and “guess-timates” into an integrated product, where uncertainty and expected risk-return tradeoffs are made tangible.

What the future holds is anybody’s guess, so the best financial model accommodates for user interaction: change the interest rate at Year 0, for example, and you’ll see the cash flows at Year 5 instantly. Now that’s visual calculation.

At the same time, getting users involved in making changes to the model can be dicey. You’ll want to client-proof your workbook before allowing that, and know how to recover and compare errant versions.

In the below workshop, I provide an overview of financial model-building in Excel, and the workbook management that goes along with it. Learners will use how to build models that are easy to use and hard to break.

Lesson 1: Workbook design

Objective: Student can follow best practices in designing a new Excel workbook

Description:

• Designing an input, process, output flow
• Excel tables
• Managing worksheets in a workbook
• Naming and referring to objects

Exercises: Create a financial model

Assets needed: None

Time: 40 minutes

Lesson 2: Workbook management

Objective: Student can troubleshoot, debug and calculate worksheets in one or more workbook

Description:

• Calculating across worksheets and workbooks
• Setting calculation options
• Formula auditing tools

Exercises: Continue building financial model

Assets needed: None

Time: 50 minutes

Lesson 3: Workbook protection

Objective: Student can add validation and protection features to a workbook

Description:

• Data validation
• Range, worksheet and workbook protection
• Exercises: Continue building financial model

Assets needed: None

Time: 45 minutes

Lesson 4: Workbook analysis

Objective: Student can perform basic sensitivity and scenario-planning analysis on a financial model

Description:

• One- and two-way data tables
• Scenario manager
• What-if analysis
• Goal seek

Exercises: Analyze financial model

Assets needed: None

Time: 50 minutes

Lesson 5: Workbook deployment

Objective: Student can distribute, recover and compare versions of a workbook

Description:

• Workbook co-authoring and version history
• Spreadsheet compare

Exercises: Recover and compare financial model

Assets needed: None

Time: 35 minutes

This download is part of my resource library. For exclusive free access, subscribe below.

The post Learning guide: Financial modeling in Excel, half-day workshop first appeared on Stringfest Analytics.

Multiple groups and points in time

I tend to start teaching inferential with the independent samples t-test, which allows users to compare means between two groups.

That may sound so constraining as to provide little use in the real-world, but quite the opposite is true. In fact, procedures like the t-test power some of the most common data tasks done today, such as the A/B test.

That said, there are probably quite a few other things you’d like to test: what about the differences across multiple groups? The difference across multiple points in time of the same subject?

There are also some assumptions to the t-test which don’t always hold up. What do you do then?

This workshop discusses these next-level statistical considerations, then caps with the queen of data analysis: linear regression.

Correlation and causation

We’ve all heard “correlation is not causation,” but what does that really mean? The second half of this workshop breaks it down right from Excel. Students will learn how to use a combination of visualizations and statistics to infer causality and make predictions.

The deeper instinct students can gather from these tests, the easier it will be for them to unpack more advanced tests and algorithms. By using Excel, this workshop keeps the focus off technology and coding, and on the more important statistical foundations.

Lesson 1: Comparing categories

Objective: Student can compare the expected values of two categories

Description:

• T-tests, continued
• Chi-square independent samples test          

Time: 35 minutes

Assets needed: A/B test results dataset

Lesson 2: Comparing repeated measures

Objective: Student can compare the means of dependent samples

Description:

• Repeated measures in statistics
• Dependent samples t-test

Time: 35 minutes

Assets needed: Patient records dataset

Lesson 3: Comparing multiple groups

Objective: Student can compare the means of more than two groups

Description:

• One-way ANOVA
• Visualizing & interpreting resultzs
• Post-hoc tests and Type II error

Time: 75 minutes

Assets needed: Abalone snails data

Lesson 4: Parametric and non-parametric tests

Objective: Student can compare groups using non-parametric methods

• Parametric versus non-parametric tests
• Statistically testing for normality
• Wilcoxon signed-rank test

Time: 75 minutes

Assets needed: Patient records dataset

Lesson 5: Correlations

Objective: Student can correlate two or more variables and visualize the results

• Correlations and covariances
• Testing for correlations
• Correlations and visualizations
• Spurious correlations
• From correlation to causation

Time: 90 minutes

Assets needed: Athlete records dataset

Lesson 6: Linear regression

Objective: Student can conduct and interpret a univariate linear regression

• Checking assumptions
• Conducting a regression
• Model interpretation & diagnostics

Time: 120 minutes

Assets needed: Athlete records dataset

This download is part of my resource library. For exclusive free access, subscribe below.

The post Learning guide: Intermediate statistics in Excel, one-day workshop first appeared on Stringfest Analytics.

It’s never been harder to be a teacher, but the last few months had to have been so trying for sincere educators. I know they want to put as few barriers between them and their students. I may not be able to control a virus, but I can help teachers warp speed their Excel work so they’re spending more time with students and less with spreadsheets.

This workshop helps teachers develop rosters, gradebooks and other classroom assets. It also imparts principles of data visualization and statistics so they’re capable of presenting valid research to their students and their administrators. Excel is great for this kind of work, which is fortunate — it’s a cheap, easy-to-use tool (and teachers will take as many of those as they can get).

Subscribe below for access to my Excel for teachers half-day workshop learning guide. Consider this a blueprint or recipe for talent development at your organization. You’re welcome to use it in full or part to develop workshops, or get in touch to partner on delivery.

Let’s do it!

The post Excel for teachers learning guide, half-day workshop first appeared on Stringfest Analytics.

Tableau has democratized what it takes to build stunning data visualizations. As a pioneer in so-called “self-service BI,” it’s the equivalent to a desktop-publishing tool for charts and dashboards.

This workshop covers the basics of Tableau with best practices of information design in mind. By the end of the workshop, students will be ready to create that most jaw-dropping of data assets, the dashboard.

Learning about “pills” and “shelves” is just part of the equation, too. While it’s great that Tableau nudges users into making the appropriate chart with their data, users should always practice data visualization with information design principles in mind. That’s another focus of this learning guide, which is yours free below.

Lesson 1: Tableau & self-service BI

Objective: Student can identify the Tableau stack of tools and their role in the data analytics workflow

Description:

• What is Tableau and when would I use it?
• The Tableau suite of tools
• What Tableau can do for you

Exercises: Explore Tableau Public

Assets needed: None

Time: 25 minutes

Lesson 2: Importing & preparing data

Objective: Student can read, clean and merge data from tabular datasets

Description:

• Sorting and filtering
• Changing data types
• Blending data sources
• Cleaning data with Data Interpreter

Exercises: Drills

Assets needed: Baseball records

Time: 35 minutes

Exercises: Drills

Assets needed: Retail orders dataset

Time: 40 minutes

Lesson 3: Building visualizations

Objective: Student can use information design principles to build visualizations

Description:

• The art & science of dataviz
• Tableaucabulary
• Starting with Show Me
• Building vizzes in Tableau
• Saving and sharing workbooks

Exercises: Building & sharing workbooks

Assets needed: Baseball records

Time: 50 minutes

Lesson 4: Aggregations and calculated fields

Objective: Student can create calculated fields, parameters and table calculations

Description:

• Calculated fields
• Table calculations
• Sets
• Parameters

Exercises: Drills

Assets needed: Baseball records

Time: 50 minutes

Lesson 5: Getting started with dashboards

Objective: Student can use information design principles to build basic dashboards

Description:

• What is a dashboard?
• Different types of dashboards
• Creating a dashboard in Tableau
• Modifying a dashboard in Tableau

Exercises: Practice building a dashboard

Assets needed: Baseball records

Time: 50 minutes

This download is part of my resource library. For exclusive free access, subscribe below.

The post Learning guide: Introduction to Tableau, half-day workshop first appeared on Stringfest Analytics.

For as impressive as the new Microsoft stack is, it’s important to remember that Microsoft didn’t invent things like a left outer join or one-to-one relationship. These concepts are old relational data modeling, and SQL has classically been the language to interact with relational database management systems.

From this in Excel to that same thing in SQL

We tend to do the same common data tasks regardless of program used: it could be sorting, filtering, or creating a calculated field. You may perform these all the time in Excel, and you will similarly do them all the time in SQL.

That’s why in this workshop I “pivot” from common Excel tasks into their corresponding SQL tasks. One of the best ways to learn something new is to relate it to what you already know, and this approach really accelerates what can be learned in a short amount of time. This workshop covers the major read operations in SQL. By the end of the workshop, users are able to query information from multiple tables in a database.

SQL is essential knowledge for anyone looking to improve their data chops. This workshop accelerates that knowledge acqusition by relating SQL skills right back to Excel skills.

Lesson 1: SQL and Excel for data analytics

Objective: Student can visually inspect a database with SQLiteStudio

Description:

• What is a database and when would you use it?
• Exploring the SQLiteStudio interface

Exercises: Practice exploring a second database

Assets needed: Baseball database

Time: 30 minutes

Lesson 2: From Excel tables to SQL SELECT

Objective: Student select some or all fields from various tables of a database

Description:

• The grammar of SQL
• Querying fields
• Functions
• Aliases

Exercises: Drills

Assets needed: Baseball database

Time: 35 minutes

Lesson 3: From Excel sort & filter to SQL WHERE and ORDER BY

Objective: Student can sort and filter a query’s results

Description:

• Sorting and filtering
• How NULLs work
• Limiting a query’s results

Exercises: Drills

Assets needed: Baseball database

Time: 30 minutes

Lesson 4: From Excel PivotTable to SQL aggregation

Objective: Student can aggregate and summarize query results

Description:

• Counting records
• Counting and listing distinct records
• Grouping and aggregating records
• Filtering aggregated records
• Using all read clauses

Exercises: Drills

Assets needed: Baseball database

Time: 35 minutes

Lesson 5: From VLOOKUP() to JOIN

Objective: Student can join multiple tables from the same database

Description:

• Relational database modeling
• Left outer joins
• Inner joins
• Set operators

Exercises: Drills

Assets needed: Baseball database

Time: 45 minutes

Lesson 6: From “That’s hard in Excel” to “That’s easy in SQL!”

Objective: Student can conduct intermediate data analysis

Description:

• Subqueries
• Case operators
• Common table expressions

Exercises: Drills

Assets needed: Baseball database

Time: 45 minutes

This learning guide is a part of my resource library. For exclusive free access, subscribe below.

The post Learning guide: SQL for Excel users, half-day workshop first appeared on Stringfest Analytics.

By the end of this workshop, you’ll know that the tidyverse is so-called because it’s a collection of packages used together to clean, model and depict data using “tidy” principles.

More than a package

That means the tidyverse is more than a package. It’s even more than a series of packages. It’s a whole “mental model” of how data should work.

Hadley Wickham and Garrett Grolemund, in R for Data Science depict the data workflow like this:

That is, it’s an iterative process that starts with preparing the data by importing it and then “tidying” it. Those first steps are the focus of this learning guide, along with the basics of data visualization.

There are different ways to prepare a dataset for analysis, but the nice thing about using the “tidy” framework is there are no surprises in how to do it. This framework will help you explicitly think through what needs to happen to a dataset for it to be of much use in your analysis.

After that, the tidyverse provides a suite of tools for continuing your data journey: from the re-shaping, to the manipulating, to the visualizing.

This one-day workshop focuses on the elements of the tidyverse most commonly to be used in basic data cleaning, exploratory data analysis and visualization.

Get your copy of the guide below. You are welcome to use this learning guide at your school or workplace to guide workshops or for however you can benefit from it. Access to the learning guide is not access to a workshop itself. You can use this learning guide to conduct a workshop at your organization. Consider this guide more like a recipe or blueprint.

This learning guide is part of my resource library. For exclusive free access, subscribe to my newsletter below.

If you’re an individual user looking to get acquainted with the tidyverse, I suggest my book Advancing into Analytics: From Excel to Python and R.

Lesson 1: The tidyverse and tidy data

Objective: Student can compare and contrast the tidyverse to the general R environment

Description:

• What is tidy data?
• A tour of the tidy galaxies
• The tidy workflow

Time: 40 minutes

Assets needed: none

Lesson 2: Importing data

Objective: Student can read tabular files into R

Description:

• Introduction to the tibble
• Importing text files
• Importing Excel workbooks

Time: 40 minutes

Assets needed: Baseball records

Lesson 3: Re-shaping data

Objective: Student can transform a dataset to fit tidy principles

Description:

• Pivoting and un-pivoting datasets
• Delimiting columns

Time: 60 minutes

Assets needed: Baseball records

Lesson 4: Manipulating data

Objective: Student can create a data manipulation pipeline

• Manipulating rows & columns
• Aggregating & summarizing data
• Piping functions

Time: 75 minutes

Assets needed: Baseball records

Lesson 5: Joining and appending data

Objective: Student can create a data manipulation pipeline

• Appending two or more tables
• Joining two tables: left, right, inner, outer

Time: 75 minutes

Assets needed: Baseball records

Lesson 6: Miscellaneous tidying

Objective: Student can manipulate strings, factors and dates

• Formatting, replacing and splitting strings
• Ordering and modifying factors
• Generating, calculating and resampling dates

Time: 60 minutes

Assets needed: Flight records

Lesson 7: Visualizing data

Objective: Student can create graphical depictions of variable relationships

• The grammar of graphics
• Plotting univariate relationships
• Plotting bivariate relationships
• Customizing scales, legends & themes

Time: 90 minutes

Assets needed: Baseball records

This learning guide is part of my resource library. For exclusive free access, subscribe to my newsletter below.

The post Learning guide: Introduction to the Tidyverse, one-day workshop first appeared on Stringfest Analytics.

Data is data, so why should it be / Python and Excel go awkwardly?

OK, I’m no lyricist, but there is a beauty in build one skill set off of another.

I believe that every data analyst should have in their “stack” of tools a spreadsheet application, BI/dashboarding program, database and programming language. For that last category, Python is a solid choice.

Data analysts generally come to programming from spreadsheets. Too often, programming languages are seen as the spreadsheet-killer. This should not be the case: after all, spreadsheets are a valued plank of the analytics stack!

Not only that, but “spreadsheet smarts” put the analyst at an advantage for Python mastery. After all, data is data — once the tasks and method become second nature to you, it’s easy to shift production into another tool.

I built the below learning guide with this shift in mind. By starting with the “mental model” of data in Excel, my hope is that analysts can augment their knowledge by picking up Python.

Lesson 1: Python and Excel for data analytics

Objective: Student can compare and contrast uses of Exc­el and Python for data analytics

Description:

• Welcome to Planet Python
• What is Python and when would you use it?
• Working in Jupyter

Exercises: “Hello world” in Jupyter

Assets needed: None

Time: 30 minutes

Lesson 2: From Excel cells to Python lists and dictionaries

Objective: Student can create, inspect and manipulate lists and dictionaries

Description:

• Variable assignment
• Data types
• Indexing and subsetting lists
• Indexing and accessing dictionaries

Exercises: Drills on lists and dictionaries

Assets needed: None

Time: 45 minutes

Lesson 3: From Excel tables to Python DataFrames

Objective: Student can create, inspect and manipulate DataFrames

Description:

• From lists to NumPy arrays
• From NumPy arrays to Pandas DataFrames
• Importing and inspecting a DataFrame

Exercises: Drills

Assets needed: Baseball records

Time: 45 minutes

Lesson 4: From Excel lookups and PivotTables to Pandas manipulation

Objective: Student can manipulate tabular data with Pandas

Description:

• Sorting, filtering, summarizing, renaming
• Merging
• Un-pivoting and re-shaping
• Exporting results

Exercises: Drills

Assets needed: Baseball records

Time: 50 minutes

Lesson 5: Data visualization with seaborn

Objective: Student can visualize univariate distributions

Description:

• Bar charts
• Line charts
• Histograms
• Custom plots & themes

Exercises: Drills

Assets needed: Baseball records

Time: 30 minutes

Lesson 6: From “That’s hard in Excel” to “That’s easy in Python!”

Objective: Student can conduct end-to-end data analysis project

Description:

• Append, transpose, summarize and visualize a set of csv files

Exercises: Drills

Assets needed: Retail sales dataset

Time: 30 minutes

This download is part of my resource library. For exclusive free access, subscribe below.

The post Learning guide: Python for Excel users, half-day workshop first appeared on Stringfest Analytics.

When I was an undergrad, a professor suggested I learn this statistical programming language called R.

I took one look at the interface, panicked, and left.

A lot has changed in the R world since then, not the least of which was the release of the RStudio integrated development environment. While the universe of R packages continues to grow, and the work can now be done from the comfort of RStudio, the fact remains: learning R means learning to code R.

Many of my students have never coded before, although this is a half-truth: they’ve probably used Excel, which requires a decent amount of functions and references. What Excel doesn’t require, though, is naming and manipulating variables.

R is an ideal choice for first-time data coders: the familiar tabular data frame is a core structure. Operations are designed with data analysis in mind: after all, R is a statistical programming language. (In my opinion, this makes it preferred to Python, which was designed as a general-purpose scripting language — again, as far as learning to code as a data analyst goes.)

I assume no prior coding language for this workshop. My goals are to equip students to work comfortably from the RStudio environment, ingest and explore data, and make simple graphical representations of data. In particular, students will perform the most common tabular data cleaning and exploration tasks using the dplyr library.

Above all these objectives, however, is my goal to help students not panic over learning R, like I did when I started.

You are welcome to use this learning guide as you see fit.

1: Welcome to the R Project

Objective: Student can install and load an R package

Description:

• What is R and when would I use it?
• R plus RStudio
• Installing and loading packages

Exercise: Install a CRAN task view

Assets needed: None

Time: 35 minutes

Lesson 2: Introduction to RStudio

Objective: Student can navigate the RStudio integrated development environment

Description:

• Basic arithmetic and comparison operations
• Saving, closing and loading scripts
• Opening help documentation
• Plotting graphs
• Assigning objects

Exercises: Practice assigning and removing objects

Assets needed: None

Time: 40 minutes

Lesson 3: Working with vectors

Objective: Student can create, inspect and modify vectors

Description:

• Creating vectors
• Vector operations
• Indexing elements of a vector

Exercises: Drills

Assets needed: None

Time: 35 minutes

Lesson 4: Working with data frames

Objective: Student can create, inspect and modify data frames

Description:

• Creating a data frame
• Data frame operations
• Indexing data frames
• Column calculations
• Filtering and subsetting a data frame
• Conducting exploratory data analysis on a data frame

Exercises: Drills

Assets needed: Iris dataset

Time: 70 minutes

Lesson 5: Reading, writing and exploring data frames

Objective: Student can read, write and analyze tabular external fines

Description:

• Reading and writing csv and txt files
• Reading and writing Excel files
• Exploring a dataset
• Descriptive statistics

Exercises: Drills

Assets needed: Iris dataset

Time:  40 minutes

Lesson 6: Data manipulation with dplyr

Objective: Student can perform common data manipulation tasks with dplyr

Description:

• Manipulating rows
• Manipulating columns
• Summarizing data

Exercises: Drills

Assets needed: Airport flight records

Time: 50 minutes

Lesson 7: Data manipulation with dplyr, continued

Objective: Student can perform more advanced data manipulation with dplyr

Description:

• Building a data pipeline
• Joining two datasets
• Reshaping a dataset

Exercises: Drills

Assets needed: Airport flight records

Time: 50 minutes

Lesson 8: R for data visualization

Objective: Student can create graphs in R using visualization best practices

Description:

• Graphics in base R
• Visualizing a variable’s distribution
• Visualizing values across categories
• Visualizing trends over time
• Graphics in ggplot2

Exercises: Drills

Assets needed: Airport flight records

Time: 70 minutes

Lesson 9: Capstone

Objective: Student can complete end-to-end data exploration project in R

Assets needed: Baseball records

Time: 40 minutes

This download is part of my resource library. For exclusive free access, subscribe below.

The post Learning guide: Introduction to R, one-day workshop first appeared on Stringfest Analytics.