If there is a list of books in Excel that needs to be sorted by call number, the list will not load properly because of Excel's rules on sorting. Here is a partial example of a list of titles "sorted" by call number in Excel:
AE25 E3 2002 |
AE27 B782x 1996 |
AE27 G76x 2003 |
AE5 E333 2003 |
AE5 E363 2003 |
AE5 E363 2010 |
The problem is obvious from the start. AE5 should be in the list before AE25 and AE27. I have been contacted by others trying to solve this as well, so I went to work looking for a solution.
Enter Power Query.
Power Query is a relatively new feature to Excel. Along with Power Pivot, some very tricky and intricate calculations can be done automatically over multiple sheets in a workbook, or even scattered about your department. I found a way to sort LC call numbers by splitting the Class number into two columns. The alpha column will sort as a general format. Changing the numeric part of the class into a "decimal" format instead of "general" in Excel, will make the list fall in line. The Cutter will automatically sort correctly because excel sorts it correctly as it is.
Once the steps of this method are completed, the resulting table can be used to find and sort call numbers by using the common ID, such as a bibliographic number or ISBN. Just be sure to use a unique number to what you are sorting for each record.
Thanks to Ken Puls' Blog post "Keep Only Numbers in Power Query" for the inspiration behind this method (the link is below).
I have included a copy of my workbook so you can see how I did this.
This method assumes a basic understanding of Power Query.
Using Power Query, find the data you wish to sort. I suggest you stay linked to that table so this list will automatically update when titles are added or removed from the raw data list. For testing, I downloaded part of a larger table and placed in the workbook so you can see how this works.
Two columns are necessary for this procedure:
Convert the worksheet into a table and name it RawData. There will be three separate Power Queries run.
NOTE: The original list of call numbers did not include a decimal between the Class and Cutter numbers. Additional formatting to account for the decimal would be necessary for this to work correctly. Alternatively, if your call numbers are set up with the decimal, please check out the links on the right-hand column with macros that will sort with the decimal present.
The first query sheet you will make prepares the call number for sorting.
Here is a breakdown of the Power Query formulas that run for this procedure. The ones with an asterisk by them automatically run in power query:
*= Excel.CurrentWorkbook(){[Name="Table1"]}[Content]
*= Table.TransformColumnTypes(Source,{{"bibNo", Int64.Type}, {"call_no", type text}})
= Table.DuplicateColumn(#"Changed Type", "call_no", "call_no - Copy")
= Table.RenameColumns(#"Duplicated Column",{{"call_no - Copy", "Sort"}})
= Table.SplitColumn(#"Renamed Columns", "Sort", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Sort.1", "Sort.2", "Sort.3", "Sort.4", "Sort.5", "Sort.6"})
*= Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Sort.1", type text}, {"Sort.2", type text}, {"Sort.3", type text}, {"Sort.4", type text}, {"Sort.5", type text}, {"Sort.6", type text}})
= Table.CombineColumns(#"Changed Type1",{"Sort.2", "Sort.3", "Sort.4", "Sort.5", "Sort.6"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged")
= Table.RenameColumns(#"Merged Columns",{{"Merged", "Sort3"}, {"Sort.1", "Sort"}})
Here is what my worksheet looks like after this step:
Now to do some DAX Codes to divide the Sort column into two separate ones: one of the alpha characters, and one with the Class Number including the decimals.
let
CharsToRemove1 = List.Transform({48..57}, each Character.FromNumber(_)),
Source = Excel.CurrentWorkbook(){[Name="PrepareSheet"]}[Content],
#"Changed Type1" = Table.TransformColumnTypes(Source,{{"Sort", type text}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type1", "Sort1", each Text.Remove([Sort],CharsToRemove1))
in
#"Added Custom1"
4. Click Done and the results should show in the Power Query window.
5. Rename the query Sort1
6. Close & Load the sheet. It should now look like this:
Step 3 is like step 2 except for the code
let
CharsToRemove2 = List.Transform({33..45,47,58..126}, each Character.FromNumber(_)),
Source = Excel.CurrentWorkbook(){[Name="Sort1"]}[Content],
#"Changed Type2" = Table.TransformColumnTypes(Source,{{"Sort", type text}}),
#"Added Custom2" = Table.AddColumn(#"Changed Type2", "Sort2", each Text.Remove([Sort],CharsToRemove2))
in
#"Added Custom2"
4. Remove the Sort column
5. Reorder the columns as Sort1, Sort2, Sort3
6. Change Sort2 to decimal format
7. Sort each row.
Here is steps 4 through 7 in DAX code:
= Table.ReorderColumns(#"Added Custom2",{"bibNo", "call_no", "Sort", "Sort1", "Sort2", "Sort3"})
= Table.RemoveColumns(#"Reordered Columns",{"Sort"})
= Table.TransformColumnTypes(#"Removed Columns",{{"Sort2", type number}})
= Table.Sort(#"Changed Type",{{"Sort1", Order.Ascending}, {"Sort2", Order.Ascending}, {"Sort3", Order.Ascending}})
Note that by making Sort2 a decimal, it will not default to Excel's "General" format that treats numbers differently than in a numeric format. One can get numbers to behave:
Here is the scenario:
My boss wants a list of titles by call number that are in the Reference Collection at the library. They are all LC Call numbers, so all I need to do is get the list and sort it.
Again, this assumes your call numbers are formatted without at decimal between the Class and Cutter, so keep that in mind as you work through this. I will be adding a procedure for formatting call numbers so they are uniform soon!
NOTE: For both of these methods, there must be a decimal between the Class Number and the Cutter Number.
To ignore SuDOC or uncataloged items in your list, use a filter in Power Query to remove them from your list before you start. Here are some of the call numbers in our library that will not display properly using this method:
Please share your thoughts! I would love to know if you found an easier way!