Issue : Unable to run the dataset refresh.
Description : Created the report by using power query interfacing to Power BI with MS graph calls in Power Query.
However, was able refresh when using Power BI desktop, but unable to refresh after loading into Microsoft Fabric portal.
Got message that dynamic data sources can't be refreshed in the Power BI service. However, have overcame that have some code changes.
For example, for Get UserDetails, issue resolved with code changed.
However for Account Created and Account Deleted below, have not been able to overcome issue with code changes.
Might not have been changing it correctly.
Hope to seek advice for Account Created and Account Deleted to overcome issue. Thanks
Would like to seek advice
[ Get UserDetails -Before Code Change]
let
resource="https://graph.microsoft.com",
tokenResponse = Json.Document(Web.Contents("https://login.windows.net/",
[
RelativePath = #"Azure AD Tenant ID" & "/oauth2/token",
Content = Text.ToBinary(Uri.BuildQueryString(
[
client_id = #"Azure Application Client ID",
resource = resource,
grant_type = "client_credentials",
client_secret = #"Azure Application Client Secret"
]
)),
Headers = [Accept = "application/json"], ManualStatusHandling = {400}
])),
access_token = tokenResponse[access_token],
Source = OData.Feed("https://graph.microsoft.com/v1.0/users?$select=assignedLicenses, assignedPlans, userPrincipalName, department, accountEnabled", [ Authorization = "Bearer " & access_token ], [ ExcludedFromCacheKey = {"Authorization"}, ODataVersion = 4, Implementation = "2.0" ])
in
Source
[ Get UserDetails - After Code Change]
let
resource = "https://graph.microsoft.com",
tenantId = #"Azure AD Tenant ID",
clientId = #"Azure Application Client ID",
clientSecret = #"Azure Application Client Secret",
tokenUrl = "https://login.microsoftonline.com/" & tenantId & "/oauth2/token",
tokenResponse = Json.Document(
Web.Contents(tokenUrl, [
Content = Text.ToBinary(
Uri.BuildQueryString([
client_id = clientId,
resource = resource,
grant_type = "client_credentials",
client_secret = clientSecret
])
),
Headers = [#"Content-Type" = "application/x-www-form-urlencoded"],
ManualStatusHandling = {400}
])
),
access_token = tokenResponse[access_token],
headers = [
Accept = "application/json",
Authorization = "Bearer " & access_token
],
Source = OData.Feed(
"https://graph.microsoft.com/v1.0/users?$select=assignedLicenses,assignedPlans,userPrincipalName,depa...",
headers,
[
ExcludedFromCacheKey = {"Authorization"},
ODataVersion = 4,
Implementation = "2.0"
]
)
in
Source
[ For Account Created ]
let
resource = "https://graph.microsoft.com",
// Get the current date and time in UTC
currentDateTimeUTC = DateTimeZone.UtcNow(),
// Convert current date and time to Singapore time
currentDateTimeSGT = DateTimeZone.SwitchZone(currentDateTimeUTC, 8),
// Get the first day of the current month in Singapore time
firstDayOfCurrentMonthSGT = Date.StartOfMonth(DateTimeZone.SwitchZone(currentDateTimeSGT, 8)),
// Get the first day of the previous month in Singapore time
firstDayOfPreviousMonthSGT = Date.StartOfMonth(Date.AddMonths(firstDayOfCurrentMonthSGT, -1)),
// Convert the first day of the previous month from Singapore time to UTC
firstDayOfPreviousMonthUTC = DateTimeZone.SwitchZone(firstDayOfPreviousMonthSGT, 0),
// Get the last day of the current month in Singapore time
lastDayOfCurrentMonthSGT = Date.EndOfMonth(DateTimeZone.SwitchZone(currentDateTimeSGT, 8)),
// Convert the last day of the current month from Singapore time to UTC
lastDayOfCurrentMonthUTC = DateTimeZone.SwitchZone(lastDayOfCurrentMonthSGT, 0),
tokenResponse = Json.Document(Web.Contents("https://login.windows.net/",
[
RelativePath = #"Azure AD Tenant ID" & "/oauth2/token",
Content = Text.ToBinary(Uri.BuildQueryString(
[
client_id = #"Azure Application Client ID",
resource = resource,
grant_type = "client_credentials",
client_secret = #"Azure Application Client Secret"
]
)),
Headers = [Accept = "application/json"], ManualStatusHandling = {400}
])),
access_token = tokenResponse[access_token],
// Construct the filter based on the first day of the previous month and the last day of the current month
filter = "createdDateTime ge " & Text.From(DateTimeZone.ToText(firstDayOfPreviousMonthUTC, "yyyy-MM-ddT16:00:00Z")) & " and createdDateTime le " & Text.From(DateTimeZone.ToText(lastDayOfCurrentMonthUTC, "yyyy-MM-ddT15:59:59Z")),
// Query with dynamic filter
Source = OData.Feed("https://graph.microsoft.com/v1.0/users?$filter=" & filter & "&$select=department,createdDateTime", [ Authorization = "Bearer " & access_token ], [ ExcludedFromCacheKey = {"Authorization"}, ODataVersion = 4, Implementation = "2.0" ])
in
Source
[ For Account Deleted ]
let
resource = "https://graph.microsoft.com",
// Get the current date and time in UTC
currentDateTimeUTC = DateTimeZone.UtcNow(),
// Convert current date and time to Singapore time
currentDateTimeSGT = DateTimeZone.SwitchZone(currentDateTimeUTC, 8),
// Get the first day of the current month in Singapore time
firstDayOfCurrentMonthSGT = Date.StartOfMonth(DateTimeZone.SwitchZone(currentDateTimeSGT, 8)),
// Get the first day of the previous month in Singapore time
firstDayOfPreviousMonthSGT = Date.StartOfMonth(Date.AddMonths(firstDayOfCurrentMonthSGT, -1)),
// Convert the first day of the previous month from Singapore time to UTC
firstDayOfPreviousMonthUTC = DateTimeZone.SwitchZone(firstDayOfPreviousMonthSGT, 0),
// Get the last day of the current month in Singapore time
lastDayOfCurrentMonthSGT = Date.EndOfMonth(DateTimeZone.SwitchZone(currentDateTimeSGT, 8)),
// Convert the last day of the current month from Singapore time to UTC
lastDayOfCurrentMonthUTC = DateTimeZone.SwitchZone(lastDayOfCurrentMonthSGT, 0),
tokenResponse = Json.Document(Web.Contents("https://login.windows.net/",
[
RelativePath = #"Azure AD Tenant ID" & "/oauth2/token",
Content = Text.ToBinary(Uri.BuildQueryString(
[
client_id = #"Azure Application Client ID",
resource = resource,
grant_type = "client_credentials",
client_secret = #"Azure Application Client Secret"
]
)),
Headers = [Accept = "application/json"], ManualStatusHandling = {400}
])),
access_token = tokenResponse[access_token],
// Construct the filter based on the first day of the previous month and the last day of the current month
filter = "deletedDateTime ge " & Text.From(DateTimeZone.ToText(firstDayOfPreviousMonthUTC, "yyyy-MM-ddT16:00:00Z")) & " and deletedDateTime le " & Text.From(DateTimeZone.ToText(lastDayOfCurrentMonthUTC, "yyyy-MM-ddT15:59:59Z")),
// Query with dynamic filter
Source = OData.Feed("https://graph.microsoft.com/v1.0/directory/deletedItems/microsoft.graph.user?$filter=" & filter & "&$select=department,deletedDateTime", [ Authorization = "Bearer " & access_token ], [ ExcludedFromCacheKey = {"Authorization"}, ODataVersion = 4, Implementation = "2.0" ])
in
Source
