Forum Discussion

Blue407's avatar
Blue407
Frequent Visitor
2 years ago
Solved

Error in some rows for ExpandRecordColumn with no data

I am working with data coming from Microsoft Graph API, primarily from Entra at the moment. One of the fields is 'Manager' which is actualy a shown as a Record when it first imports the data. When ...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Blue407 ,
    You can handle this situation in Power Query by using the and functions to catch errors and return instead.

    = Table.TransformColumns(
    Table.ExpandRecordColumn(#"Changed Type", "Manager", {"id"}, {"Manager.id"}),
    {"Manager.id", each try _ otherwise null}
)

    This code will attempt to expand the “Manager” column and, if an error occurs (such as when there is no manager assigned), it will replace the error with nulll.
    You can open the advanced editor to replace this code with the previous one, but of course you need to be careful that the contextual references are correct.

    Best regards,
    Albert He

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

     

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Blue407 ,
You can handle this situation in Power Query by using the and functions to catch errors and return instead.

= Table.TransformColumns(
    Table.ExpandRecordColumn(#"Changed Type", "Manager", {"id"}, {"Manager.id"}),
    {"Manager.id", each try _ otherwise null}
)

This code will attempt to expand the “Manager” column and, if an error occurs (such as when there is no manager assigned), it will replace the error with nulll.
You can open the advanced editor to replace this code with the previous one, but of course you need to be careful that the contextual references are correct.

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly