Convert “Column1” to “A”

I admit that the technique I will share with you in this post may not have many use-cases, but it was a crucial component for the Excel Compare Power BI app that we released on AppSource earlier this week, and you can consider reading this post as a technical challenge to evaluate your Power Query and M skills (or the skills of your colleagues and team).

Background

As part of our focus in delivering low-code data quality automation using Power Query here, and here, we have released a new auditing app that allows you to compare between two Excel files and track changes and errors.

• 

• 

• 

One of the core features of the app is that it compares the values and errors in cells across two matching worksheets and returns “A –> B” if the value had changed from A to B. Or “A –> #error” if a new error was detected in the cell in the second worksheet. To make the solution user-friendly, we wanted to use the column and row “coordinates” to direct the users to the changes and errors. And here is the problem — When you import an Excel worksheet into Power BI, the column names are represented as “Column1”, “Column2”, …, “Column34” etc, and not as “A”, “B” or “AH”.

We didn’t want to tell users – “look at Column34, Row 1 to review the change we have found in the app”. This information will leave the client with the need to start counting to 34 from Column A to find the right column with the change. Instead, we preferred a user experience that will guide the user to “look at Column AH, Row 1 to review the change”.

Before we share our solution, could you challenge yourself and try it on your own?

Your Challenge

Write a Power Query function that receives a number and returns its corresponding Excel alphabetical column name. Here are few examples?

For column “Column1”, the function will receive 1 and return A.

For column “Column2”, the function will receive 2 and return B.

For column “Column34”, the function will receive 34 and return AH.

The Solution

Please don’t keep reading, before you give it a try…

If you are really stuck, here is a hint…

HINT 1: Solving only A to Z is easier, but for very wide tables with 1000 columns how would you solve it?

HINT 2: Will a recursion work for you? Take a look at this book excerpt on M recursions.

HINT 3: There are 26 letters in English and you can use Number.IntegerDivide and Number.Mod to generate a single English character in each iteration of the recursion.

---

The Recursion

let
    GetLetterFromNumber =
    (ColumnNum as number) as text =>
    let
        Mod = Number.Mod(ColumnNum - 1, 26),
        
        PrefixLetter =
            if Mod + 1 = ColumnNum then
                ""
            else
                @GetLetterFromNumber(Number.IntegerDivide(ColumnNum - 1, 26)),

        Result = PrefixLetter & Character.FromNumber(Mod + 65)  
    in
        Result

in
    GetLetterFromNumber

The List.Generate Solution

Now, when you have the function working, you can move to a more advanced option to solve this challenge using a loop. Here is a great solution that was shared in the comments below by Stéphane. Using loops instead of recursions is typically better when you work with large data since recursions will soon consume the call stack of your computer. But practically speaking – For our specific use-case, you will not notice any gain as the largest column is XFD, and recursion of 3 levels is not a big deal…

(ColumnNum as number) as text =>
    Text.Combine(
        List.Reverse(
            List.Transform(
                List.Generate(
                    () => ColumnNum,
                    each _ >= 1,
                    each (_ - 1) / 26
                ),
                each Character.FromNumber(
                    Number.Mod(
                        Number.RoundDown(_) - 1,
                        26
                    ) + 65
                )
            )
        )
    )

Please try the app, and consider writing a review here.