Calling Graph from Power Query

[mhamilton-wiot]

Hi everyone,

We're trying to connect to Planner from Power BI using Graph.

We've followed this excellent post, calling "/groups/<our group id>/planner/plans" to get all the plans for a specific group into a table called "Plans". So far so good.

Next, we somehow need to iterate over that list of plans, and call "/planner/plans/<id>/tasks" to get all the tasks across all the plans into a table called "Tasks" that we can then join to Plans.

Is such a thing possible? Can I somehow loop over one table and call Graph for each row to produce another table? Or is there some easier way that we're missing?

Cheers,

Matt

[ImkeF]

True @mhamilton-wiot ,
couldn't agree more.
Especially, as they also actively discourage using GraphAPI endpoints at all from Power Query.

[mhamilton-wiot]

Thanks for the reply! The custom column solution did occur to me the day after posting this, but I hadn't gotten it working to the point where I was happy to post back here until the weekend struck.

I've managed to get it refreshing with a single custom column to get the tasks for each plan, but if I then tried to add another custom column to get the buckets for each plan ... that seemed to be a bridge too far and the dataset would stop refreshing. In the end I had to split it into two queries - one to get the plans+tasks, and the other to get the plans+buckets. A bit of duplication but it seems to work.

Hard to believe it's 2023 and MS still hasn't given us a proper Planner connector for Power BI. 😞

[ImkeF · Accepted solution]

Sure @mhamilton-wiot ,
you add a custom column to the table where you reference the column with the plan id as input.
If you run into refresh errors in the service, please check out this article: 
Web Scraping 2: Scrape multiple pages at once and refresh in Power BI service – (thebiccountant.com)