Forum Discussion
Lettered Index in Power Query
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.
