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 highlighted columns dynamically.

 

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

     

6 Replies

  • dufoq3's avatar
    dufoq3
    Community Champion
    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

     

    • vbharakhada's avatar
      vbharakhada
      Frequent Visitor
      2 years ago

      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's avatar
        dufoq3
        Community Champion
        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