Press "Enter" to skip to content

Author: Kevin Feasel

Mass Column Renaming in Power Query

Published 2022-04-05 by Kevin Feasel

Kristi Cantor renames columns en masse:

Recently, we hosted Allision Kennedy on the Raw Data by P3 Adaptive Podcast. During the course of the conversation, our co-host Thomas LaRock expressed his frustration at the lack of a simple method to complete what should be a simple task in Power Query. In Tom’s example, he explained he wanted to replace a given value within the column names without individually renaming all of the columns. He pointed out that this has been possible in Office for 20+ years but requires learning some M to complete in Power Query due to the fact that column headers are not considered data.

It turns out, however, that there is an answer here. Read on for that answer.

Comments closed

Data Profiling in Python

Published 2022-04-05 by Kevin Feasel

Brendan Tierney uses Python to look at some data:

One of the most common, and sometimes boring, task when working with datasets is writing some code to profile the data. Most data scientists will have built a set of tools/scripts to help them with this regular and slightly boring task. As with most IT tasks we should be trying to automate what we can, to allow us to spend more time on more important tasks, such as deriving insights and delivering value to the business, instead of repeatedly writing code to produce various statistics about the data and drawing pretty pictures.

I’ve written previously about automating and using some data profiling libraries to help us with this task. There are lots of packages available on pypi.og and on GitHub. Below I give examples of 5 Python Data Profiling libraries, with links to their GitHubs.

Brendan includes some good examples of libraries here so check it out.

Comments closed

Paginated-Ish Reports with Excel

Published 2022-04-05 by Kevin Feasel

Matt Allington goes to the bargain bin:

Paginated Reports have been available in Power BI since 2019.  They serve an important purpose, but they are not easy for the average business user to learn, plus they require Power BI Premium to use.  In my blog and video today, I will show you how you can use Excel as a substitute for Paginated Report Builder to build simple paginated reports from your Power BI Desktop data model.

Click through to see how.

Comments closed

DiskChangeFileSize Error on Backups

Published 2022-04-05 by Kevin Feasel

Greg Dodd diagnoses a problem:

I hit an error recently on a server that caused backups to fail. The database was backing up to a UNC path. Looking in the SQL Log file and Event Viewer, I found the following error:

The operating system returned the error ‘121(The semaphore timeout period has expired.)’ while attempting ‘DiskChangeFileSize’ on ‘\\uncpath\folder\databasename.bak’.

Read on to see what caused this error.

Comments closed

Log Analysis with Powershell

Published 2022-04-05 by Kevin Feasel

Chad Baldwin doesn’t need no fancy GUIs:

Let’s talk about how to make something that’s already super exciting, even more fun, by using PowerShell. Why bother with fancy GUI’s and polished tools when you can do it the fun way?

Yes, there’s lots of good options now when it comes to logging, like structured logs, AWS CloudWatch, Azure Monitor, ELK, etc. Tools that give you a lot of power when it comes to filtering, alerts, and monitoring. However, I still often find myself digging through good ol’ *.log files on a server.

Read on for some good information about how to analyze a log file using nothing more than Powershell.

Comments closed

Azure Delete Locks

Published 2022-04-05 by Kevin Feasel

Denny Cherry has some advice:

When I’m working in a client’s Azure environment, and they don’t have a delete lock on their production environment I always work on getting them to have one.

This doesn’t always play nicely with everything in Azure, so read on for Denny’s advice when working with Azure Migrate.

Comments closed

Creating a Generic SSRS Report

Published 2022-04-04 by Kevin Feasel

Tim Mitchell breaks out the black print on white cardboard:

When a simple presentation of data is needed, SQL Server Reporting Services (SSRS) is a common tool of choice. Creating simple tabular, row-and-column type reports can be done relatively quickly and easily in SSRS. But what happens if you need a lot of these reports? If you need to present dozens or even hundreds of reports to end users, building even a simple report for each one can take time. Unfortunately there’s no Biml-like automation tool for SSRS, so this work must typically be done by hand.

However, it is possible to create a generic report in SSRS that can dynamically present information from tables or views. In this post, I’ll share my implementation of this.

Looking at Tim’s solution, I’d be a bit concerned about longer-term maintenance. In particular, my worry would be that what you gain in ease of development, you give away in making troubleshooting and report maintenance harder.

Comments closed

Emphasizing One Data Point in Excel

Published 2022-04-04 by Kevin Feasel

Elizabeth Ricks makes a point:

Today’s post is a tactical one: how to highlight a data point in Excel. 

When we craft visualizations for explanatory purposes—that is, when there’s a specific finding or recommendation that we want to communicate to someone specific—our goal is to drive action. In those cases,  our visuals should emphasize what’s interesting in the data and what requires attention. Highlighting key points in our graph is an important step in creating successful explanatory communications.

Read on for examples as well as how to do this.

Comments closed

PRECEDING and FOLLOWING in Window Functions

Published 2022-04-04 by Kevin Feasel

Reitse Eskens looks at a couple of useful window operators:

I was teaching a class today on more advanced SQL queries and we were discussing if you could use preceding and following in a single windowing function.

Windowing Functions

If you’re not familiar with windowing functions, they’re really cool. You can perform all kinds of trickery and magic with them. Let’s start with a simple example.
The dataset I’m using has flight data from the early 2000’s.

Read on for the example and for the answer to Reitse’s question.

Comments closed

Monitoring Kubernetes in Production

Published 2022-04-04 by Kevin Feasel

Samir Behara provides some guidance:

Kubernetes is an open-source container orchestration system for automating the deployment and management of containerized applications. Kubernetes provides capabilities like service discovery, horizontal autoscaling, and load balancing, while ensuring that application configurations are declarative and that systems are self-healing.

In this article, I will explain how to monitor your Kubernetes cluster and implement automated health checks, and discuss the various monitoring tools available.

Read on for some thoughts. In addition to Samir’s links and ideas, I’d also throw in some tools like Rancher to make management a little easier.

Comments closed