Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to 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
=List.TransformMany({"A".."Q","S".."Z"},each {1..9999},(x,y)=>x&Number.ToText(y,"0000"))
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