Scheduled refresh blocked by “Data source for Query1” (no such query in model) — Power BI Pro

Hi V-Menakakota,

Thanks for the quick response. I’ve pasted the sanitized version of my Power Query below, no confidential information included. This is the main query I’m using. I appreciate your help!

let
// ===================== SETTINGS =====================
GroupDisplayName = "***YOUR GROUP NAME***",

// ===================== GRAPH HELPERS (service-safe) =====================
// Always use literal host + RelativePath/Query so it refreshes in the Service
GraphGet = (rel as text, optional q as nullable record) as record =>
let
base = [RelativePath = "v1.0/" & rel, Headers = [Accept = "application/json" /* , Authorization = "Bearer ***" (NOT USED; relying on connector auth) */]],
opts = if q = null then base else base & [Query = q],
res = Json.Document(Web.Contents("https://graph.microsoft.com", opts))
in
res,

// Add a key/value to an optional query-record
QAdd = (q as nullable record, key as text, value as any) as record =>
if q = null then Record.FromList({value}, {key}) else Record.AddField(q, key, value),

// List loader with $skiptoken paging (no dynamic URLs)
GraphList = (rel as text, optional q as nullable record) as list =>
let
First = GraphGet(rel, q),

GetToken = (link as any) as nullable text =>
let lk = try link otherwise null in
if lk = null then null else
let
parts = Uri.Parts(Text.From(lk)),
qrec = Record.FieldOrDefault(parts, "Query", null),
tok = if qrec <> null and Record.HasFields(qrec, "$skiptoken")
then Record.Field(qrec, "$skiptoken") // avoid invalid identifier
else null
in
tok,

