Log In
Register
Home
August 3, 2020
Offline
Hi,
I'm trying to adapt Rick de Groot's excellent function to extract multiple elements of a list, which requires two parameters: the base list and the numerical modifiers detailg which elements to extract.
I have a table of, for example, diners and the menu items they have chosen as a list of numbers, so that Selection 1,4,8 equates to the second, fifth and ninth elements of the list.
I'm almost there and have created the list of selections, but it's in text form, so can't be used as a list index - see the attached file, qryNamesAndSelectionList. Step "Select list elements - wrong" almost works, except for the issue just mentioned. Step "Select list elements - fixed" hard codes the selection list which, while it gives an output, isn't what I want!
I think that what I need is for Step "Create column of lists" to create a list of numbers: at present, I'm using Text.Split which, of course, outputs a list of text values. Can someone kindly advise me how to make this a list of numbers?
Hope this makes sense!
Pieter
See Answer
Moderators
January 31, 2022
Online
You can transform the list of texts to a list of numbers by wrapping the Text.Split in a List.Transform function.
Answers Post
August 3, 2020
Offline
Hi Riny,
Very many thanks - I was struggling with the List.Transform ... each Number.From(_) part of the wrap syntax and it was driving me crazy! I took a slightly different route for the last step, using the GUI to Expand Values... with a comma delimiter, so my total code is:
let
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Create column of lists" = Table.AddColumn(Source, "SelectionList", each List.Transform( Text.Split([Selection],",") ,each Number.From( _ ) )),
#"Select list elements" = Table.AddColumn(#"Create column of lists", "Meal", each fxListSelectPositions(lstChoices,[SelectionList])),
#"Extracted Values" = Table.TransformColumns(#"Select list elements", {"Meal", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
#"Removed Other Columns" = Table.SelectColumns(#"Extracted Values",{"Diner", "Meal"})
in
#"Removed Other Columns"
... and it does exactly what I needed, so thanks again!
Pieter
1 Guest(s)
