Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi,
I am trying to query active directory as a data source. I have only Azure AD with my automatic "company1.onmicrosoft.com" and my custom domain "company1.uk"
I am using the following "Get Data"
I've tried entering both domains and neither seems to be recognised "domain not recognised"
Unable to Connect: We encountered an error while trying to connect. Details: "Active Directory: The active directory domain 'company1.uk' couldn't be found."
My questions are:
1. Can I query Azure Active Directory using Power BI?
2. How should I correctly enter my active directory domain?
3. Which credential should I use. Will it be the Microsoft account used on the Azure Portal (as opposed to my O365 Work Account).
Many thanks for your help
Solved! Go to Solution.
I would try the graph API: https://msdn.microsoft.com/en-us/library/azure/ad/graph/api/api-catalog
Following @Greg_Deckler's lead I search and found a post which I've ironed out a copy paste issue plus created an additional function and parameterised the lot to try to save any future folk some time.
I'll link to the post that lead me to this solution. Thanks to @curth for the solution and @Evogelpohl for the original question..
My first function calls the users in my domain with MyDomain as an input parameter - it will ask for your Azure AD credentials (NB: my Work Account worked for me as I have Admin rights, didn't need to use Azure Portal Admin credentials or anything like that)
(MyDomain as text) => let Source = Json.Document(Web.Contents("https://graph.windows.net/" & MyDomain & "/users?api-version=1.6")), value = Source[value], ConvertedtoTable = Table.FromList(value, Splitter.SplitByNothing(), null, null, ExtraValues.Error), ExpandedColumn1 = Table.ExpandRecordColumn(ConvertedtoTable, "Column1", {"userPrincipalName", "userType"}), GetUserDetails = Table.AddColumn(ExpandedColumn1, "UserDetails", each if [userType] = "Member" then try fnGetUserDetails([userPrincipalName],MyDomain) otherwise null else null), ExpandUserDetails = Table.ExpandTableColumn(GetUserDetails, "UserDetails", {"City", "Country", "Department", "Name", "Email", "Postal Code", "State", "Street Address"}, {"Custom.City", "Custom.Country", "Custom.Department", "Custom.Name", "Custom.Email", "Custom.Postal Code", "Custom.State", "Custom.Street Address"}) in ExpandUserDetails
My first function calls a second function fnGetUserDetails which takes the userPrincipalName for all users of type 'Member' (and not guest) and returns user details. The fnGetUserDetails is as follows:
let FNLookupAD = (EmailAddress as text,MyDomain as text) => let Source = Json.Document(Web.Contents("https://graph.windows.net/" & MyDomain & "/users?api-version=1.5", [ Query = [#"$filter"="proxyAddresses/any(c:c eq 'smtp:" & EmailAddress & "')"]])), value = Source[value], #"Converted to Table" = Table.FromList(value, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"odata.type", "objectType", "objectId", "deletionTimestamp", "accountEnabled", "assignedLicenses", "assignedPlans", "city", "companyName", "country", "creationType", "department", "dirSyncEnabled", "displayName", "facsimileTelephoneNumber", "givenName", "immutableId", "jobTitle", "lastDirSyncTime", "mail", "mailNickname", "mobile", "onPremisesSecurityIdentifier", "otherMails", "passwordPolicies", "passwordProfile", "physicalDeliveryOfficeName", "postalCode", "preferredLanguage", "provisionedPlans", "provisioningErrors", "proxyAddresses", "sipProxyAddress", "state", "streetAddress", "surname", "telephoneNumber", "thumbnailPhoto@odata.mediaContentType", "usageLocation", "userPrincipalName", "userType"}, {"Column1.odata.type", "Column1.objectType", "Column1.objectId", "Column1.deletionTimestamp", "Column1.accountEnabled", "Column1.assignedLicenses", "Column1.assignedPlans", "Column1.city", "Column1.companyName", "Column1.country", "Column1.creationType", "Column1.department", "Column1.dirSyncEnabled", "Column1.displayName", "Column1.facsimileTelephoneNumber", "Column1.givenName", "Column1.immutableId", "Column1.jobTitle", "Column1.lastDirSyncTime", "Column1.mail", "Column1.mailNickname", "Column1.mobile", "Column1.onPremisesSecurityIdentifier", "Column1.otherMails", "Column1.passwordPolicies", "Column1.passwordProfile", "Column1.physicalDeliveryOfficeName", "Column1.postalCode", "Column1.preferredLanguage", "Column1.provisionedPlans", "Column1.provisioningErrors", "Column1.proxyAddresses", "Column1.sipProxyAddress", "Column1.state", "Column1.streetAddress", "Column1.surname", "Column1.telephoneNumber", "Column1.thumbnailPhoto@odata.mediaContentType", "Column1.usageLocation", "Column1.userPrincipalName", "Column1.userType"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Column1",{"Column1.odata.type", "Column1.objectType", "Column1.objectId", "Column1.deletionTimestamp"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column1.accountEnabled", "Account Enabled"}}), #"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns",{"Column1.assignedLicenses", "Column1.assignedPlans"}), #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns1",{{"Column1.city", "City"}, {"Column1.companyName", "OA-FA"}, {"Column1.country", "Country"}}), #"Removed Columns2" = Table.RemoveColumns(#"Renamed Columns1",{"Column1.creationType"}), #"Renamed Columns2" = Table.RenameColumns(#"Removed Columns2",{{"Column1.department", "Department"}}), #"Removed Columns3" = Table.RemoveColumns(#"Renamed Columns2",{"Column1.dirSyncEnabled"}), #"Renamed Columns3" = Table.RenameColumns(#"Removed Columns3",{{"Column1.displayName", "Name"}}), #"Removed Columns4" = Table.RemoveColumns(#"Renamed Columns3",{"Column1.facsimileTelephoneNumber", "Column1.givenName", "Column1.immutableId"}), #"Renamed Columns4" = Table.RenameColumns(#"Removed Columns4",{{"Column1.jobTitle", "Title"}}), #"Removed Columns5" = Table.RemoveColumns(#"Renamed Columns4",{"Column1.lastDirSyncTime"}), #"Renamed Columns5" = Table.RenameColumns(#"Removed Columns5",{{"Column1.mail", "Email"}}), #"Removed Columns6" = Table.RemoveColumns(#"Renamed Columns5",{"Column1.mailNickname", "Column1.mobile", "Column1.onPremisesSecurityIdentifier"}), #"Expanded Column1.otherMails" = Table.ExpandListColumn(#"Removed Columns6", "Column1.otherMails"), #"Removed Columns7" = Table.RemoveColumns(#"Expanded Column1.otherMails",{"Column1.otherMails", "Column1.passwordPolicies", "Column1.passwordProfile", "Column1.physicalDeliveryOfficeName"}), #"Renamed Columns6" = Table.RenameColumns(#"Removed Columns7",{{"Column1.postalCode", "Postal Code"}}), #"Removed Columns8" = Table.RemoveColumns(#"Renamed Columns6",{"Column1.preferredLanguage", "Column1.provisionedPlans", "Column1.provisioningErrors"}), #"Expanded Column1.proxyAddresses" = Table.ExpandListColumn(#"Removed Columns8", "Column1.proxyAddresses"), #"Removed Columns9" = Table.RemoveColumns(#"Expanded Column1.proxyAddresses",{"Column1.sipProxyAddress"}), #"Renamed Columns7" = Table.RenameColumns(#"Removed Columns9",{{"Column1.state", "State"}, {"Column1.streetAddress", "Street Address"}}), #"Removed Columns10" = Table.RemoveColumns(#"Renamed Columns7",{"Column1.surname", "Column1.telephoneNumber", "Column1.thumbnailPhoto@odata.mediaContentType", "Column1.usageLocation", "Column1.userPrincipalName", "Column1.userType"}), #"Removed Duplicates" = Table.Distinct(#"Removed Columns10", {"Name"}) in #"Removed Duplicates" in FNLookupAD
Thanks to all for your help. Here's a link to @Evogelpohl's original post
Following @Greg_Deckler's lead I search and found a post which I've ironed out a copy paste issue plus created an additional function and parameterised the lot to try to save any future folk some time.
I'll link to the post that lead me to this solution. Thanks to @curth for the solution and @Evogelpohl for the original question..
My first function calls the users in my domain with MyDomain as an input parameter - it will ask for your Azure AD credentials (NB: my Work Account worked for me as I have Admin rights, didn't need to use Azure Portal Admin credentials or anything like that)
(MyDomain as text) => let Source = Json.Document(Web.Contents("https://graph.windows.net/" & MyDomain & "/users?api-version=1.6")), value = Source[value], ConvertedtoTable = Table.FromList(value, Splitter.SplitByNothing(), null, null, ExtraValues.Error), ExpandedColumn1 = Table.ExpandRecordColumn(ConvertedtoTable, "Column1", {"userPrincipalName", "userType"}), GetUserDetails = Table.AddColumn(ExpandedColumn1, "UserDetails", each if [userType] = "Member" then try fnGetUserDetails([userPrincipalName],MyDomain) otherwise null else null), ExpandUserDetails = Table.ExpandTableColumn(GetUserDetails, "UserDetails", {"City", "Country", "Department", "Name", "Email", "Postal Code", "State", "Street Address"}, {"Custom.City", "Custom.Country", "Custom.Department", "Custom.Name", "Custom.Email", "Custom.Postal Code", "Custom.State", "Custom.Street Address"}) in ExpandUserDetails
My first function calls a second function fnGetUserDetails which takes the userPrincipalName for all users of type 'Member' (and not guest) and returns user details. The fnGetUserDetails is as follows:
let FNLookupAD = (EmailAddress as text,MyDomain as text) => let Source = Json.Document(Web.Contents("https://graph.windows.net/" & MyDomain & "/users?api-version=1.5", [ Query = [#"$filter"="proxyAddresses/any(c:c eq 'smtp:" & EmailAddress & "')"]])), value = Source[value], #"Converted to Table" = Table.FromList(value, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"odata.type", "objectType", "objectId", "deletionTimestamp", "accountEnabled", "assignedLicenses", "assignedPlans", "city", "companyName", "country", "creationType", "department", "dirSyncEnabled", "displayName", "facsimileTelephoneNumber", "givenName", "immutableId", "jobTitle", "lastDirSyncTime", "mail", "mailNickname", "mobile", "onPremisesSecurityIdentifier", "otherMails", "passwordPolicies", "passwordProfile", "physicalDeliveryOfficeName", "postalCode", "preferredLanguage", "provisionedPlans", "provisioningErrors", "proxyAddresses", "sipProxyAddress", "state", "streetAddress", "surname", "telephoneNumber", "thumbnailPhoto@odata.mediaContentType", "usageLocation", "userPrincipalName", "userType"}, {"Column1.odata.type", "Column1.objectType", "Column1.objectId", "Column1.deletionTimestamp", "Column1.accountEnabled", "Column1.assignedLicenses", "Column1.assignedPlans", "Column1.city", "Column1.companyName", "Column1.country", "Column1.creationType", "Column1.department", "Column1.dirSyncEnabled", "Column1.displayName", "Column1.facsimileTelephoneNumber", "Column1.givenName", "Column1.immutableId", "Column1.jobTitle", "Column1.lastDirSyncTime", "Column1.mail", "Column1.mailNickname", "Column1.mobile", "Column1.onPremisesSecurityIdentifier", "Column1.otherMails", "Column1.passwordPolicies", "Column1.passwordProfile", "Column1.physicalDeliveryOfficeName", "Column1.postalCode", "Column1.preferredLanguage", "Column1.provisionedPlans", "Column1.provisioningErrors", "Column1.proxyAddresses", "Column1.sipProxyAddress", "Column1.state", "Column1.streetAddress", "Column1.surname", "Column1.telephoneNumber", "Column1.thumbnailPhoto@odata.mediaContentType", "Column1.usageLocation", "Column1.userPrincipalName", "Column1.userType"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Column1",{"Column1.odata.type", "Column1.objectType", "Column1.objectId", "Column1.deletionTimestamp"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column1.accountEnabled", "Account Enabled"}}), #"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns",{"Column1.assignedLicenses", "Column1.assignedPlans"}), #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns1",{{"Column1.city", "City"}, {"Column1.companyName", "OA-FA"}, {"Column1.country", "Country"}}), #"Removed Columns2" = Table.RemoveColumns(#"Renamed Columns1",{"Column1.creationType"}), #"Renamed Columns2" = Table.RenameColumns(#"Removed Columns2",{{"Column1.department", "Department"}}), #"Removed Columns3" = Table.RemoveColumns(#"Renamed Columns2",{"Column1.dirSyncEnabled"}), #"Renamed Columns3" = Table.RenameColumns(#"Removed Columns3",{{"Column1.displayName", "Name"}}), #"Removed Columns4" = Table.RemoveColumns(#"Renamed Columns3",{"Column1.facsimileTelephoneNumber", "Column1.givenName", "Column1.immutableId"}), #"Renamed Columns4" = Table.RenameColumns(#"Removed Columns4",{{"Column1.jobTitle", "Title"}}), #"Removed Columns5" = Table.RemoveColumns(#"Renamed Columns4",{"Column1.lastDirSyncTime"}), #"Renamed Columns5" = Table.RenameColumns(#"Removed Columns5",{{"Column1.mail", "Email"}}), #"Removed Columns6" = Table.RemoveColumns(#"Renamed Columns5",{"Column1.mailNickname", "Column1.mobile", "Column1.onPremisesSecurityIdentifier"}), #"Expanded Column1.otherMails" = Table.ExpandListColumn(#"Removed Columns6", "Column1.otherMails"), #"Removed Columns7" = Table.RemoveColumns(#"Expanded Column1.otherMails",{"Column1.otherMails", "Column1.passwordPolicies", "Column1.passwordProfile", "Column1.physicalDeliveryOfficeName"}), #"Renamed Columns6" = Table.RenameColumns(#"Removed Columns7",{{"Column1.postalCode", "Postal Code"}}), #"Removed Columns8" = Table.RemoveColumns(#"Renamed Columns6",{"Column1.preferredLanguage", "Column1.provisionedPlans", "Column1.provisioningErrors"}), #"Expanded Column1.proxyAddresses" = Table.ExpandListColumn(#"Removed Columns8", "Column1.proxyAddresses"), #"Removed Columns9" = Table.RemoveColumns(#"Expanded Column1.proxyAddresses",{"Column1.sipProxyAddress"}), #"Renamed Columns7" = Table.RenameColumns(#"Removed Columns9",{{"Column1.state", "State"}, {"Column1.streetAddress", "Street Address"}}), #"Removed Columns10" = Table.RemoveColumns(#"Renamed Columns7",{"Column1.surname", "Column1.telephoneNumber", "Column1.thumbnailPhoto@odata.mediaContentType", "Column1.usageLocation", "Column1.userPrincipalName", "Column1.userType"}), #"Removed Duplicates" = Table.Distinct(#"Removed Columns10", {"Name"}) in #"Removed Duplicates" in FNLookupAD
Thanks to all for your help. Here's a link to @Evogelpohl's original post
Hey @Brian_M
Thanks for sharing the code. It works nicely but i have an issue, it returns only first 100 users (same issue as @no_one_but_me ). Tried to use parameter $top but its seems it is not accepted in current version of API. Do you maybe any idea how it can be passed or show all users in Tenant (domain)?
@no_one_but_me Maybe you found a solution to this one, if yes can you please explain how you solved it?
@Greg_Deckler do you have any additional advice about parameters that we can pass in this query?
Patryk
Is it possible to export info about managers for all users?
Hey guys,
I have used the same query to get the user details but that is loading only the top 100 user details I don't know why
Can you help me with this?
Can I get the hierarchical structure from the Active Directory from power bi with the help of this code
If yes, Please help me
Thanks in advance
Please kindly help with this
or if I can get the "reports to" info from the above query
Hi Brian:
Is there a way to get the user email from the logged user and passed to your fnGetUserDetails function? With DAX, by using
Well, I found the answer to my question: If you want to get all the AAD information of the logged user, us the graph interface :
https://graph.windows.net/{domain}/me?api-version=1.6
Here you can find more info https://docs.microsoft.com/en-us/graph/api/user-get?view=graph-rest-1.0&tabs=http . Unfortunately, as usual, though it says that is updated, is not. Is version 1.0
Hi, thanks to @Brian_M for his thorough answer. If I copy paste this it works by collecting AD data from the person entered as parameter in Power Query. But how do you get information for the current logged on user? I see that @joseabarriga has solved this and I tried to understand the information in the link but I get stuck.
I want:
1. Get a list of all AD users with e-mail, city and department
or of this can't be done:
2. Get e-mail, city and department from only the logged on user
Thank you in advance!
What I did was use the Microsoft Graph REST API described in https://docs.microsoft.com/en-us/graph/api/user-get?view=graph-rest-1.0&tabs=http .
If you want all the data from all the users in your domain, the command is:
https://graph.windows.net/< your domain>/users?api-version=1.6
For just the logged user will be
https://graph.windows.net/< your domain>/me?api-version=1.6
To test it I suggested using PostMan
https://docs.microsoft.com/en-us/graph/use-postman?context=graph%2Fapi%2Fbeta&view=graph-rest-beta
Finally, be aware the there are two API with the same results which are the Azure AD Graph and Microsoft Graph ( Just that) ... take a look at https://docs.microsoft.com/en-us/graph/migrate-azure-ad-graph-resource-differences
this to me means that the Active Directory in Get Data is really misleading because it doesnt work. is that the case?
Because I have the same issue and I dont want to use Graph because I should be able to use the connector as stated in power BI?
However, I used the Primary Domain I found in Azure Active Directory. I used
Hi @Brian_M,
I am trying to connect to AAD from Power BI Desktop, but am hoping this can be done through the Desktop UI as I have no expertise in making calls to the Graph API.
I am getting the same message you were "The active directory domain x couldn't be found" (where x is the onmicrosoft domain you are given when Power BI is the only thing you're running off AAD). (See attached.) I've tried other domains we have, but definitely the one I would expect to work is not found.
Any suggestions?
Thanks,
Ben
Hi,
I'm looking for the same thing i.e. get data from Azure AD. For business needed, I've build a report with data from our timesheeting tool. A big plus would be to be able to create a filter on departments. As this notion of departments already exist in AAD, I would like to use the AAD data to avoid having a static table in my report that would need updates each time a person leave or join us.
Do you have any info on how to get data from AAD and store them in a datawarehouse ?
Thanks,
Julien.
Hi,
did you somehow solve this issue? I'm looking for exporting Users, Groups and their Memberships for some custom security scenario.
The solution might be a combination of the M code provided above together with Data Flows storing the data in a text format in the CDM folders as part of your Data Lake. For me that would be just perfect...
Thanks,
Thomas
I would try the graph API: https://msdn.microsoft.com/en-us/library/azure/ad/graph/api/api-catalog
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
114 | |
95 | |
90 | |
35 | |
35 |
User | Count |
---|---|
154 | |
102 | |
82 | |
64 | |
54 |