Azure AD Graph Refresh

[Evogelpohl]

Created a PowerBI Desktop solution that gets data from Azure AD via the Graph API.  Worked flawlessly on the desktop.

Here's the first line of the query:

Source = Json.Document(Web.Contents("https://graph.windows.net/domain.com/users?api-version=1.5")

Posted to PowerBI.com - won't refresh.  Says source is not supported.

What's wrong with a simple REST query?  Why doesn't PowerBI support this call?  Doing something wrong?

-Eric.

[curth · Accepted solution]

Yeah :(.

When you run this inside Power Query or PBI Desktop, you either have to enable "Fast Combine" or you need to set privacy levels for the two sources. Currently, all sources inside refresh are marked as "Private" with no ability to change this configuration or set "Fast Combine". This prevents the query from working correctly. This was picked as a default to maximize security without asking the user hard-to-explain questions, but it's obviously quite limiting in the kinds of queries that can be performed -- so we need to figure out how best to enable this scenario for refresh.

[konstantinos · Accepted solution]

It won't refresh,at least with use of function as functions are not supported for refresh

http://community.powerbi.com/t5/Integrations-with-Files-and/Refreshing-queries-with-functions-doens-...

Not sure if will refresh as is not supported but you can try without the function - only one email to see if APIs will refresh

I think yes "from web"= Web.Contents

[apods1979]

Good afternoon!!!
I saw many websites, chats and posts, but few are objective and each person values his method as if it were unique !!!
Each has its logic, so there is no single method !!
Objectively, for me ...
It worked this way:

Enjoy: 😘

===========================
let
tenantId = "12345678-9012-3456-7890-123456789012",
clientId = "abcdefgh-9012-3456-7890-123456789012",
clientSecret = "1234567890qwertyuiopasdf",
grantType = "client_credentials",
resource = "https://graph.windows.net/",
endpointUsers = "/users",
apiVersion = "?api-version=1.6",

baseURL="https://login.microsoftonline.com",
relativePath = "/"&tenantId&"/oauth2/token",
urlToken = baseURL & relativePath,

body = [client_id=clientId,
grant_type=grantType,
client_secret=clientSecret,
resource=resource],

//Access
Source = Json.Document(Web.Contents(urlToken, [Headers=[#"Accept" = "application/json", #"Content-Type"="application/x-www-form-urlencoded;charset=UTF-8"],Content = Text.ToBinary(Uri.BuildQueryString(body))])),
accessToken = Source[access_token],
tokenType = Source[token_type],
fullAccessToken = tokenType&" "&accessToken,

//Get Users
SourceUsers = OData.Feed(resource & tenantId & endpointUsers, [Authorization=fullAccessToken], [Implementation="2.0", Query=[#"api-version"="1.6"]]),

Step1 = Table.RemoveColumns(SourceUsers,{"createdObjects", "department", "employeeId", "facsimileTelephoneNumber", "legalAgeGroupClassification", "jobTitle", "telephoneNumber", "mobile", "givenName", "physicalDeliveryOfficeName", "consentProvidedForMinor", "sipProxyAddress", "streetAddress", "city", "state", "country", "postalCode", "ageGroup", "companyName", "preferredLanguage", "manager", "directReports", "members", "transitiveMembers", "owners", "ownedObjects", "appRoleAssignments", "licenseDetails", "oauth2PermissionGrants", "ownedDevices", "registeredDevices", "assignedLicenses", "assignedPlans", "deletionTimestamp", "immutableId", "isCompromised", "lastDirSyncTime", "passwordProfile", "provisionedPlans", "provisioningErrors", "proxyAddresses", "signInNames", "usageLocation", "userIdentities", "memberOf", "transitiveMemberOf", "thumbnailPhoto", "createdOnBehalfOf", "dirSyncEnabled", "onPremisesDistinguishedName", "onPremisesSecurityIdentifier", "showInAddressList"}),
Step2 = Table.TransformColumnTypes(Step1,{{"createdDateTime", type datetime},{"refreshTokensValidFromDateTime", type datetime}}),
Step3 = Table.TransformColumns(Step2, {"otherMails", each Text.Combine(List.Transform(_, Text.From)), type text})
in
Step3

[Evogelpohl]

Thanks Curt. I finally got back to this today. I retyped your example and got it to work. I posted to PowerBI.com and it is successfully refreshing. Thanks again.

[jude_moore]

Hi Evogelpohl 

Do you have the URL for your post in Powerbi_com ?

That would be handy 

Many thanks

Jude

[curth]

While I think this isn't officially supported, I know of no reason why it wouldn't work. What's the rest of the query doing?

[Evogelpohl]

Understand the list of supported item, but - for some reason - i thought generic WEB source calls worked.

Here's what I'm doing:

1) Get the SaleForce.Com Object = User Profile (a list of all our SF.com users)

2) Using the 1.5 version of the Azure AD Graph API - Connect to my company's AD-Directory (I want to get metadata for each user)

3) Using the Add Custom Column ability of Power Query - For Each SalesForce.com user's email address, lookup in AD Graph their Company, Dept & Location (Using this: Add Custom Column, 'try FnADUserLookup([Email])'

This BTW works flawlessly in Excel & the Designer.  Just won't refresh on PowerBI.com

Here's the M-code of the Function i wrote. 

let
    FNLookupAD = (EmailAddress as text) =>

let
    Source = Json.Document(Web.Contents("https://graph.windows.net/<mydomain>.com/users?api-version=1.5&$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

[curth · Accepted solution]

Yeah :(.

When you run this inside Power Query or PBI Desktop, you either have to enable "Fast Combine" or you need to set privacy levels for the two sources. Currently, all sources inside refresh are marked as "Private" with no ability to change this configuration or set "Fast Combine". This prevents the query from working correctly. This was picked as a default to maximize security without asking the user hard-to-explain questions, but it's obviously quite limiting in the kinds of queries that can be performed -- so we need to figure out how best to enable this scenario for refresh.

[Evogelpohl]

@curth.  Ahh.  Gotcha.  Not a source issue, but a function issue.

This stinks...  We have hundreds of SalesForce.com users and I need to mesh up AD data to demonstrate usage-profiling for senior managment.

I tried a PowerPivot approach - however, the Azure AD Graph API seems to limit bulk exports to 100 records.  I don't know how to paginate with that API and if i did, I'd probably use a function to loop through a hidden table that has values of 1-99, 100-199, 200.. and so on in PowerQuery.  But that would work either since it's a function (right?). 

Our regular/internal Active Directory would work fine, but that source isn't supported either.

Oh well.  Thanks for the consultaton.

Eric Vogelpohl

[curth]

@Evogelpohl, we're working to enable various scenarios as fast as we can. I don't know if this particular issue is represented yet in UserVoice; feel free to add it and encourage others to upvote it there.

To @konstantinos' point, the function is also an issue -- but in this case, we can rewrite the query slightly to fix that problem:

let

  FNLookupAD = (EmailAddress as text) =>

    let

      Source = Json.Document(Web.Contents("https://graph.windows.​net/<mydomain>.com/users?api-version=1.5", [

      Query=[#"$filter"="p​roxyAddresses/any(c:c eq 'smtp:" & EmailAddress & "')"]])),

      value = Source[value],

This works because the URL is now a literal that's not dependent on the parameter.

[Evogelpohl]

@curth Thanks.  I replace the first few lines of my Fx with yours.  I see you're using the query option syntax.  However, when i try to invoke that function (just as a test) - I'm getting an error.  I did make sure to replace the <mydomain> with my actual domain.com. 

I get: DataSource.Error: The remote name could not be resolved: 'graph.windows.​net'.  I tried in Excel PQ and PBID.

Odd too.  I was prompted to re-enter my creds for Azure AD.  Previously, I had success using the Organizational Account method, but that too fails with this new approach.  I had to enter 'basic'.  Perhaps this is why it's failing.

Thanks again for your help.

[curth]

That seems odd, and unlikely to be related. If you change back to the earlier query, it starts working again?

[Evogelpohl]

Yes.  The original one works.  And a naked/simple: Source = Json.Document(Web.Contents("https://graph.windows.net/DOMAIN.com/users?api-version=1.5") - works as well, it returns 100 users.

I'm getting a garbled error response in PowerQuery.  It's as if the URL that it's piecing together is mixed with bad characters.  You can see the error.. below.  The "P" in ProxyAddresses is separated by a bunch of odd reserve-characters.  Could we have a syntax problem?  Too many ""-marks perhaps?

I'm getting this error in the upper PQ message bar:  

DataSource.Error: Web.Contents failed to get contents from 'https://graph.windows.net/DOMAIN.com/users?%24filter=p%E2%80%8BroxyAddresses%2Fany%28c%3Ac%20eq%20%27smtp%3Aeric.vogelpohl%40DOMAIN.com%27%29&api-version=1.5' (400): Bad Request.

Here's the full M-code.  Your query-option lines plus the rest of my query.  If you have an Azure AD account and replace DOMAIN with your tenant, does it work? 

let

  FNLookupAD = (EmailAddress as text) =>

    let

      Source = Json.Document(Web.Contents("https://graph.windows.net/DOMAIN.com/users?api-version=1.5", [

      Query=[#"$filter"="p​roxyAddresses/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

[curth · Accepted solution]

This is probably my fault for pasting colorized text into the editor. %E2%80%8B is the UTF-8 for an unbreakable space. Can you just retype the line instead of copying and pasting it?

[Greg_Deckler]

Not on list of supported data sources for refresh.

https://support.powerbi.com/knowledgebase/articles/474669-refresh-data-in-power-bi

[Evogelpohl]

@Greg_Deckler On the URL you posted, it says "Web" under online sources supported for refresh.  I'm using the Web.Contents source.  Isn't that the same thing?  If not, what is the difference?

[konstantinos · Accepted solution]

It won't refresh,at least with use of function as functions are not supported for refresh

http://community.powerbi.com/t5/Integrations-with-Files-and/Refreshing-queries-with-functions-doens-...

Not sure if will refresh as is not supported but you can try without the function - only one email to see if APIs will refresh

I think yes "from web"= Web.Contents