#30DQuery Challenge, Day 30

Background

This is the twenty-seventh post in my 30 Day Power Query Folding Challenge. You can jump right to my implementation if you’ve read the previous posts.

Alex Powers B | T has concluded a 30 Day Power Query Folding Challenge on YouTube: For the past 30 days participants were given a daily challenge to create a query that uses folding from start to finish. Folding is a technique that passes the query to the data source in a format native to the source. It can greatly improve performance and reduce the amount of data passed between server and client. Koen Verbeeck [B][T] wrote a good post on the topic for MSSQLTips.

Head Into Space

This is a simple sounding task for the final challenge, we need to transform each column header by adding in spaces before each upper case character so that “CodeBankBlog” becomes “Code Bank Blog”.

To do this, we need to inspect each character of the header name. Conveniently, M is case sensitive. Less convenient: our SQL Server is not.

Practice Safe Distancing

Before committing to transforming our source dataset, I’m going to first build a query around one phrase and try to convert it. Once I have my model, I’ll apply it in production (everything is production depending on how you look at it).

To inspect each character, I need to be able to access it by position. If my phrase were a list of characters, it would be easier to inspect.

    Source = "CodeBankBlog",
    MakeListOfChar = List.Transform(
            {0..Text.Length(Source)-1}, 
            each Text.At(Source, _))

There’s a lot happening there.

I’ve used dynamic list generation before. {0..N} will create a list of numbers from 0 to N. In this case, 0 to the Length of my Source less 1. Why -1? Because lists are 0 indexed and if I try to access the Text.At(Source, 12) I will end up with an error.

Power Query window showing a list with an error

The next step is to prep my replacement. I really don’t want to iterate through the list 26 times, once for each upper case character. Conveniently, Power Query has a function for that!

The BIAccountant, Imke Feldmann B | T has a great post on List.ReplaceMatchingItems. Go ahead, pop over and read it. I’ll wait here and take a nap. 😴

Back? 🥴 That was fast!

List.ReplaceMatchingItems needs a list of {Value, Replacement} so I’m going to generate that now. We can generate a list of numbers, but we can also do the same with letters!

    ReplaceList = List.Transform(
            // Generate a list of upper case letters
            {"A".."Z"},
            // Create List of Lists of replacements 
            each {_," " & _})

The transform will create a list of pairs like {{"A", " A"}, {"B", " B"}, {"C", " C"}...}. And you can see below that all the upper case characters are slightly offset.

Text.Combine takes a list of text and combines it with an optional delimiter. I don’t need a delimiter so I will leave it blank, but I will also use Text.Trim to remove the leading space.

    CommitReplace = List.ReplaceMatchingItems(
                    TextList,
                    ReplaceList),
                        
    TextJoinAndTrim = Text.Trim(
                Text.Combine(CommitReplace))

Now that I have my transform steps, I’m going to convert this query into a custom function. 😁 Spencer Baucke B | T has a great post on web scraping with Power Query that includes a clear write up on converting queries to functions. I highly recommend reading it. I’ll go for a short ride while you do. 🚴‍♂️

So, while you were doing that, I did this! Notice there is a new function in there?

(input as text) as text =>
let
    TextList = Text.ToList(input),
    ReplaceList = List.Transform(
            {"A".."Z"}, 
            each {_," " & _}),
    CommitReplace = List.ReplaceMatchingItems(
            TextList,
            ReplaceList),
    TextJoinAndTrim = Text.Trim(
            Text.Combine(CommitReplace))
in
    TextJoinAndTrim

So, I initially performed a little unnecessary wizardry early on. Power Query has a Text.ToList function that…well…converts text to a list of individual characters. No need for all that indexing (but it was fun to write).

The Final Challenge

Referencing AdventureWorks, finishing where we started, I navigate to the DimPromotion table.

    Source = AdventureWorks,
    dbo_DimPromotion = Source{
            [Schema="dbo",            
            Item="DimPromotion"]}[Data]

