(There was a reason for this - the field was used elsewhere within a PowerBI model, and the clinicians couldn't get their heads around True/False, PowerBI doesn't have an easy way to map True/False values to strings, so we used 'Clinical/Non-Clinical' as string values).
I am reluctant to share the code example, because I'm preciously guarding an example of an LLM making an error in the hope that I'll be able to benchmark models using this, however here's the powerquery code (which you can put into excel) - ask an LLM to explain the code/predict what the output will look like, and compare it with what you get in excel.
let
MyTable = #table(
{"Foo"},
{
{"ABC"},
{"BCD"},
{"CDE"}
}
),
AddedCustom = Table.AddColumn(
MyTable,
"B",
each if Text.StartsWith([Foo], "LIAS") or Text.StartsWith([Foo], "B")
then "B"
else "NotB"
),
SortedRows = Table.Sort(
AddedCustom,
{{"B", Order.Descending}}
)
in
SortedRows
I believe the issue arises because the column that sorts B/NotB is also called 'B' (i.e. the Clinical/Non-Clinical column was simply called 'Clinical', which is not an amazing naming convention).
