PowerShell: Things I used to solve with Excel that I now solve with PowerShell
Gateway drug
In a lot of way, Excel was my gateway drug to programming, and I used to use it for everything. There was a time when I started my career where I would open up Excel for any task. It could be as simple as taking down bullet points, but most of the time is was for tasks that related to numbers or data, of course.
I use PowerShell every day these days, and some of the tasks for which I used to use Excel, I now use PowerShell. If I've set it up right, sometimes it's "quicker". There's a lot of technical reasons, but at the base of it is that I get a quiet satisfaction from being able to use the command line.
A lot of the time, I don't even have to copy-paste onto anything, because PowerShell can perform operations on data that is stored on the clipboard! For all of the examples below, I alias Get-Clipboard into cb, and I alias Set-Clipboard into scb.
Applications
Finding duplicates
Many times, I would need to find the duplicates in a list of text, numbers. There are many ways of approaching this, but the younger me would probably have pasted this into Excel, conditional format for duplicates, and sort for the highlighted red ones.
Now, I have a function that's loaded into my PowerShell profile that finds the duplicates in an input array.
It groups the elements, thereby giving a list of unique members in the list, using the commandlet Group-Object. This commandlet gives you the count of how many multiples there are of each member. Then, it's a simple matter of filtering for those members that have a count of more than 1.
function duplicates($array) {
return $array | Group-Object| Where-Object { $_.Count -gt 1 }
}
Sorting a list
PowerShell has a built in commandlet that does this, and most of the time, I don't even need to use anything more than the default behaviour.
(cb) | Sort-Object
Summing/averaging/counting a list
This again is a built-in commandlet from PowerShell. The only thing to note is that you need include the right flags for the operation that you want, like -Sum, -Average, etc.
(cb) | Measure-Object -Sum
Simple "column-wise" text manipulation
Let's say that we need to append the same piece of text to every member of a list of text. For example, I have a list of text and I want to put "[ ] " before every member, to make it into a checklist. This is a bit trickier because it's not a stock one-liner that I can use, and PowerShell loses out to VS Code in terms of user-friendliness for this task.
However, if I can be bothered writing it out, I will do something like this:
(cb) | % { "[ ] " + $_ } | scb