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

CNENFRNL's avatar
CNENFRNL
Community Champion
5 years ago

Hi, Anonymous , it's totally feasible to debug by yourself by this means,

Create a blank query then using these code in the advanced editor,

let
    recTable = ...,
    columnToExpand = "columnName",
    result = Table.ExpandRecordColumn(
                recTable,
                columnToExpand,
                Table.ColumnNames(
                    Table.FromRecords(
                        List.Select(
                            Table.Column(
                            recTable,
                            columnToExpand
                            ),
                        each _ <> "" and _ <> null
                        )
                    )
                )
    )
in
    result