Azure AD Graph Refresh

Hi Evogelpohl 

Do you have the URL for your post in Powerbi_com ?

That would be handy 

Many thanks

Jude

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?

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 :mansad:

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", "[email protected]", "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", "[email protected]", "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", "[email protected]", "Column1.usageLocation", "Column1.userPrincipalName", "Column1.userType"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns10", {"Name"})
in
    #"Removed Duplicates"
in
    FNLookupAD