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

     

jean789's avatar
jean789
New Member
2 years ago

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's avatar
    Blue407
    Frequent Visitor
    2 years ago

    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?