Convert numbers to Excel Column Letters in Power Query

Question

I would like to transform Numbers to Excel Column Letters.

There are several threads on the topic but it seems that it has not been answered yet for M language.

I managed to address it with the following code but I am sure someone will have a much more efficient code to propose:

= Table.AddColumn(Source, "Column Letter", each Text.Combine({ if (Number.IntegerDivide(
    Number.IntegerDivide(
        Number.IntegerDivide([Column1]-1,1),26)-1,26))<1 then "" else Character.FromNumber( 64+Number.IntegerDivide(Number.IntegerDivide(Number.IntegerDivide([Column1]-1,1),26)-1,26)),
(if Number.IntegerDivide([Column1]-1,26)-
Number.Abs(
    Number.IntegerDivide(
        Number.IntegerDivide([Column1]-1,26)
        -1,26)*26)<1 then "" else 
        Character.FromNumber(64+
        Number.IntegerDivide([Column1]-1,26)-Number.Abs(
            Number.IntegerDivide(
                Number.IntegerDivide([Column1]-1,26)-1,26)*26))),
    (if [Column1]-Number.Abs(
    Number.IntegerDivide([Column1]-1,26)*26)<1 then "" else Character.FromNumber(64+[Column1]-Number.Abs(
    Number.IntegerDivide([Column1]-1,26)*26)))})) 

Thank you!

Answer 1

I think this custom function will work also:

Edit
*Formula for b corrected as per @VassilisAgapitos note below

(n as number)=>

let 
//make sure it is an integer
    a = Number.IntegerDivide(n,1),
    
    b = Number.IntegerDivide(Number.IntegerDivide(a-1,26)-1,26),
    L1 = if b = 0 then null else Character.FromNumber(b+64),

    c = Number.IntegerDivide(a-b*26*26-1,26),
    L2 = if c = 0 then null else Character.FromNumber(c+64),
    
    d = a-b*26*26-c*26,
    L3 = Character.FromNumber(d+64) 
in  
    Text.Combine({L1,L2,L3}) 

You can use it as a Table.AddColumn argument:

 #"Added Custom" = Table.AddColumn(#"Changed Type", "Column Letter", each fnNumberToExcelColumn([Column Number]), type text)

Answer 2

An uglier version than Ron's, just for fun

=(if [Column1] <703 then "" else Character.FromNumber(64+Number.IntegerDivide([Column1]-27,26*26)))
&( if [Column1] >26 then Replacer.ReplaceText(Character.FromNumber(64+ Number.Mod( Number.IntegerDivide([Column1]-1,26) ,26) ), "@", "Z") else "" )
&(Character.FromNumber(65+Number.Mod([Column1]-1,26)))