Reporting on Profile and Account information

As referenced in Profiles vs Accounts of the API documentation, whenever a user ID is referenced in the data models, this refers to the user's id in the profile data model and not the id in the accounts data model.

These fields are not always named profile_id, but more commonly by their role or purpose. For example, approver_id, manager_id, submitted_by, etc.

While these IDs reference the profile model, the useful data is often found in the accounts data model. Therefore, it is routinely necessary to link the two tables. It is recommended that a joined table such as this one is created in most PowerQuery datasets.

Example - Joining Profiles and Accounts

let 
    // get profiles
    ProfileFields = {"id","account_id","user_tier"},
    ProfileDisplayNames = {"Profile Id", "Account Id", "User Tier"},
    ProfileTable = fVitruviPagedQuery("/api/v1/raw/core/profile/", 3000, null, ProfileFields, ProfileDisplayNames),
    ChangedProfileTableTypes = Table.TransformColumnTypes(ProfileTable,{{"Profile Id", Int64.Type}, {"Account Id", Int64.Type}, {"User Tier", type text}}),

    //get accounts
    AccountFields = {"id","last_login","email","first_name","last_name","is_active"},
    AccountDisplayNames = {"Account Id","Last Login","Email","First Name","Last Name","Is Active"},
    AccountTable = fVitruviPagedQuery("/api/v1/raw/core/account/", 3000, null, AccountFields, AccountDisplayNames),
    ChangedAccountTableTypes = Table.TransformColumnTypes(AccountTable,{{"Account Id", Int64.Type}, {"Last Login", type datetime}, {"Email", type text}, {"First Name", type text}, {"Last Name", type text}, {"Is Active", type logical}}),

    //Merge the profiles and accounts
    MergedTable = Table.NestedJoin(ChangedProfileTableTypes, {"Account Id"}, ChangedAccountTableTypes, {"Account Id"}, "Accounts-Joined", JoinKind.LeftOuter),
    ExpandAccountRecord = Table.ExpandTableColumn(MergedTable, "Accounts-Joined", {"Last Login","Email","First Name","Last Name","Is Active"}, {"Last Login","Email","First Name","Last Name","Is Active"}),
    RemoveAccountId = Table.RemoveColumns(ExpandAccountRecord,{"Account Id"}),

    //clean up the view
    AddFullName = Table.AddColumn(RemoveAccountId, "Full Name", each [First Name] & " " & [Last Name], type text),
    Table = Table.ReorderColumns(AddFullName,{"Profile Id", "First Name", "Last Name", "Full Name", "Email", "User Tier", "Last Login", "Is Active"})

in
    Table