I’ll need to use the list trick to ensure this folds. Table.ColumnNames will provide me with the list of names to apply my custom function to. Then I will use Table.RenameColumns to update the column names. It takes a list of lists {name, new name} so I’ll tweak my transform to return a list of lists as well with the original column name and the update.

    ColumnNames = Table.ColumnNames(dbo_DimPromotion),

    ApplyFunc = List.Transform(ColumnNames, 
                    // List of { name, new name } 
                    each {_, InsertSpace(_)}),

    RenCols = Table.RenameColumns(dbo_DimPromotion, 
            ApplyFunc)

So, I’m done, it folded and I’ve finished the challenges (ok, there are a couple posts pending out of order)!

Reflection

I committed to two core tasks during this challenge, the 30 days of queries for one and this blog for two.

Power Query and The Challenges

I’ve been using Power Query for a few years, so it wasn’t new to me. I’m familiar with reading the documentation, creating custom functions and refactoring my M to be more clear and reusable. There are certainly a number of functions that are new to me, which for various reasons I have not had a need to use or thought to even seek.

Folding certainly is new to me. I can see great benefits to handing the heavy lifting off to SQL Server and retrieving a more refined dataset. Better SELECTs can mean less load on the server, less irrelevant or unnecessary data being transferred, faster query loads and smaller Excel workbooks.

Power Query can put SQL queries into the hands of anyone who can use Excel, take some of the load off DBAs and empower the end user. Though there could be pitfalls if there are enough people hitting the Server with frequent queries while building their code. An organization, and individuals, needs to keep that in mind. A potential solution could be a small development database that everyone can use for their initial builds before transferring to production.

Where I work, our primary database is held in a proprietary application with no SQL endpoint (at least, not one that doesn’t have an expensive annual license per user) so I won’t get to use folding in Production. It’s still been really cool and I have learned tons. My M code sure looks a great deal better.

Blog

Currently I hold a very broad IT role: DBA for a couple of small Azure SQL Databases, Deskside support for staff, IT training and education for staff, telephony support and account management, O365 account maintenance… It’s been a great opportunity to learn, but is very hard to grow expertise in any one area.

I initially created this blog as a repository for code snippets that I use in my day-to-day work, and sporadically added to it. Mostly neglected it.

This was an opportunity to try and do something a little more with it. Hopefully help others seeking their own answers to various technical challenges, and think through technical challenges more methodically, since I am sharing this knowledge, these thoughts, with [you]. Thank you for taking the time to visit.

THANK YOU!

Along the way, I’ve met some really interesting people. They all have great knowledge and a desire to share it too. Their thoughts, posts, questions and solutions helped me a great deal. A few, in no particular order:

Alex Powers B | T

Petra Mastenbroek-Mvuria B | T

Barrett Studdard B | T

Susan Bayes B | T

Mark Beedle T

Owen Auger T

Final Code

let
  Source = AdventureWorks,
  dbo_DimPromotion = Source{[Schema = "dbo", Item = "DimPromotion"]}[Data],
  ColumnNames = Table.ColumnNames(dbo_DimPromotion),
  ApplyFunc = List.Transform(ColumnNames, each {_, InsertSpace(_)}),
  RenCols = Table.RenameColumns(dbo_DimPromotion, ApplyFunc)
in
  RenCols
SELECT [_].[promotionkey]             AS [Promotion Key],
       [_].[promotionalternatekey]    AS [Promotion Alternate Key],
       [_].[englishpromotionname]     AS [English Promotion Name],
       [_].[spanishpromotionname]     AS [Spanish Promotion Name],
       [_].[frenchpromotionname]      AS [French Promotion Name],
       [_].[discountpct]              AS [Discount Pct],
       [_].[englishpromotiontype]     AS [English Promotion Type],
       [_].[spanishpromotiontype]     AS [Spanish Promotion Type],
       [_].[frenchpromotiontype]      AS [French Promotion Type],
       [_].[englishpromotioncategory] AS [English Promotion Category],
       [_].[spanishpromotioncategory] AS [Spanish Promotion Category],
       [_].[frenchpromotioncategory]  AS [French Promotion Category],
       [_].[startdate]                AS [Start Date],
       [_].[enddate]                  AS [End Date],
       [_].[minqty]                   AS [Min Qty],
       [_].[maxqty]                   AS [Max Qty]
FROM   [dbo].[dimpromotion] AS [_] 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: