Forum Discussion
Helper VAzure AD Graph Refresh
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.
It won't refresh,at least with use of function as functions are not supported for refresh
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
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?
Power BI TeamWhile 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?
Helper VUnderstand 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
- curth
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"="proxyAddresses/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.