title: Powerquery: Expand all columns of that have records in them
body: <p>Using Power Query in Microsoft Excel 2013, I created a table that looks like this:</p>
  
  <pre><code>// To insert this in Power Query, append a '=' before the 'Table.FromRows'
  Table.FromRows(
    {
      {"0", "Tom", "null", "null"},
      {"1", "Bob", [ name="Berlin" , street="BarStreet" ], [ name="Mary", age=25 ]},
      {"2", "Jim", [ name="Hamburg", street="FooStreet" ], [ name="Marta", age=30 ]}
    },
    {"ID", "Name", "Address", "Wife"}
  )
  </code></pre>
  
  <p>Now, I want to expand the columns <code>Address</code> and <code>Wife</code> by using the <code>name</code> attribute
  on both records. Manually, I would do it like this:</p>
  
  <pre><code>// To insert this in Power Query, append a '=' before the 'Table.FromRows'
  let
    t = Table.FromRows(
      {
        {"0", "Tom", "null", "null"},
        {"1", "Bob", [ name="Berlin" , street="BarStreet" ], [ name="Mary", age=25 ]},
        {"2", "Jim", [ name="Hamburg", street="FooStreet" ], [ name="Marta", age=30 ]}
      },
      {"ID", "Name", "Address", "Wife"}
    ),
    expAddress = Table.ExpandRecordColumn(t, "Address", {"name"}, {"Address → name"}),
    expWife = Table.ExpandRecordColumn(expAddress, "Wife", {"name"}, {"Wife → name"})
  in
    expWife
  </code></pre>
  
  <h2>Background</h2>
  
  <p>Whenever I have data tables that have a different layout, I need to rewrite the
  query. In a fantasy world, you could expand all columns that have Records in
  them using a specific key. <strong>Ideally, you would have the following library
  functions:</strong></p>
  
  <pre><code>// Returns a list with the names of the columns that match the secified type.
  // Will also try to infer the type of a column if the table is untyped.
  Table.ColumnsOfTypeInfer(
    table as table,
    listOfTypes as list
  ) as list
  
  // Expands a column of records into columns with each of the values.
  Table.ExpandRecordColumnByKey(
    table as table,
    columns as list,
    key as text,
  ) as table
  </code></pre>
  
  <p>Then, I could call</p>
  
  <pre><code>// To insert this in Power Query, append a '=' before the 'Table.FromRows'
  let
    t = Table.FromRows(
      {
        {"0", "Tom", "null", "null"},
        {"1", "Bob", [ name="Berlin" , street="BarStreet" ], [ name="Mary", age=25 ]},
        {"2", "Jim", [ name="Hamburg", street="FooStreet" ], [ name="Marta", age=30 ]}
      },
      {"ID", "Name", "Address", "Wife"}
    ),
    recordColumns = Table.ColumnsOfTypeInfer(t, {type record}),
    expAll = Table.ExpandRecordColumnByKey(t, recordColumns, "name")
  in
    expAll
  </code></pre>
  
  <h2>Question</h2>
  
  <ol>
  <li>Can you get a list of columns with a specific type that is not specified in the table, aka infer it?</li>
  <li>Can you make that record expansion generic?</li>
  </ol>
  
  <h2>Edit: Added row #0 with two null cells.</h2>