Lettered Index in Power Query

%3CLINGO-SUB%20id%3D%22lingo-sub-2888345%22%20slang%3D%22en-US%22%3ELettered%20Index%20in%20Power%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2888345%22%20slang%3D%22en-US%22%3E%3CP%3EHeyo%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20anyway%20to%20simplify%20this%20code%3F%20Maybe%20using%20List.Generate%3F%20I'm%20trying%20to%20take%20a%20table%20and%20add%20a%20lettered%20index%20A-Z%20as%20opposed%20to%20numbered.%20I%20couldn't%20find%20any%20web%20posts%20on%20the%20idea%20so%20I%20hashed%20this%20one%20up%20and%20figured%20I%20should%20post%20my%20solution%20.%20.%20.hopefully%20the%20community%20can%20hash%20out%20a%20better%20one%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETable.RemoveColumns(%20Table.AddColumn(Table.AddIndexColumn(%20Source%2C%20%22Index1%22%2C%201%2C1%2CInt64.Type)%2C%22Index%22%2Ceach%20List.ReplaceMatchingItems%20(%7B%5BIndex1%5D%7D%2C%20List.Zip(%7B%7B1..26%7D%2C%7B%22A%22..%22Z%22%7D%7D))%7B0%7D)%2C%20%7B%22Index1%22%7D)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheers!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2888345%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Visitor

Heyo

 

Is there anyway to simplify this code? Maybe using List.Generate? I'm trying to take a table and add a lettered index A-Z as opposed to numbered. I couldn't find any web posts on the idea so I hashed this one up and figured I should post my solution . . .hopefully the community can hash out a better one?

 

Table.RemoveColumns( Table.AddColumn(Table.AddIndexColumn( Source, "Index1", 1,1,Int64.Type),"Index",each List.ReplaceMatchingItems ({[Index1]}, List.Zip({{1..26},{"A".."Z"}})){0}), {"Index1"})

 

Cheers!

1 Reply

@vinoqueries Not really sure why you would want to create an A to Z index, as you limit yourself to 26 rows. But, I take it that you have a good reason for doing so. To simplify your code, you could try this. After adding the regular index column from 1 to max 26, add the AlfaIndex column using:

 

= Table.AddColumn(#"Added Index", "AlfaIndex", each Character.FromNumber ([Index]+64))

 

Alternatively, create a static table in Excel. One column with a number, another with a letter and merge your indexes data table with the static one.