Forum Discussion

vbharakhada's avatar
vbharakhada
Frequent Visitor
2 years ago
Solved

How to expand multiple record columns dynamically

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 highlighte...
Need Help
Show and Tell
  • dufoq3's avatar
    dufoq3
    2 years ago

    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's avatar
    dufoq3
    2 years ago

    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's avatar
vbharakhada
Frequent Visitor
2 years ago

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

dufoq3's avatar
dufoq3
Community Champion
2 years ago

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