Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Believer
Advocate III
Advocate III

How to generate table of values A0001-Z9999

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.

1 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 

View solution in original post

4 REPLIES 4
Believer
Advocate III
Advocate III

@jbwtp  That's perfect, thanks!!

wdx223_Daniel
Super User
Super User

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

Believer
Advocate III
Advocate III

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...

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 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors