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 do not want to keep on maintaining the query every time something changes...

 

When I expand the JSON data manually, I have to click on the "Load more" button for all the columns to be loaded and shown...

 

When I use the Table.FromRecords command, I get the same result I would get if I DO NOT press the "Load more" button when I convert it manually. This would imply that the FromRecords command limits itself, but I do not have the option to "Load more" in the query.

 

I think this is a bug in the code as it does not create a complete table when using this command.

 

Do you have a work-around for this or can you create a bug for this issue?

 

In my example, doing it manually in the Desktop app, I get more than 200 columns, but using FromRecords I get 22 columns from the same data...

 

Here is the function I created to expand a column automatically:

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

 

Any help would be appreciated, Thank you.

 

Francois Du Toit

  • 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

4 Replies

  • Jimmy801's avatar
    Jimmy801
    Community Champion
    5 years ago

    Hello Anonymous 

     

    your function is written to expand only one Column a time and I can't see any json data. If you really want to expand all columns of a json data, look at this link to find a function that does exactly that

    Here the link 

     

    If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
    Kudoes are nice too

    Have fun

    Jimmy

  • 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

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    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.