List of Lists

[Bob Phillips]

I am trying to build a list of lists from data in an Excel cell (it is multiple sort criteria). I am supposing to put data similar to this in the cell

Column1,0,Column2,0,Column3,0

and my query will split that into a list of lists that I can use in a sort, such as this

columns.sort = Table.Sort(nulls.remove, columns.sortorder),

1. If I create a simple list of lists in PQ that can be fed to the sort it works fine, example

columns.sortorder = {{"Company",0},{"FOOH or Union",0},{"Area-Union Code",0}},

2. If I try and use a test string and split it to feed to the sort I get invalid sort criteria, example

columns.sortorder = List.Split(Text.Split("Company,0,FOOH or Union,0,Area-Union Code,0", ","), 2),

3. I can create a list of lists from the Excel cell to feed to the sort but this also gives invalid sort criteria, example

columns.sortorder = List.Split(Text.Split(Excel.CurrentWorkbook(){[Name="tblTableParameters"]}[Content]{0}[SortBy], ","), 2),

As far as I can tell, the list of list in all 3 of the above are identical, but #1 works in the sort, #2 and #3 do not work.

Can anyone see what I need to do to get this to work, or suggest another approach (I can't have multiple cells, it must all be in one cell).

TIA

 

[Bob Phillips]

Here is a workbook to demonstrate the issue.

I have a data table tblData that I wish to do a multi-column sort on. The sort criteria are in another table tblTableParameters.

My query has 3 attempts at sorting the data, according to the 3 methods I outlined above. The third, or some variation of, is the one I want to get working.

This is the M code

let
   Source = Excel.CurrentWorkbook(){[Name="tblData"]}[Content],

   sort.order1 = {{"Name",0}, {"Age",0}},
   result1 = Table.Sort(Source, sort.order1),

   params.sort2 = "Name,0,Age,0",
   sort.order2 = List.Split(Text.Split(params.sort2, ","), 2),
   result2 = Table.Sort(Source, sort.order2),

   params.sort3 = Excel.CurrentWorkbook(){[Name="tblTableParameters"]}[Content]{0}[SortBy],
   sort.order3 = List.Split(Text.Split(params.sort3, ","), 2),
   result3 = Table.Sort(Source, sort.order3)
in
   result3

If you step through the code you will see that step result1 returns the list sorted as required. Steps result2 and result3 both return an invalid sort criteria error.

 

[JoePublic]

The numbers in your lists are text. You could use something like:

List.Split(List.Transform(Text.Split(params.sort2, ","), each try Number.From(_) otherwise _), 2)

 

[Bob Phillips]

Of course, it's obvious ... isn't it always when someone points it out to you. It had to be something dumb on my part.

Thanks Joe.

 

[JoePublic]

You're welcome. It's always easier to see when it's not your own code.