Error in some rows for ExpandRecordColumn with no data

[Blue407]

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 I select the option to expand this in PowerQuery, it provides this code:

= Table.ExpandRecordColumn(#"Changed Type", "Manager", {"id"}, {"Manager.id"})

Where there is a Manager assigned in Entra, the Entra Id for the Manager is returned, where there isn't it returns 'Error'.

Is there a way to check and then return Null or Blank instead of Error, where there is no Manager?

I suspect I may get the same issue with some of the other fields that contain 'Tables'.

[Anonymous · Accepted solution]

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

[jean789]

Hello,

Add a Custom Column: Create a custom column that checks if the ‘Manager’ field is null.
Expand the Record: Expand the ‘Manager’ field only if it is not null.  Mypascoconnect 
Here’s an example of how you can modify your PowerQuery M code:

let
Source = ...,
#"Changed Type" = ...,
// Add a custom column to check for null
#"Added Custom" = Table.AddColumn(#"Changed Type", "ManagerCheck", each if [Manager] = null then null else [Manager]),
// Expand the 'Manager' column
#"Expanded Manager" = Table.ExpandRecordColumn(#"Added Custom", "ManagerCheck", {"id"}, {"Manager.id"})
in
#"Expanded Manager"

In this code:

The #"Added Custom" step adds a new column ManagerCheck that contains the ‘Manager’ record if it exists, or null if it doesn’t.
The #"Expanded Manager" step expands the ManagerCheck column instead of the original ‘Manager’ column.

Best Regards

jean789

[Blue407]

Thankyou, that looks helpful.

However, as I am new to Power Query, I am not understanding where to put this. I tried a couple of ways but get an error.

Currently I have Applied Steps showing 'Source', within this is = #"Entra ID Users Data Import"

Where do I add this code for it to work correctly?