How to dynamically expand columns of a table with condition

= 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))))

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"}