Power Query List from "AA" to "ZZ" or "AAA" to "ZZZ"?

[Wolfspyda]

I saw a great video yesterday that allows me to make a list within PQ from ExcellsFun YouTube that can make a list from A to Z. I then Tried to make a List going from either AA to ZZ or AAA to ZZZ.

I created a Blank Query that simply had ={"AAA".."BBB"} however it didn't like that. Is there a solution to this instead of just making a huge list manually?

 

[Norie]

You could add a custom column.

let
Source ={"A".."Z"},
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Added Custom3" = Table.AddColumn(#"Converted to Table", "Custom", each [Column1]&[Column1]&[Column1])
in
#"Added Custom3"

 

[Wolfspyda]

Ok, thanks. Ill have a crack at that. I'm surprised though that Excel doesn't understand A to Z or even AA to ZZ seeing as that is how it generates its range.

 

[Norie]

What would you expect {"AA".."ZZ"} to generate?

Would it be AA, BB, CC ... ZZ or AA, AB, AC ... ZY, ZZ?

 

[VBA Geek]

I'm surprised though that Excel doesn't understand A to Z or even AA to ZZ seeing as that is how it generates its range.

PQ is able to do that because of the character number associated with each letter. For instance, because "@" has character number 64 and low case "x" has character number 120, when you do {"@".."x"} you will be able to get a sequence of all the characters going from number 64 to 120. But when you do {"x".."@"} it will not work obviously, because it does not go backwards

In order to do what you are after, you can simply do:

let
    Source = List.Transform(
                            {"A".."Z"},
                            each Text.Repeat(_,[COLOR=#b22222][SIZE=3][B]3[/B][/SIZE][/COLOR])
                           )
in
    Source

 

[Norie]

VBA Geek

I knew there was a better way to do it and I knew it involved Text.Repeat, just couldn't get the syntax right.

PS Could something like that be transformed into a function where you supplied the no of times to repeat the character and/or the start/end points?

 

[VBA Geek]

Hi Norie,

yes it would be something like:

(numberOfRepeats as number, startChar as text, endChar as text) as list =>


let
    
    start = Text.Start(startChar, 1),
    end   = Text.End(endChar, 1),


    Result = List.Transform(
                            {List.Min({start,end})..List.Max({start,end})},
                            each Text.Repeat(_,numberOfRepeats)
                           )
in
    Result