How to generate table of values A0001-Z9999

[Believer]

More specifically I need A-Q, S-Z and 0001 to 9999 such as:

A0001

A0002

...

Q9999

S0001

...

Z9999

I have been able to make tables of A-Z and 0001-9999 but was unable to full outer join them to the correct result.  In any case, something tells me that a List function should be able to get me what I need, just haven't been able to wrangle it.

[Believer]

@jbwtp  That's perfect, thanks!!

[wdx223_Daniel]

=List.TransformMany({"A".."Q","S".."Z"},each {1..9999},(x,y)=>x&Number.ToText(y,"0000"))

[Believer]

I discovered that what I wanted was a cross join not a full outer join (I knew that!)  I have a solution where I create 2 separate tables (one letters, one numbers) then I cross join them.  Still interested to know if there's something more elegant, perhaps with only list generation...

[jbwtp · Accepted solution]

Hi @Believer,

You can kind of do it this way

= List.Combine(List.Transform({"A".."Q"} & {"S".."Z"}, (x)=>List.Transform({1..9999}, each x & Number.ToText(_, "0###"))))

I think this is quicker than crossjoin (there are some performance comsideration that you can search in Cris Webb's blogg), but I guess for the number of the items on the lists you would not see much difference.

Cheers,

John