How to dynamically expand columns of a table with condition

[smpa01]

@ImkeF @AlexisOlson @watkinnc @CNENFRNL 

I have a table like following

Source = #table({"row", "__name", "id","location","salary","age","stack"}, {        
        {1, #table({"fName","lName"},{{"John", "Doe"}}),101, #table({"country","state"},{{"CA","ON"}}),100000,50,#table({"frontEnd","backEnd"},{{null,"SQL"}})}
        ,{2, #table({"fName","lName"},{{"Jane", "Doe"}}),102, #table({"country","state"},{{"CA","AB"}}),200000,35,#table({"frontEnd","backEnd"},{{"HTML","SQL"}})}
    })

How can I ask PQ to dynamically and efficiently expand all the columns that have a table in them?

I want to end up with this

The full query is below

let
    Source = #table({"row", "__name", "id","location","salary","age","stack"}, {
        
        {1, #table({"fName","lName"},{{"John", "Doe"}}),101, #table({"country","state"},{{"CA","ON"}}),100000,50,#table({"frontEnd","backEnd"},{{null,"SQL"}})}
        ,{2, #table({"fName","lName"},{{"Jane", "Doe"}}),102, #table({"country","state"},{{"CA","AB"}}),200000,35,#table({"frontEnd","backEnd"},{{"HTML","SQL"}})}
    }),
    #"Expanded __name" = Table.ExpandTableColumn(Source, "__name", {"fName", "lName"}, {"fName", "lName"}), 
    #"Expanded location" = Table.ExpandTableColumn(#"Expanded __name", "location", {"country", "state"}, {"country", "state"}),
    #"Expanded stack" = Table.ExpandTableColumn(#"Expanded location", "stack", {"frontEnd", "backEnd"}, {"frontEnd", "backEnd"})
in
    #"Expanded stack"

Thank you in advance

[wdx223_Daniel · Accepted solution]

= Table.FromRecords(Table.TransformRows(Source,each Record.Combine(List.TransformMany(Table.ToRows(Record.ToTable(_)),each if _{1} is table then Table.ToRecords(_{1}) else {Record.AddField([],_{0},_{1})},(x,y)=>y))))

[ImkeF]

Hi @AlexisOlson ,
nice solutions!
But I don't see any recursion in @wdx223_Daniel s solution. 
As he seems to call the source data less times, I would imagine his solution being faster.

[AlexisOlson]