Values =
List.Generate(
() => [pg = First, out = if Record.HasFields(First, "value") then First[value] else {}],
each [out] <> {},
each
let
tok = GetToken( try [pg][#"@odata.nextLink"] otherwise null ),
nextPg = if tok <> null then GraphGet(rel, QAdd(q, "$skiptoken", tok)) else null,
nextOut = if nextPg = null then {} else if Record.HasFields(nextPg, "value") then nextPg[value] else {}
in
[pg = nextPg, out = nextOut],
each [out]
),

Combined = List.Combine(Values)
in
Combined,

// Safe expand helper (treat nulls/non-records as empty records)
SafeExpand = (tbl as table, col as text, fields as list, newNames as list) as table =>
let
withRec = Table.TransformColumns(tbl, {{col, each if _ is record then _ else [], type record}}),
exp = Table.ExpandRecordColumn(withRec, col, fields, newNames)
in
exp,

// ===================== 1) GROUP =====================
Groups = GraphList(
"groups",
Record.FromList({ "displayName eq '" & GroupDisplayName & "'" }, {"$filter"})
),
GroupId = if List.Count(Groups) > 0 then Groups{0}[id] else error "Group '" & GroupDisplayName & "' not found or access denied.",

// ===================== 2) PLANS =====================
PlansList = GraphList("groups/" & GroupId & "/planner/plans"),
PlansTable = Table.FromList(PlansList, Splitter.SplitByNothing(), {"rec"}),
PlansExp = Table.ExpandRecordColumn(
PlansTable, "rec",
{"@odata.etag","createdDateTime","owner","title","id","createdBy","container"},
{"plan.etag","plan.createdDateTime","plan.owner","PlanName","PlanId","plan.createdBy","plan.container"}),

ExpandCB = SafeExpand(PlansExp, "plan.createdBy", {"user","application"}, {"createdBy.user","createdBy.application"}),
ExpandCBUser = SafeExpand(ExpandCB, "createdBy.user", {"displayName","id"}, {"createdBy.user.displayName","createdBy.user.id"}),
ExpandCBApp = SafeExpand(ExpandCBUser, "createdBy.application", {"displayName","id"}, {"createdBy.application.displayName","createdBy.application.id"}),
ExpandCont = SafeExpand(ExpandCBApp, "plan.container", {"containerId","type","url"}, {"container.containerId","container.type","container.url"}),

TypedPlans = Table.TransformColumnTypes(
ExpandCont,
{
{"plan.etag", type text}, {"plan.createdDateTime", type datetimezone},
{"plan.owner", type text}, {"PlanName", type text}, {"PlanId", type text},
{"createdBy.user.displayName", type text}, {"createdBy.user.id", type text},
{"createdBy.application.displayName", type text}, {"createdBy.application.id", type text},
{"container.containerId", type text}, {"container.type", type text}, {"container.url", type text}
}),

// ===================== 3) TASKS PER PLAN =====================
AddTasks = Table.AddColumn(TypedPlans, "TasksList", each GraphList("planner/plans/" & [PlanId] & "/tasks")),
ExpandTasks= Table.ExpandListColumn(AddTasks, "TasksList"),
TaskCols = Table.ExpandRecordColumn(
ExpandTasks, "TasksList",
{"planId","bucketId","title","startDateTime","createdDateTime","dueDateTime","completedDateTime","priority","percentComplete","id","assignments"},
{"Tasks.planId","Tasks.bucketId","TaskTitle","Tasks.startDateTime","Tasks.createdDateTime","Tasks.dueDateTime","Tasks.completedDateTime","Tasks.priority","Tasks.percentComplete","TaskId","Tasks.assignments"}),

// ===================== 4) BUCKET NAMES =====================
AddBucketsList = Table.AddColumn(TaskCols, "BucketsList", each GraphList("planner/plans/" & [PlanId] & "/buckets")),
AddBucketName = Table.AddColumn(
AddBucketsList, "BucketName",
each let
bid = [Tasks.bucketId],
match = List.First(List.Select([BucketsList], (x) => Record.FieldOrDefault(x, "id", null) = bid), null)
in
if match = null then null else Record.FieldOrDefault(match, "name", null),
type text),
DropBuckets = Table.RemoveColumns(AddBucketName, {"BucketsList"}),

// ===================== 5) TYPES =====================
TypedTasks = Table.TransformColumnTypes(
DropBuckets,
{
{"Tasks.startDateTime", type datetimezone},
{"Tasks.createdDateTime", type datetimezone},
{"Tasks.dueDateTime", type datetimezone},
{"Tasks.completedDateTime", type datetimezone},
{"Tasks.priority", Int64.Type},
{"Tasks.percentComplete", Int64.Type}
}),

// ===================== 6) ASSIGNEE NAMES (from exportUsers) =====================
// NOTE: 'exportUsers' is a separate query/table; I did NOT include its raw contents here to avoid sharing names/emails.
UsersSafe = try exportUsers otherwise #table({"Column6","Column2"}, {}),
UsersNorm = Table.TransformColumns(
UsersSafe,
{
{"Column6", each Text.Upper(Text.Trim(Text.From(_))), type text},
{"Column2", each Text.From(_), type text}
}),
AddAssigneeName = Table.AddColumn(
TypedTasks, "AssigneeName",
each let
ids = if [Tasks.assignments] = null then {} else Record.FieldNames([Tasks.assignments]),
idsClean = List.Transform(ids, each Text.Upper(Text.Trim(Text.From(_)))),
names = List.RemoveNulls(
List.Transform(idsClean, (k) =>
let pos = List.PositionOf(UsersNorm[Column6], k)
in if pos >= 0 then UsersNorm[Column2]{pos} else null))
in if List.Count(names) > 0 then Text.Combine(List.Distinct(names), ", ") else "Not Assigned",
type text),

// ===================== 7) STATUS =====================
AddStatus = Table.AddColumn(
AddAssigneeName, "status",
each let p = try [Tasks.percentComplete] otherwise null,
c = [Tasks.completedDateTime]
in if (p <> null and p = 100) or c <> null then "Completed"
else if p <> null and p > 0 then "In progress"
else "Not started",
type text),

// ===================== 😎 TASK NOTES (details endpoint) =====================
AddNotes = Table.AddColumn(
AddStatus, "Notes",
each let det = try GraphGet("planner/tasks/" & [TaskId] & "/details") otherwise null
in if det = null then null else Record.FieldOrDefault(det, "description", null),
type text),

// ===================== 9) DATE-ONLY (for relationships/visuals) =====================
AddDueDateDate =
Table.AddColumn(
AddNotes, "Due Date (Date)",
each let dd = [Tasks.dueDateTime] in if dd = null then null else DateTime.Date(dd),
type date)
in
AddDueDateDate

Thank you,

Fares Bechara