Slithering Back In

I'm finally catching up on the latest editions of my newsletter after a bit of a break. Writing newsletters or any kind of content on a regular frequency requires getting into a rhythm along the way. Additionally, not publishing newsletters for a period of time makes this rhythm even harder to get back into. I try to share useful insights in every edition, so there's quite of bit of work that I do behind the scenes to select the content to include and to do additional research on these selected discussions.

That's not to say that I haven't been writing over this time period. In fact, I've been writing quite a lot. Whenever I record a course for LinkedIn Learning for example, I create an outline for the entire course and the videos it entails. Then, within each video, I outline the technical steps and descriptions that I want to go along with each step. I then write out word-for-word scripts that correspond to my actions that I'm recording on the screen.

Between two new courses and weekly videos for three quarters of the year for my ongoing serial course, this adds up to a lot of writing along the way. Here's what these new courses entail!

Python in Excel: Data Outputs in Custom Algorithms and Visualizations

Excel is a mainstay of the business world. It's almost universally on computers around the world serving as the models, reports, and analyses that run the business world.

Conversely, Python is one of the world's most popular programming languages. Check out the TIOBE 's rankings over the last year as an example of proof of its domination in the developer space.

Source: https://www.tiobe.com/tiobe-index/

As standalone processes, there's not necessarily a lot of overlap between these two tools. Excel is an ad hoc tool that lets us easily input data and analyze it, even on a granular cell-by-cell level. The Excel application has limitations, like the number of calculations it stably supports within a workbook. These constraints, however, also make it a powerful ad hoc analysis tool because it does force us to limit the sizes of the models we configure within Excel.

Python, on the other hand, is a programming language that enables us to build large scalable models (like pipelines and automated procedures). We can also use it to run algorithms (like AI models), which can include logistic regression, clustering, and anomaly detection, as well as to create custom visualizations like dendrograms (for hierarchical clustering models). These are also examples of potential ad hoc models and visuals that Excel doesn't otherwise support in its own built-in functions and chart capabilities.

Microsoft recently debuted the Python in Excel functionality, which enables us to write Python code directly in Excel using the PY function within our Excel cell formulas. It's currently in beta mode, but it will hopefully be generally available soon. Without this functionality, using Python for ad hoc analysis can become a tedious because it can take quite a bit of work to configure the data frameworks to use in these models and visuals.

I think of the Python in Excel functionality a sandbox environment for working with Python code. It provides the tangible flexibility of working with Python notebooks (*.ipynb files) as well as the potential for easily connecting to data sources through ETL frameworks in Power Query, XMLA endpoints, and Power BI Datasets (now known as Semantic models). Writing Python code in Excel lets us bridge the gaps between applications that might otherwise require a lot of work and potentially become quite tedious to set up. For example, it allows us to easily:

1. Connect to an API and transform the data into a data table.
2. Load the data table directly into Excel.
3. Use this Excel data directly in Python code to run an algorithm on it.
4. Visualize the outputs of this algorithm in a custom visual created with Python code.

Course link: https://www.dhirubhai.net/learning/python-in-excel-data-outputs-in-custom-data-visualizations-and-algorithms

My latest LinkedIn Learning course focuses on how to use this Python in Excel sandbox environment for data modeling to create neat AI models and visuals like:

• Logistic regression
• KMeans clustering
• Hierarchical clustering
• Dendrograms
• Anomaly detection

Power BI Data Methods

Earlier this year, I created a completely new course update for the Power BI Data Methods course that was first published in the LinkedIn Learning library in 2019. In order to even create Power BI models, we need to get the data into our model by through various data methods that I focus on in this course.

Want to be good at Power BI? Learn Power Query! Want to be good at Excel? Again learn Power Query.

We can use Power Query to build ETL frameworks to load the data into our model. This data can come from databases, flat files, and various online sources. We can then transform the data connection into a useable data table to load into our Power BI model. I explore how to build these key ETL frameworks with this Power BI Data Methods course!

Course link: https://www.dhirubhai.net/learning/power-bi-data-methods-23487089

Some of my favorite concepts from this course include:

• Connecting to an API and transforming an XML or JSON data structure into a data table.
• Connecting to files in a folder and combining it together into a single data table.
• Unpivoting and pivoting data tables directly.
• Creating a contiguous calendar dates table.

Up Next

Tomorrow (Tuesday September 10th) at 10 AM Mountain Time/11 AM Central Time, I'm going to be doing a LinkedIn Live session with another LinkedIn Learning instructor, Barton Poulson, PhD on how to make data work within Power BI. We always have great conversations about data between ourselves and we're excited to invite others to be part of this interactive discussion as well!

Event link: https://www.dhirubhai.net/events/powerbianddatawork-withhelenwal7237516357850800129/theater/

Hope to see you at the live event tomorrow, otherwise a recording will be available here on LinkedIn afterwards.

-HW