List of Lists

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,953
Reaction score
0
Points
36
Excel Version(s)
O365
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
 
Last edited:

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,953
Reaction score
0
Points
36
Excel Version(s)
O365
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

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.
 

Attachments

  • ExcelGuru 1686 - List of Lists as Parameter.xlsx
    16.3 KB · Views: 5
Last edited:

JoePublic

Super Moderator
Staff member
Joined
Sep 16, 2011
Messages
245
Reaction score
3
Points
18
Location
UK
Excel Version(s)
2016
The numbers in your lists are text. You could use something like:

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

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,953
Reaction score
0
Points
36
Excel Version(s)
O365
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

Super Moderator
Staff member
Joined
Sep 16, 2011
Messages
245
Reaction score
3
Points
18
Location
UK
Excel Version(s)
2016
You're welcome. It's always easier to see when it's not your own code.
 
Top