Retrieving employeeID and Department from Active Directory power query

[Anonymous]

Hello,

In connecting with my Active Directory, I can get the User object. However, the attributes this object provides me is not really coherent with what's being used. For example, departmentNumber and employeeNumber exist in the query, but not department and employeeID. Since department is most often the name of the assigned department and is shown on in AD as an important field and employeeID is what Azure AD utilizes I'm somewhat confused why I can't access this data from the AD connector in Power BI.

Is there any way to retrieve this information using the AD connector in Power BI? I've tried just adding this fields to the query, but only get null returned to my table. Please help!

[Anonymous · Accepted solution]

Hello @v-eqin-msft,

The record/object in which I could find employeeID and division was the organizationalPerson and not the user object. This is weird and I've communicated this to Microsoft's support.

[v-eqin-msft]

Hi @Anonymous ,

If you still cannot solve the issue,sorry for that, we do not found an effective solution.

We suggest you go to https://powerbi.microsoft.com/en-us/support/ ,scroll down and click "CREATE SUPPORT TICKET" for further help if you are a pro user.

Best Regards,
Eyelyn Qin

[Anonymous]

Hello @v-eqin-msft ,

Have you been able to try this out yourself? I still have the issue and can't come any further with my Power BI report before I get access to the data. If not, I will have to seek out alternative solutions. 

Thanks for you help!

[v-eqin-msft]

Hi @Anonymous ,

If you could see the columns in AD, then you must be able to see them in Power BI as well.🤔

So please check if you have access to the columns. You may use another account to have a try again.

Best Regards,
Eyelyn Qin

[Anonymous]

Hi,

I can see those fields in AD. Department as a field is presented directly in the AD's UI on the Organization tab. The employeeId field can be found in the Attribute Editor, but is used in Azure AD. See below. Do the fields come up in your AD connector in Power BI?

[Anonymous]

Hello @v-eqin-msft 

No, the fields are not in the list unfortunately. Please see the below screenprint. Also, the values are as they should, i.e. the departmentNumber is empty and employeeNumber is our HR system's record ID. Thus, this must be a configuration in the connector to not include these two fields, which feels very weird to me as the fields are quite important in the AD and AAD environments.

[v-eqin-msft]

Hi @Anonymous ,

1. Could you find department and employeeID in User Record? Please search :

2.For example, departmentNumber and employeeNumber exist in the query, but not department and employeeID. 

If the Name is not matched, how about the Values? 

Refer to:

https://community.powerbi.com/t5/Power-Query/AD-attributes-in-Power-BI-report/td-p/84848

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.