Table Field identifier using a variable

[grimmj]

Hi all,

this is my first post in this community. Hope I am doing all fine to provide the needed information.

I tried to write a function that extracts data of one (generic) column which consists of a list of records for each row. That means I am extracting the list of records (generating more rows) and afterwards, I am trying to combine the data using the Table.Group command with a desired delimiter.

Sample:

At the beginning the data looks like that (where the field "FixVersions" is of relevant for aggregation):

and finally it should look like that: 

It works in a hardcoded way more or less. However, I want to implement it in a more dynamic fashion as the same logic needs to be applied for several fields/columns sequentially. Unfortunately, I was not able to find a suitable solution in Power Query.

To be precise, I am struggling to identify the relevant field in the Table.Group statement using a variable. 

(fieldname as text, recordProperty as text, delimiter as text) =>
let
    /* mocking test data */
    tmp0 = Table.FromRecords({
        [Key= 22, Summary="test1", FixVersions = Table.ToRecords(Table.FromRows(
        {   {1, "DC-24-1", "DC-24-1"},
            {2, "DC-24-2", "DC-24-2"},
            {3, "DC-24-3", "DC-24-3"}
        },  {"id", "name", "value"} ))],
        [Key= 23, Summary="test2", FixVersions = Table.ToRecords(Table.FromRows(
        {   {1, "DC-24-1", "DC-24-1"},
            {2, "DC-24-2", "DC-24-2"},
            {3, "DC-24-3", "DC-24-3"}
        },  {"id", "name", "value"} ))]
    }),

    /* here we go with the data -- extract values */
    tmp1 = Table.ExpandListColumn(tmp0, fieldname),
    tmp2 = Table.ExpandRecordColumn(tmp1, fieldname, {recordProperty}, {fieldname}),
    groupColumns = List.RemoveItems(Table.ColumnNames(tmp2), {fieldname}),
    aggrColumn = Table.Column(tmp2, fieldname),
    /* tmp3 = Table.Group(tmp2, groupColumns, {fieldname, each Text.Combine(aggrColumn, delimiter)}) -> nio - full set aggregated */
    /* tmp3 = Table.Group(tmp2, groupColumns, {fieldname, each Text.Combine([fieldname], delimiter)}) /* -> nio - compiling issue - field not recognized */
    tmp3 = Table.Group(tmp2, groupColumns, {fieldname, each Text.Combine([FixVersions], delimiter)}) /* -> io - works, however could not be used as function as desired */
in 
    tmp3

The problematic part is given where each Text.Combine(...) starts in the Table.Group statement given.

Is there any trick to achieve the needful? Would be pleased to get some hints. The uncommented version works as a workaround but makes the general application impossible.

Kind regards,

J

[grimmj]

Made my day, thank you.

[jgeddes · Accepted solution]

Try this code and see if it works for you...

(fieldname as text, recordProperty as text, delimiter as text) =>
let
/* testing data
    fieldname = "FixVersions",
    recordProperty = "name",
    delimiter = "|",
*/
    /* mocking test data */
    tmp0 = Table.FromRecords({
        [Key= 22, Summary="test1", FixVersions = Table.ToRecords(Table.FromRows(
        {   {1, "DC-24-1", "DC-24-1"},
            {2, "DC-24-2", "DC-24-2"},
            {3, "DC-24-3", "DC-24-3"}
        },  {"id", "name", "value"} ))],
        [Key= 23, Summary="test2", FixVersions = Table.ToRecords(Table.FromRows(
        {   {1, "DC-24-1", "DC-24-1"},
            {2, "DC-24-2", "DC-24-2"},
            {3, "DC-24-3", "DC-24-3"}
        },  {"id", "name", "value"} ))]
    }),
    transformListValues = Table.TransformColumns(tmp0, {{fieldname, each Text.Combine(List.Transform(_, each Record.Field(_, recordProperty)), delimiter)}})
in
    transformListValues