Press "Enter" to skip to content

Month: January 2021

Creating a Hyperlink in Excel using Powershell

Published 2021-01-14 by Kevin Feasel

Mikey Bronowski continues a series on generating Excel documents with Powershell:

Last week I have mentioned the new functions waiting to be included into the module. This week I would like to write about another set of functions:

– Add-ExcelHyperlink
– Get-ExcelHyperlink
– Remove-ExcelHyperlink

Those are fresh too and were inspired by a query I have got from Garry Bargsley (blog|twitter) who needed adding hyperlinks to the existing cells without using HYPERLINK() Excel function.

Read on to see how.

Comments closed

Power BI Helper Update

Published 2021-01-14 by Kevin Feasel

Reza Rad has an update to the Power BI Helper:

Happy New Year. We wish this be a year full of happiness and joy for all of you. We are glad to let you know that version 12 of Power BI Helper is now available to download with the below new features;

– Detecting the storage mode of tables (DirectQuery, Dual, Import)
– Detecting the connection mode of the file (Import, DirectQuery, Live Connection, Composite/Mixed)
– Report level measures
– Compare two files based on their report level measures
– Analyzing the visualization just by selecting the model
– Feedback form
– Documentation of all the above new information
– bug fixes

Click through for the full changelog.

Comments closed

The Editions of Powershell

Published 2021-01-14 by Kevin Feasel

Jeffrey Hicks gives us an update on the Powershell landscape:

The PowerShell community is beginning another year in the world of PowerShell 7. Most of you know what that means. However, there are newcomers to our community practically every day. Or I know there are occasional or reluctant users who might not pay enough attention to understand the world of PowerShell as it stands today. I wrote this post as a kind of virtual sticky note for the PowerShell community. Feel free to reference this post in your own work so that you don’t have to explain or define “Windows PowerShell” and “PowerShell”.

Click through to learn how to differentiate the two.

Comments closed

Countdown Number Puzzle

Published 2021-01-13 by Kevin Feasel

Tomaz Kastrun has a fun puzzle for us:

So the game is (was) known as a TV show where then host would give a random 3-digit number and the contestants would draw 6 random numbers from stack of numbers. Given the time limit, the winner was the one who would create a formula matching the result or being closest.

Many ways, tips, tricks and optimisations were already considered, maybe the most famous was the Reverse Polish notation where operators follow their operands and is a great fit for the game.

With useless functionality, I have decided to use permuteGeneral function from RcppAlgos or same functionality could be achieved with combn function.

Click through to see it in action.

Comments closed

Using Color Intentionally in Visuals

Published 2021-01-13 by Kevin Feasel

Elizabeth Ricks continues a series:

To create more impactful explanatory visuals, develop the habit of using color in a purposeful way. When applied thoughtfully, color is arguably the most important decision you make when focusing your audience’s attention where you want it. 

One way to start is by overriding the default color palette of your data viz tool and start with nothing emphasized.

Using color for emphasis is important. Color is a pre-attentive attribute, so it’s great to take advantage of that powerful implicit signal. And that means not using lots of different colors.

Comments closed

Soft Deletes in SQL Server

Published 2021-01-13 by Kevin Feasel

Erik Darling has some thoughts on soft deletes:

Implementing soft deletes for an app that’s been around for a while can be tough. In the same way as implementing Partitioning can be tough to add in later to get data management value from (rebuilding clustered indexes on the scheme, making sure all nonclustered indexes are aligned, and all future indexes are too, and making sure you have sufficient partitions at the beginning and end for data movement).

Read the whole thing. Incidentally, this also ties well into a recent post by Erik about deleting into a different table. It can be easier to implement soft deletes as deleting from the current table and adding to an archive table. That gives you the benefits of keeping deleted data while not running into some of the problems Erik mentions. And if you want to undo a deletion? Delete from the archive table and insert back into the main table.

Comments closed

Spelling Cleanup with Power Query

Published 2021-01-13 by Kevin Feasel

Imke Feldmann has some misspellings:

A typical problem with data that has been created by manual entries is that category values are often misspelled or missed. So in this article I’m showing a very powerful technique on how to deal with this problem to clean up dirty category data. It was inspired by the “Preppin’ data” challenge whose instructions you can read here.

Read on for the solution.

Comments closed

Using sp_prepare with Plan Guides

Published 2021-01-13 by Kevin Feasel

Aaron Bertrand tries something different:

There are features many of us shy away from, like cursors, triggers, and dynamic SQL. There is no question they each have their use cases, but when we see a trigger with a cursor inside dynamic SQL, it can make us cringe (triple whammy).

Plan guides and sp_prepare are in a similar boat: if you saw me using one of them, you’d raise an eyebrow; if you saw me using them together, you’d probably check my temperature. But, as with cursors, triggers, and dynamic SQL, they have their use cases. And I recently came across a scenario where using them together was beneficial.

Read on to see the method to this madness.

Comments closed

Little Things in Azure Data Factory

Published 2021-01-13 by Kevin Feasel

Rayis Imayev has some kind words about small niceties in Azure Data Factory:

Recently Microsoft team conducted a brief year-end survey about a “one thing” that Azure Data Factory (ADF) “made your day in 2020” – https://twitter.com/weehyong/status/1343709921104183296. There were different responses from the global parameters support to the limit increase of ADF instances per subscription.

I personally like the little things that are not easily detected on a surface, but with a deeper immersion into a data pipeline development, your level of gratefulness increases even more.

Click through for a few examples.

Comments closed