How to expand multiple record columns dynamically

[vbharakhada]

I have multiple columns which contains Record and inside record , I need only 'value'.

I need to ExpandRecordColumn for multiple columns in a single step.

I need to expand the yellow highlighted columns dynamically.

[dufoq3 · Accepted solution]

Hi @vbharakhada, add this as new step (not custom column, but new step)

Replace

1. {"Column1", "Column4, "Column5"} with your columns with records you want to expand
2. Source with your previous_step reference

= List.Accumulate(
    {"Column1", "Column4", "Column5"},
    Source,
    (s,c)=> Table.ExpandRecordColumn(s, c, List.Combine(List.Transform(Table.Column(s, c), Record.FieldNames))))

Whole code with sample data:

let
    Source = #table(null, {{[a=1], 12345, "abc", [b=2], [c=3]}, {[d=1], 23456, "bcd", [e=2], [f=3]}}),
    ExpandedColumnsDynamic = List.Accumulate(
    {"Column1", "Column4", "Column5"},
    Source,
    (s,c)=> Table.ExpandRecordColumn(s, c, List.Combine(List.Transform(Table.Column(s, c), Record.FieldNames))))
in
    ExpandedColumnsDynamic

[dufoq3 · Accepted solution]

Hi @vbharakhada, add this as new step (not custom column, but new step)

Replace

1. {"Column1", "Column4, "Column5"} with your columns with records you want to expand
2. Source with your previous_step reference

= List.Accumulate(
    {"Column1", "Column4", "Column5"},
    Source,
    (s,c)=> Table.ExpandRecordColumn(s, c, List.Combine(List.Transform(Table.Column(s, c), Record.FieldNames))))

Whole code with sample data:

let
    Source = #table(null, {{[a=1], 12345, "abc", [b=2], [c=3]}, {[d=1], 23456, "bcd", [e=2], [f=3]}}),
    ExpandedColumnsDynamic = List.Accumulate(
    {"Column1", "Column4", "Column5"},
    Source,
    (s,c)=> Table.ExpandRecordColumn(s, c, List.Combine(List.Transform(Table.Column(s, c), Record.FieldNames))))
in
    ExpandedColumnsDynamic

[vbharakhada]

Hi @dufoq3 , 
thank you so much, It's working for some scenario but unable work on the below scenario.

I have attached sample table that needs to be expanded.

let
    Source = #table(null, {
{"key-1", "summary1",[self="customFieldOption/10838",value="Regression",id=783], [self="celdOptio838",value="sion",id=1273], [self="custom",value="UAT1",id=2742]}, 
{"key-2", "summary2",null,  [self="egrth/jyt",value="UAT",id=3875], [self="testing",value="Testing Case",id=53]},
{"key-3", "summary3",[self="clhchdwj",value="definite",id=754642],  [self="rtjyf/rhtj",value="UAT",id=3875], null}})
in
    Source

Regards

[dufoq3 · Accepted solution]

Try this:

Result:

let
    Source = #table(null, {
{"key-1", "summary1",[self="customFieldOption/10838",value="Regression",id=783], [self="celdOptio838",value="sion",id=1273], [self="custom",value="UAT1",id=2742]}, 
{"key-2", "summary2",null,  [self="egrth/jyt",value="UAT",id=3875], [self="testing",value="Testing Case",id=53]},
{"key-3", "summary3",[self="clhchdwj",value="definite",id=754642],  [self="rtjyf/rhtj",value="UAT",id=3875], null}}),
    ExpandColumnsDynamic = List.Accumulate(
    {"Column3", "Column4", "Column5"},
    Source,
    (s,c)=> [ a = List.Distinct(List.Combine(List.Select(List.Transform(Table.Column(Source, c), each try Record.FieldNames(_) otherwise null), (x)=> x <> null))),
              b = Table.ExpandRecordColumn(s, c, a, List.Transform(a, (x)=> c & "_" & x ))
            ][b] )
in
    ExpandColumnsDynamic

[vbharakhada]

Thank you so much for the help, Even ChatGPT was not able to help me. Thanks a lot.🤟🤟

[dufoq3]

If you don't want to select columns with records manualy, you can use this as 1st argument for List.Accumulate instead of hardcoded {"Column1", "Column2", "Column3"}

List.Transform(List.PositionOf(List.Transform(Table.ToColumns(Source), (x)=> List.AnyTrue(List.Transform(x, (y)=> y is record))), true, Occurrence.All), each Table.ColumnNames(Source){_})

Whole code:

let
    Source = #table(null, {
{"key-1", "summary1",[self="customFieldOption/10838",value="Regression",id=783], [self="celdOptio838",value="sion",id=1273], [self="custom",value="UAT1",id=2742]}, 
{"key-2", "summary2",null,  [self="egrth/jyt",value="UAT",id=3875], [self="testing",value="Testing Case",id=53]},
{"key-3", "summary3",[self="clhchdwj",value="definite",id=754642],  [self="rtjyf/rhtj",value="UAT",id=3875], null}}),
    ExpandColumnsDynamic = List.Accumulate(
    List.Transform(List.PositionOf(List.Transform(Table.ToColumns(Source), (x)=> List.AnyTrue(List.Transform(x, (y)=> y is record))), true, Occurrence.All), each Table.ColumnNames(Source){_}), //Columns containing record
    Source,
    (s,c)=> [ a = List.Distinct(List.Combine(List.Select(List.Transform(Table.Column(Source, c), each try Record.FieldNames(_) otherwise null), (x)=> x <> null))),
              b = Table.ExpandRecordColumn(s, c, a, List.Transform(a, (x)=> c & "_" & x ))
            ][b] )
in
    ExpandColumnsDynamic

[dufoq3]

You're welcome.