Custom Function to Expand [OwningTeam] and [OwningUser] and Return Owner Name

Question

I am retrieving various types of records such as Accounts and Contacts from Dynamics 365.

These records have two table columns [owningteam] and [owninguser].

I would like to pass these two unexpanded columns to a custom function as parameters.

The custom function would then expand each of these two columns and would return a text column named Owner. The Owner is either the name of the team from the expanded [owningteam] or the fullname of the user from the expanded [owninguser]. Only one of these two columns actually contains a value.

How do I create a function that would receive these table columns and complete these steps? Here is an example of the steps: I want to functionise these steps so that the function can be called for any query I use for retrieving different types of records from Dynamics 365:

#"Expanded (Owning Team)" = Table.ExpandRecordColumn(#"Removed Other Columns", "owningteam", {"name"}, {"owningteam.name"}),
#"Expanded (Owning User)" = Table.ExpandRecordColumn(#"Expanded (Owning Team)", "owninguser", {"fullname"}, {"owninguser.fullname"}),
#"Added Conditional Column" = Table.AddColumn(#"Expanded (Owning User)", "Owner", each if [owningteam.name] <> null then [owningteam.name] else if [owninguser.fullname] <> null then [owninguser.fullname] else null),

Colin Maitland

Answer 1

Hey!
In principle a function can accept parameters of any M data types, but a "column" data type doesn't exist.

The closest that you can do is to either pass the names of the columns as text. But in that case you'd also need the context of the table as another parameter. Effectively, you'd need to create a custom function that would do exactly what you have in those steps, which is the easiest form to do it.

I'd suggest not to do it through a custom function and just do it through the UI as you're currently doing it.

A function would be doing exactly those same steps, but just in one step that you won't be able to modify on a per query basis if you ever need to, so there's that dependency.

Answer 2

Hi @Colin Maitland

Agree with Miguel (if you really want a function let me know though). Now, if you don't need to do anything else than identifying the Owner from your 2 columns of records ([owningteam] & [owninguser]) be aware you don't need to expand them, the following will do it:

= Table.AddColumn(<PreviousStepName>, "Owner", each  
    List.RemoveNulls(  
        {  
            [owningteam][name],  
            [owninguser][fullname]  
        }  
    ){0}?,  
    type text  
)