Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Table.FromRecords does not show all columns

Hi,   I am trying to build an automated expand all columns for JSON data. I do not want to specify each column name, as this changes depending on the end-point used when getting the JSON data and I...
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Everyone,

     

    I want to thank Jimmy801 & CNENFRNL for your replies...

     

    Just some feedback on the "solution" I figured out. I still believe the Table.FromRecords does not consider ALL the records to determine the columns for the table...

     

    This function seems to work if your records do not contain the same fields for each record:

     

    ExpandAll = (recTable as table, columnToExpand as text) =>
  let
    result = Table.ExpandRecordColumn(
                recTable,
                columnToExpand,
                List.Distinct(
                  Record.FieldNames(
                      Record.Combine(
                        Table.Column(recTable, columnToExpand)
                      )
                  )
                )
    )
  in
    result;

     

     

    The Combine is used to gather all the fields to get all the field names...

     

    I hope this helps someone else 🙂

     

    Kind regards,

    Francois

szekersz's avatar
szekersz
New Member
3 years ago

In the latest version (0.2.3) of Power Query Connector SDK for Visual Studio Code this issue is still present.
We created another of workaround using records.

 

Utils.PaddedTable.FromRecords = (records as list) =>
        let
            fieldNames = List.Distinct(Record.FieldNames(Record.Combine(records))),
            paddedRecords = List.Transform(records, each Record.SelectFields(_, fieldNames, MissingField.UseNull)),
            asTable = Table.FromRecords(paddedRecords)
        in
            asTable,

 I hope this also helps some people. It would also be good if MS would fix this or note it in the documentation.