Microsoft Tips and Codes

The first query sheet you will make prepares the call number for sorting.

1. From the ribbon, Click Data > From Table
2. Select the call number column and from the Add Column tab, click Duplicate
3. Rename the duplicated column Sort
4. Select the Sort column
   • From the Transform ribbon, select Split Column > By Delimiter
   • Choose Space for a delimiter if it is not already selected
   • Click Insert (again, this will not work if you have a decimal between the Class and Cutter numbers). This will split all the pieces of the call number into columns where there are spaces in the call number.
5. Select all columns Sort.2 through Sort.# where # is the number of the last column. Do not select Sort.1
   • From the Transform ribbon, select Merge Columns and use a Space delimiter
   • Rename the column Sort3
6. Rename the Query PrepareSheet (or whatever makes sense to you. I will use this name for the following functions)
7. From the Home tab, click Close & Load
8. Rename the resulting sheet PrepareTable

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.

1. From the Data Ribbon: Click on New Query > From Other Sources > Blank Query
2. When The Query window opens, click on Advanced Editor
3. If you have named the tables and columns the same as mine, paste the following code in the editor window:

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

1. From the Data Ribbon: Click on New Query > From Other Sources > Blank Query
2. When The Query window opens, click on Advanced Editor
3. If you have named the tables and columns the same as mine, paste the following code in the editor window:

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.

1. Open a new Excel workbook and link it to the list of titles through Power Query, adding the list to the data model
2. Also link this method to the new workbook, adding it to the data model
3. Using an INDEX() / MATCH() formula, I can add the three sort columns to the correct records by the common ID
4. Once the three columns are added
   1. Go to the Home Ribbon
   2. Click the pulldown arrow under Sort & Filer and choose "Custom Sort..."
   3. Click "Add Level" twice to add two sorting columns
   4. Populate the "Sort By" fields in this order: Sort1, Sort2, Sort3
   5. Click OK.
5. Your list should be in call number order.

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!