I didn't mean to suggest Daniel's solution involves looping or recursion. It does not and I wanted to see if I could do something similar (as opposed to Chris Webb's solution I linked to, which is recursive).

[AlexisOlson · Accepted solution]

@wdx223_Daniel's solutions is amazing but I also wanted to give this a shot without using any looping or recursion. I don't really know how efficient my solution is compared to other possibilities. Maybe @ImkeF has some intuition.

If your tables always have a single row, then you can take the first row as a record and then expand each row of your table like this:

let
  Source = #table(
    {"row", "__name", "id", "location", "salary", "age", "stack"}, 
    {
      {1, #table({"fName", "lName"}, {{"John", "Doe"}}), 101, #table({"country", "state"}, {{"CA", "ON"}}), 100000, 50, #table({"frontEnd", "backEnd"}, {{null,   "SQL"}})}, 
      {2, #table({"fName", "lName"}, {{"Jane", "Doe"}}), 102, #table({"country", "state"}, {{"CA", "AB"}}), 200000, 35, #table({"frontEnd", "backEnd"}, {{"HTML", "SQL"}})}
    }
  ),
  FirstRow = Table.First(Source),
  AllCols = List.Union(
    List.Transform(
      Table.ColumnNames(Source),
      each try Table.ColumnNames(Record.Field(FirstRow, _)) otherwise {_}
    )
  ), 
  fn_expandRowToList = (row) =>
    List.Union(
      List.Transform(
        Record.ToList(row), 
        each if _ is table then Record.ToList(Table.First(_)) else {_}
      )
    ), 
  Result = Table.FromRows(Table.TransformRows(Source, fn_expandRowToList), AllCols)
in
  Result

Here's a slightly cleaner equivalent with the source already in record (instead of table) form:

let
  Source = #table(
    {"row", "__name", "id", "location", "salary", "age", "stack"}, 
    {
      {1, [fName="John", lName="Doe"], 101, [country="CA", state="ON"], 100000, 50, [frontEnd=null,   backEnd="SQL"]},
      {2, [fName="Jane", lName="Doe"], 102, [country="CA", state="AB"], 200000, 35, [frontEnd="HTML", backEnd="SQL"]}
    }
  ),
  FirstRow = Table.First(Source),
  AllCols = List.Union(
    List.Transform(
      Table.ColumnNames(Source),
      each try Record.FieldNames(Record.Field(FirstRow, _)) otherwise {_}
    )
  ), 
  fn_expandRowToList = (row) =>
    List.Union(
      List.Transform(
        Record.ToList(row), 
        each if _ is record then Record.ToList(_) else {_}
      )
    ), 
  Result = Table.FromRows(Table.TransformRows(Source, fn_expandRowToList), AllCols)
in
  Result

The logic for expanding the column names and column values is quite similar. Here's how AllCals is calculated:

AllCols
= List.Union(
    List.Transform(
      Table.ColumnNames(Source),
      each try Record.FieldNames(Record.Field(FirstRow, _)) otherwise {_}
    )
  )
= List.Union(
    List.Transform(
      {"row", "__name", "id", "location", "salary", "age", "stack"},
      each try Record.FieldNames(Record.Field(FirstRow, _)) otherwise {_}
    )
  )
= List.Union(
    {
      {"row"},
      Record.FieldNames(Record.Field(FirstRow, "__name")),
              /* = Record.FieldNames(FirstRow[__name])*/
      {"id"},
      Record.FieldNames(Record.Field(FirstRow, "location")),
              /* = Record.FieldNames(FirstRow[location])*/
      {"salary"},
      {"age"},
      Record.FieldNames(Record.Field(FirstRow, "stack"))
              /* = Record.FieldNames(FirstRow[stack])*/
    }
  )
= List.Union(
    {
      {"row"},
      {"fName", "lName"}
      {"id"},
      {"country", "state"},
      {"salary"},
      {"age"},
      {"frontEnd", "backEnd"}
    }
  )
= { "row", "fName", "lName", "id", "country", "state", "salary", "age", "frontEnd", "backEnd" }

The function fn_expandRowToList does nearly the same except that it reads the field values instead of the field names.

fn_expandRowToList(FirstRow)
= List.Union(
      List.Transform(
        Record.ToList(row), 
        each if _ is record then Record.ToList(_) else {_}
      )
    )
= List.Union(
    List.Transform(
      Record.ToList(
        [
          row = 1,
          __name = [fName="John", lName="Doe"],
          id = 102,
          location = [country="CA", state="ON"],
          salary = 100000,
          age = 50,
          stack = [frontEnd=null, backEnd="SQL"]
        ]
      ),
      each if _ is record then Record.ToList(_) else {_}
    )
  )
= List.Union(
    List.Transform(
      {1, [fName="John", lName="Doe"], 102, [country="CA", state="ON"], 100000, 50, [frontEnd=null, backEnd="SQL"]},
      each if _ is record then Record.ToList(_) else {_}
    )
  )
= List.Union(
    {
      {1},
      Record.ToList([fName="John", lName="Doe"]),
      {101},
      Record.ToList([country="CA", state="ON"]),
      {100000},
      {50},
      Record.ToList([frontEnd=null, backEnd="SQL"]),
    }
  )
= {1, "John", "Doe", 101, "CA", "ON", 100000, 50, null, "SQL"}

[wdx223_Daniel · Accepted solution]

= Table.FromRecords(Table.TransformRows(Source,each Record.Combine(List.TransformMany(Table.ToRows(Record.ToTable(_)),each if _{1} is table then Table.ToRecords(_{1}) else {Record.AddField([],_{0},_{1})},(x,y)=>y))))