In this article, We will see how to get employee’s manager from the Dataverse. We have a manager field in the worker entity however it is always blank. Follow below steps to get the manager record from the dataverse.

Entities required

1. cdm_worker
2. cdm_jobposition
3. cdm_positionworkerassignmentmaps

Relationships

Get manager record in Power Platform

Step 1 : Get worker name and personnel number from cdm_worker

Step 2 : Get worker position from cdm_positionworkerassignmentmaps

Step 3 : Now, we have position and get parent position from Cdm_jobposition

Now, we have worker personnel number, worker position and parent position.

Step 4 : Get Manager name based on Parent position.(Manager)

Use cdm_positionworkerassignmentmaps to get worker id based on ParentJobPosition and primary position.

Step 5 : Get (Manager) worker name and (Manager)Worker personnel number based on cdm_worker.

Get manager record in Power BI

Step 1 : Add below entities to Power BI

• cdm_worker
• cdm_jobposition
• cdm_positionworkerassignmentmaps

Step 2 : Create a blank query in Power BI, go to Advance editor, select All and paste the below code.

let
    Source = Table.NestedJoin(cdm_worker, {"cdm_workerid"}, cdm_positionworkerassignmentmap, {"cdm_workerid"}, "cdm_positionworkerassignmentmap", JoinKind.LeftOuter),
    #"Expanded cdm_positionworkerassignmentmap" = Table.ExpandTableColumn(Source, "cdm_positionworkerassignmentmap", {"cdm_jobpositionid", "cdm_positionworkerassignmentmapid", "cdm_workerid"}, {"PWA.cdm_jobpositionid", "PWA.cdm_positionworkerassignmentmapid", "PWA.cdm_workerid"}),
    #"Merged Queries" = Table.NestedJoin(#"Expanded cdm_positionworkerassignmentmap", {"PWA.cdm_jobpositionid"}, cdm_jobposition, {"cdm_jobpositionid"}, "cdm_jobposition", JoinKind.LeftOuter),
    #"Expanded cdm_jobposition" = Table.ExpandTableColumn(#"Merged Queries", "cdm_jobposition", {"cdm_parentjobpositionid"}, {"cdm_parentjobpositionid"}),
    #"Merged Queries1" = Table.NestedJoin(#"Expanded cdm_jobposition", {"cdm_parentjobpositionid"}, cdm_positionworkerassignmentmap, {"cdm_jobpositionid"}, "cdm_positionworkerassignmentmap", JoinKind.LeftOuter),
    #"Expanded cdm_positionworkerassignmentmap1" = Table.ExpandTableColumn(#"Merged Queries1", "cdm_positionworkerassignmentmap", {"cdm_workerid"}, {"cdm_positionworkerassignmentmap.cdm_workerid"}),
    #"Merged Queries2" = Table.NestedJoin(#"Expanded cdm_positionworkerassignmentmap1", {"cdm_positionworkerassignmentmap.cdm_workerid"}, cdm_worker, {"cdm_workerid"}, "cdm_worker", JoinKind.LeftOuter),
    #"Expanded cdm_worker" = Table.ExpandTableColumn(#"Merged Queries2", "cdm_worker", {"cdm_fullname", "cdm_workerid", "cdm_workernumber"}, {"cdm_worker.cdm_fullname", "cdm_worker.cdm_workerid", "cdm_worker.cdm_workernumber"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded cdm_worker",{"cdm_workerid", "PWA.cdm_jobpositionid", "PWA.cdm_positionworkerassignmentmapid", "PWA.cdm_workerid", "cdm_parentjobpositionid", "cdm_positionworkerassignmentmap.cdm_workerid", "cdm_worker.cdm_workerid"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"cdm_worker.cdm_workernumber", "Reports to id"}, {"cdm_worker.cdm_fullname", "Reports to name"}})
in
    #"Renamed Columns"

Step 3 : Once you add above query then you will get below result.

If you like this article, feel free to share it with others who might find it helpful! If you have any questions, feel free to reach out to me.