Generate a dynamic list of items

[gue]

I want to generate a list like { "member_1", "member_2", ... "member_n"} where "n" is stored in a variable
Is there an easy way without generating a list of numbers "1 to n", converting to table and ... and merging with another table containing "member_" ?

 

[alansidman]

Where would you like this list to appear?

 

[gue]

Where would you like this list to appear?

I want to split a text into tokens (separated by ","). Depending on the number of tokens I want generate this list, which should be used as headers of a new table.

and is it afterwards possible to change the type of these columns also dynamically (all of the same type)?

= Table.TransformColumnTypes(Source,{{"Data_1", Int64.Type}, ... , {"Data_n", Int64.Type}})

 

[Ron Coderre]

Try this M-Code in a new query:

let
    Limit = 5,
    MyList = List.Transform({1..Limit}, each "Membership_" & Text.From(_)),
    ListTable = Table.FromList(MyList),
    TransposeTable = Table.Transpose(ListTable),
    PromoteFirstRowToHeaders = Table.PromoteHeaders(TransposeTable, [PromoteAllScalars=true])
in
    PromoteFirstRowToHeaders

I think that gets you most of the way to what you want....Does it?

 

[gue]

This was exactly what I was looking for !
Thanks for your support!

 

[Ron Coderre]

Glad that worked for you...

I still felt the pull to automatically assign Int64.Type to the columns, but I couldn't quite get there. I could, however, asign "type number" to the columns:

let
    Limit = 5,
    MyList = List.Transform({1..Limit}, each "Membership_" & Text.From(_)),
    ListTable = Table.FromList(MyList),
    BuidDataTypeString = Table.AddColumn(ListTable, "ColDataTypeText", each "type number"),
    ConvertColToType = Table.TransformColumns(BuidDataTypeString,{{"ColDataTypeText", Expression.Evaluate}}),
    ConcatRecVals = Table.AddColumn(ConvertColToType, "Custom", each Record.FieldValues(_)),
    KeepDataTypeTextCol = Table.RemoveColumns(ConcatRecVals,{"Column1", "ColDataTypeText"}),
    MakeTypeChangeList = KeepDataTypeTextCol[Custom],
    TransposeOrigListCol = Table.Transpose(ListTable),
    PromoteFirstRowToHeaders = Table.PromoteHeaders(TransposeOrigListCol, [PromoteAllScalars=true]),
    SetTypesUsingChangeList = Table.TransformColumnTypes(PromoteFirstRowToHeaders,MakeTypeChangeList)
in
    SetTypesUsingChangeList

There may be more elegant ways to get there, but that's what I came up with.
(I couldn't find a way to use Int64.Type...The above method seems to only work with primitive data types)

 

[gue]

Hi Ron,

this is even better because it covers also whole numbers.
Thanks again!

 

[Bill Szysz]

Hi Ron,
Imke asked me about changing types of columns years ago.
I showed her my approach to the problem.

let
    Limit = 5,
    RonList = List.Transform({1..Limit}, each "Membership_" & Text.From(_)),
    MyTable = Table.FromRows({}, RonList),
    #"Changed Type" = Table.TransformColumnTypes(MyTable, List.Transform(RonList, each {_, Int64.Type}))
in
    #"Changed Type"

 

[Ron Coderre]

Thanks, Bill

I knew there had to be a more elegant approach...It was just eluding me.
I increased your approach by one step to make the Field/Type pair calculation more obvious:

let
    Limit = 5,
    RonList = List.Transform({1..Limit}, each "Membership_" & Text.From(_)),
    MyTable = Table.FromRows({}, RonList),
[COLOR=#FF0000]    Field_DataType_Pairs = List.Transform(RonList, each {_, Int64.Type}),[/COLOR]
    ApplyDataTypes = Table.TransformColumnTypes(MyTable, [COLOR=#FF0000]Field_DataType_Pairs[/COLOR])
in
    ApplyDataTypes

(This one goes in my code stash)

 

[Bill Szysz]

Thanks for reading, Ron

 

[gue]

thanks a lot!
the final solution is really short and easy to understand