Sort Delimited Data (Ignoring Case) in Power Query
Steps:
1. Load table in Power Query
2. Run Transform Operation on Data Column
3. Drill down to data on one representative cell
4. Use Text.Split function to put delimited string into list format
5. Create Table from List
6. Add Custom Column with Text.Upper and Sort
7. Convert Sorted Data back to a list
8. Copy query and modify code into a custom Function
9. Return to Query and Replace Transform Function
Custom Function:
each
let
Custom1 = Text.Split(_, "|"),
#"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each Text.Upper([Column1])),
#"Sorted Rows" = Table.Sort(#"Added Custom",{{"Custom", Order.Ascending}}),
#"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Custom"})[Column1],
Custom2 = Text.Combine(#"Removed Columns","|")
in
Custom2