Get PLANNER Data into Power BI with a 2-ACTION Power Automate Flow!

Подписаться 7 тыс.

Просмотров 30 тыс.

50% 1

There's no Planner connector in Power BI, but you can DIY with Power Automate! Here we make super-simple Power Automate flow to grab and store the data on a schedule.
Here's the full details version of this video:
• Get Planner Task Detai...
You can schedule refresh on the dataset for this with the technique here:
• How to Schedule REFRES...
Or get Planner data for ALL your groups and plans here:
• Get Planner Data for A...
Send Teams or Outlook notifications asking assignees to update specific tasks from Power BI:
• Use the Power Automate...
Or opt to send all tasks for a given assignee in a single email/chat:
• Send STYLED Lists or T...
Free Microsoft Gantt visual:
• How to create a Gantt ...
or
David Bacci's open source Deneb template:
• How to use David Bacci...
Here's the custom Power Query formula we used to flag late items in the video (Github link, RU-vid does not allow some of the code characters in the description):
github.com/chpayton/BI-Files/...
0:00 Intro
0:43 Create the Power Automate flow
2:44 Enable overwriting on "create file"
3:39 Copy the file path & download
4:45 Connect in Power BI
6:35 Clean up the data
7:05 Add a custom column to flag late tasks
8:20 Schedule refresh

Наука

Опубликовано:

17 июл 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 118

@bi-ome 7 месяцев назад

If you are looking for assigned-to name, buckets, subtasks, or descriptions, see this video! ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-q7bkWpVczz0.htmlsi=XeOXFuHizwUNNS-d

@komailbutt2998 10 месяцев назад

Nice video. Thanks for making it. Looking forward to the second part .

@jasonsimons9130 10 месяцев назад

Very cool. Thanks Christine. 🎉

@akkoordvanwassenaar200 Месяц назад

Thank you for your excellent information on customizing planner data in Microsoft PowerBI. I am starting to learn and I'm feeling more confident now I have your video's to teach me how to proceed. Thank you very much from the Netherlands.

@zzota 10 месяцев назад

This is just what I was looking for 🙂Thanks Christine.

@bi-ome 10 месяцев назад

Awesome!

@corybeyer1 19 дней назад

This was an amazing video. You made this sound so easy. I'm thankful you took the time to make this video.

@stalawina 7 месяцев назад

I am usually pretty silent on RU-vid, but God you're great! I've been looking for such a tutorial for a while. I use Planner a lot in my organisation it works perfecly on a day to day basis. I have one bucket for each equipement of my shop and have scheduled tasks that are automatically assigned to Azure AD groups. But creating reports was not easy with Planner itself. The only way was to manually export to Excel every time. Now, if I could have access to the description and comments of each Planner tasks, that would be heaven. Thanks again Christine!

@bi-ome 7 месяцев назад

Aw thank you!! I’m so glad it helps you. 🤗 Description is gettable via the method in part 2 of this video! I think one of the comments goes over it, though I’ve been meaning to update the video to streamline it a bit so we will see…

@AngeloBommarito 9 месяцев назад

Definitely one of the best Power automate videos on the web. No missing steps, straightfoward and to the point. Keep making great content!

@bi-ome 9 месяцев назад

Thanks so much, that means a lot to me to hear :)

@JamesFujitsu 9 месяцев назад

This is a great video, thanks so much!

@claudeuu2 5 месяцев назад

Thank you so much, great video!

@hugofrauches 10 месяцев назад

Great video, thank you!

@astatine0085 8 месяцев назад

This is great, thank you. It solves a problem of 'scattered' data that we can consolidate views and analytics in power bi. tasks and other docs.

@bi-ome 8 месяцев назад

Yeah! Conceivably you could run this whole thing in a for-each loop and loop over a whole list of Planner board IDs, too, then put it all in one dataset to get a high-level view of activity. Haven't gone there yet, but it'd be doable for sure. :)

@HI-gf7nk 8 месяцев назад

Thank you so much for sharing, great video, easy to follow :)

@bi-ome 8 месяцев назад

Glad it was helpful!

@mescron 3 месяца назад

Awesome video!

@Wh0NeedsFr1ends 2 месяца назад

This is seriously clever.

@rebekah5429 7 месяцев назад

This was so helpful!

@bi-ome 7 месяцев назад

Thanks! If you are looking to get the task details and visualize, I have a new updated video that I am wrapping up right now, it should be available tomorrow morning :)

@rebekah5429 7 месяцев назад

@bi-ome I am really looking forward to it! I understand all if the concepts in thiw video, would like to know how to do the buckets amd assigned to. I read up on it and still have a few questions!

@gabrielwalteros9068 9 месяцев назад

A very good video. (y)

@bi-ome 9 месяцев назад

Thanks!!

@berkay3391 7 месяцев назад

Thank you for the video! What about if I want to add description and checklist items parts of the tasks to my PowerBI dashboard? There should be another step as get task details in the flow? I have tried it however, when I select description or checklist items in value field, it applies an "apply to each" action which makes everything confusing for me. Thank you in advance!

@bi-ome 7 месяцев назад

Yes, there is a part 2 video linked at the end of this one and in the description that gets the details. :)

@AliceJakins-hb6ll Месяц назад

thank you for the video. Do you know if it is possible to connect multiple planners across multiple teams into power bi?

@bi-ome Месяц назад

Yeah, you can get/display your data virtually however you want in Power BI. As long as you're recording the plan name/ID in your data, you can append it all together and filter on plan name no problem. We actually do this in the last part of the three part series here: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-mqGFbIGpCcw.html, but make sure to start with part 1 if you decide to do this (that's where we configure all the actions). Part 2 is the Power BI modeling, part 3 does it for all plans for a particular owner, but if you wanted it to get multiple owner's worth you could add a service account as the owner on the plans you want to pull. It's better to run flows with a service account anyways, so that's a good bet. The only thing you'll run into is if you have too many tasks in the plans, it doesn't handle thousands of tasks very well (you might swap to Planner premium for that, and get the data out a diff way with the Dataverse connector).

@emmaforster5551 7 месяцев назад

great video!! is it possible to show the labels of each task in power bi?

@rebekah5429 7 месяцев назад

Curious on this as well

@bi-ome 7 месяцев назад

I just did an update to this that gets the labels here - you have to get each of the color labels separately, it's a bit tedious but it works: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-q7bkWpVczz0.html

@MESMVIX 7 месяцев назад

First of all I want to thank you for this clarifying video. I follow the steps but seems that something is missing when PBI gets data from JSON file. I reviewed several times the part PBI imports the columns. Comparing the example on the video, the column "value.CompletedDatetime" was not showed im my results. I tried to find but couldn't find. Have you ever expirienced something like that?

@bi-ome 7 месяцев назад

Try opening the JSON file in a text editor and do a search for the word “completed” with control + f to see if it’s in the file at all - that will tell you if the issue is with the flow putting it in the file or with Power BI. 🙂

@Guinosh 7 месяцев назад

Typing body into the dynamic content field at the File Content step for me doesn't find anything. I see the various other dynamic items like value Id, value Title, etc., but no body. If I use one or more of them, I can generate the JSON file, but each line overwrites the last as a For each auto-generates if I pick any of the dynamic content items I do have.

@bi-ome 7 месяцев назад

Someone else mentioned this too, it's the new Power Automate UI - if you switch to classic with the ellipsis menu next to the Test button in the toolbar it should show up there.

@Guinosh 3 месяца назад

@@bi-ome Thank you! And thank you for all your great videos!

@alexanderostos3284 9 месяцев назад

Great video! Very helpful indeed! What happened with the part 2 video? Can't find it and it was here before :(

@bi-ome 9 месяцев назад

I re-recorded it yesterday to streamline and add subtasks - uploading it later today as soon as I finish editing :)

@bi-ome 9 месяцев назад

Here's the updated part 2: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-NdkIbJCkgOs.html

@alexanderostos3284 9 месяцев назад

@@bi-ome you are the best!

@thomasbraud6368 8 месяцев назад

Hello Christine, great video, thank you for the help ! Do you know if it's possible to get Notes and Comments of the tasks ? If yes, what is the name of theses columns in power query ? Thank you in advance

@bi-ome 8 месяцев назад

Notes are the "Description" field on the other end, but comments are not available yet. You can vote on the idea here though. 😄 ideas.powerautomate.com/d365community/idea/b4f93894-a516-49ce-a5d5-6384d46e8b43

@martinnatella5500 8 месяцев назад

@@bi-omehi Christine, do you know how to return the description field? I can only see the hasdescription true/false field as being available to expand.

@bi-ome 8 месяцев назад

@@martinnatella5500 I went and checked the flow - it's in the top level of the task details action as "Description". So if you already have a for-each loop on the tasks to get the task details (we added one in part 2 of this series to get assignee names), then you should have it, otherwise you can add one.

@martinnatella5500 8 месяцев назад

@@bi-ome I've added it to the flow but I get the output in the subtask JSON and not the planner tasks JSON. Sadly not all of my tasks have subtasks. All a bit over my head!

@bi-ome 8 месяцев назад

@@martinnatella5500 Yeah, you will have to essentially create your own JSON variable to hold the tasks instead of using the body from "get tasks" to create the file. I did this one as an example so you can see what it looks like - you would compose this variable outside of the for-each loop and use that in place of the body of tasks for the create file step: christine-payton.com/wp-content/uploads/2023/11/list-tasks-2.png I didn't notice the description wasn't included or I would have done it this way to start with lol - time for video retake #4 I guess. 😅

@aslanjRuns Месяц назад

Thank you for the video, Christine! In my case, the Create file step doesn’t show Body as a dynamic content item 3ven if I search the word "body." What can be the reason? Thank you

@bi-ome Месяц назад

Make sure you’re in the classic editor, toggle modern off - it doesn’t show all the cards for some reason

@aslanjRuns Месяц назад

@@bi-ome it worked! Great! Huge thanks 🙌

@user-jo5fj6fq9d 7 месяцев назад

Is there a way I can get the Labels from MS Planner also into PBI?

@bi-ome 7 месяцев назад

They are not currently in the Power Automate connector response, but there are a lot of updates coming to Planner in 2024 so hopefully it gets added!

@alannahschooling1157 2 месяца назад

Hi! Does this provide live planner data, or just the data within planner at the time of export?

@bi-ome 2 месяца назад

You would schedule the flow and schedule the refresh on the file in SharePoint, so it's not live but it can auto-update. Usually I go with once per day for the schedule. If you're using Project for the Web / the new premium planner, you can make it live via direct query to Dataverse, skipping Power Automate. That's on my to-do list. :)

@bethnorbury8644 7 месяцев назад

Great video! Is there a way to get the assigned to User ID through?

@bi-ome 7 месяцев назад

Yep! The User ID is in the _Assignments in "get tasks" - we use that to get the assigned-to display name in this one: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-q7bkWpVczz0.html

@user-vg6ni6ns9d 5 месяцев назад

Great simple video! I'm almost there but running into a problem that the data all shows fine from the downloaded JSON on PBI, but when I replace it with the sharepoint file path it all comes up blank? Just one row of data with all either 0's or 'nulls'

@bi-ome 5 месяцев назад

If you open the SP file, does it have the same content/structure to it it that the other file had?

@user-vg6ni6ns9d 4 месяца назад

@@bi-ome Yeah my downloaded file and the file I link it to on sharepoint look the exact same, in terms of the content and structure in there anyway

@bi-ome 4 месяца назад

@@user-vg6ni6ns9d Try refreshing in Power Query? If the content is the exact same, there is no reason that it shouldn't come out the other end the exact same unless there's an issue with the path or something. You might click through the query steps to see where the weirdness starts.

@user-vg6ni6ns9d 4 месяца назад

@@bi-ome Thanks, no idea what happened but after coming back to it after a few days, now it works! Thanks for the great video!

@AmandaCrook-er7gm 4 месяца назад

I love this tutorial and I'm trying to implement - my only issue is that it only returns 400 rows and we have a large and ongoing planner (currently at nearly 3000 tasks and growing steadily). I have worked out how to increase the number retrieved by using pagination and threshold, but eventually we will pass the maximum threshold. I'd like to use the filter on the tasks to only show those created in the last X days, but I'm not sure how/where to put the filter in the flow and then only get that output into the json file? Anyone had any luck with this?

@bi-ome 4 месяца назад

That's a tricky one if they're all in a single plan. As far as I can tell from the documentation and an internet search, the Planner queries (both via HTTP API call and connector) don't support filters in the "get" step. You can filter the array after you get the tasks, but that doesn't do you any good. Something to consider is that there is a maximum number of tasks you can put in a plan, period, so I would consider breaking it into multiple plans if you have a lot of old, irrelevant tasks in there because you could max out Planner itself 😐. learn.microsoft.com/en-us/office365/planner/planner-limits

@AmandaCrook-er7gm 4 месяца назад

Thankyou, I didn’t know there were task limits. I think my solution will be exporting the plan to excel and archiving that document before deleting old tasks so we don’t lose our task info, rather than filtering in power automate.

@RitaMendesFonseca 2 месяца назад

Hi , can you give the code from the custom column please

@oliverfisk4204 4 месяца назад

The video is very clear but I do not get the same options when creating the file step. There is no body option either in list or through search.

@bi-ome 4 месяца назад

Are you in the classic editor? A lot of the dynamic content cards don't show up in modern.

@saadnasr2373 5 месяцев назад

Hello! I ran into an issue importing the .Json file into the Power BI data flows; after expanding the tables i got this error: "Expression.Error: The import Expanded value.assignments.fa1f6e96-accc-4a65-885b-a566e8c4dabd.assignedBy.application matches no exports. Did you miss a module reference?"

@bi-ome 5 месяцев назад

It sounds like you're trying to expand a field that no longer exists in your data. If you click the gear menu next to the expansion step, it'll open up a window. You probably won't see an "application" field there, but if you do, uncheck it. Then click "ok" whether or not you've unchecked anything and it should update the step to remove that reference.

@user-rz1hq8tz3x 6 месяцев назад

Hi, my planner export .json file doesn't show completed items for some strange reasons. I can't find any items for which progress is 100 percent. I have turned on pagination and set it to 1000 as my data can go into 1000 rows. Any idea on this?

@bi-ome 6 месяцев назад

How many rows does that result in when it runs? It does sound like you're hitting the row limit, I'm wondering if you got more rows in your result after turning pagination on or not.

@mikeforkan1524 5 месяцев назад

Hi Christine, great video. However I am having an issue where all assignees names pull in, for example task 1 might be assigned to person x, and task 2 might be assigned to person y, but when I pull the data, it says person x and y are assigned to both tasks 1 and 2

@bi-ome 5 месяцев назад

It sounds like you didn’t reset the variable that holds assignees in the for each task loop. You want to reset it right at the end after it gets fed to the larger data array, otherwise it’ll keep tacking on people as it goes. I think that’s covered somewhere in the video because I ran into the same issue in a prior version. 😄

@mikeforkan1524 5 месяцев назад

@@bi-ome thanks a lot for your help. The issue was it automatically created a for each task loop on the append to array variable step.

@caraqal 13 дней назад

Thank you so much for the video, refreshingly clear, concise and easy to follow! I would like to offer an edited version of the code to evaluate the Late? column, that handles null (not completed) value.completedDateTime and date format mismatch i had in value.dueDateTime: if [value.dueDateTime]=null then false else if DateTime.Date(DateTime.LocalNow())>DateTime.Date([value.dueDateTime]) and [value.percentComplete] 100 then true else if [value.completedDateTime] null and[value.completedDateTime] > [value.dueDateTime]then true else false

@bi-ome 13 дней назад

Thanks for sharing!!

@sudeepghosh7003 5 месяцев назад

I am not able to get Body option when I am trying to create file. It does not give the option even when I try to search. What am I doing wrong.Cant seem to get the planner tasks exported at all.

@bi-ome 5 месяцев назад

Make sure you toggle off modern UI in the toolbar. It’ll keep a bunch of things from showing up-

@user-iy5sk4eq6r 10 месяцев назад

Hi its very helpful, can you try to pull the assigned to name .

@bi-ome 10 месяцев назад

Yes, I am planning on doing that soon :)

@amberalcorn1074 3 месяца назад

When creating a file I don’t see power BI … can I use share point site in site address

@bi-ome 3 месяца назад

Power BI is a desktop application you would need to install to do this, you can get it from the Microsoft store or powerbi.com. I am not sure what you mean by the second part of your question, but you can connect to files stored in SharePoint with Power BI yes

@atheermsh4209 6 месяцев назад

when I download the file , and try to get data using json file it's not appear! where do you think the issue is?

@bi-ome 6 месяцев назад

Did you remember to put .json at the end of your filename in the flow? You have to do that to set the file type, and the json connector will be looking for that specific file extension. :)

@user-pf4jk4qr3v 6 месяцев назад

Is there a work around for mac users?

@bi-ome 6 месяцев назад

You can use Excel Power Query to connect to the data, but you won’t have the same visualization options. Or use Parallels on the Mac.

@ricktorres5418 Месяц назад

Has anyone figured out a solution that doesn't lead to exceeding to reaching your query limit? I get a notification from Microsoft stating my flow is running too many actions and there is a "transition limit" of 10000.

@bi-ome Месяц назад

That’s throttling - you can fix by setting degrees of parallelism on loops to 1 usually. But this method is not going to work well if you have 1000+ tasks, period, I didn’t anticipate people having that many :)

@miraintwala173 2 месяца назад

Can group ID and plan ID be same ?

@bi-ome 2 месяца назад

They shouldn't be. They're system-generated though, so there's probably like a 1:1000000000000000 chance it happens, but if yours are I would make sure that you inserted the right dynamic content cards in the append to array step first. :)

@shirosaomd7146 5 месяцев назад

For some how the excel file is corrupted when I create an excel file via Power Automate

@bi-ome 5 месяцев назад

That's because this tutorial is not creating an Excel file, it's creating a JSON file. The "list tasks" output is hierarchical data, not flat, so if you want it in Excel you'd probably want to take the specific pieces you need and use a "create csv" action on them.

@saadnasr2373 5 месяцев назад

Hello! Thanks for the amazing video! When I export the JSON file into powerBi I get the error "DataFormat.Error: We found an unexpected character in the JSON input. Details: Value=b Position=0" how can i solve this?

@bi-ome 5 месяцев назад

It sounds like there's a typo in your array variable somewhere. Either there's missing brackets, missing quotes, extra quotes, or missed comma - something like that. If you make even one typo in there, it'll basically invalidate the json. You might open up the JSON file to look at it and see if you can tell from there, then check the steps that are appending to arrays.

@saadnasr2373 5 месяцев назад

Thanks for the response! It was a type and I was able to figure it out! How can i save the file in SharePoint as an Excel format instead of .JSON @@bi-ome

@bi-ome 5 месяцев назад

Sure :) The easiest way to save to Excel would be to save it to a CSV with the "create csv" action, but you will need to only select the "flat" fields (e.g. if you try to put assignee or subtasks in there, you're gonna have a bad time). This is why I used JSON, because it's handy to have it in Power BI and Power BI has an "export to Excel" button anyhow.

@SeanBauman 5 месяцев назад

@@saadnasr2373 I had the same error. How did you find and fix the typo?

@juancarloslezama140 Месяц назад

@@bi-ome Hello Christine, thank you for such an amazing video, it is helping me a lot to have statistics graphs of task in my team, I have the same error, and as you said we can locate the typo error and go forward, but the problem is that this error appears when we tell Power BI to get the data from share point instead of taking the fle from C: drive without touching the file, when we take the file from C: drive there is no error, anyway this means that we need to touch the file to fix it and aoutomation is gone. Do you have an idea to workarround this error? thank you again and looking forward for your kind help on this.

@user-qd8nu1yw6g 6 месяцев назад

can only json file be used like you did? Can't we use Excel file?

@bi-ome 6 месяцев назад

It's technically possible, but you need to concatenate all of the multivalue field values (subtasks, assignees) into a string, meaning you'd lose the ability to filter properly on something like assignee name where you have multiple assignees. Using JSON is the easiest way to let you keep the multi-dimensionality for sorting and filtering - it makes a better Power BI source.

@user-qd8nu1yw6g 6 месяцев назад

@@bi-ome when I tried with an excel file, in power bi it gives an error that the datatype is binary and thus I wasn't able to change the source

@bi-ome 6 месяцев назад

@@user-qd8nu1yw6g Yeah, if you are using an Excel file as your source you will need to use the Excel connector instead of JSON and the query steps will be totally different because the data is structured differently, so you'd need to redo the queries entirely most likely.

@profundone5149 Месяц назад

I have errors in the overdue column

@bi-ome Месяц назад

Yeah sorry you have to provide more context if you want help with that, like your formula and/or the error message

@profundone5149 Месяц назад

@@bi-ome Expression.Error : Sorry... We were unable to apply the < operator to the DateTime and Date types. Détails : Operator=< Left=21/06/2024 12:00:00 Right=14/06/2024 ******************************* if [value.dueDateTime] = null then false else if DateTime.Date(DateTime.LocalNow()) > [value.dueDateTime] and [value.percentComplete] 100 then true else if [value.completedDateTime] > [value.dueDateTime] then true else false *******************************

@profundone5149 Месяц назад

example : value.percentComplet : 0 value.StartDateTime: Null value.createDateTime: 30/04/2024 value.dueDateTime:21/06/2024 value.completeDateTime: null value.checkitemlistitemcount: 0 value.activechecklistitemCount:0 Late: Error

@bi-ome Месяц назад

@@profundone5149 One of your columns looks like date and the other datetime - they both need to be the same type to do the comparison. You can select the column, right click, and change the type to match (make sure you do that before the comparison step).

@profundone5149 Месяц назад

@@bi-ome All the columns are only dated

@vaishnavisuresh8496 3 месяца назад

Is it just me? or any of you also now tried and not able to find the the option to get the body from dynamic content even after explicitly typing it

@bi-ome 3 месяца назад

Are you in the modern UI? It doesn't show up in the modern UI, you need to toggle it to classic. This is a top complaint about the modern UI, a lot of the dynamic content doesn't show up. :x

@vaishnavisuresh8496 3 месяца назад

@@bi-ome thank you'

@matiasbravo166 5 месяцев назад

Greetings dear, along with my regards, I would appreciate it if you could help me by providing the following information: How can I obtain the Excel/table that you worked with? When I try to perform the same exercise, I can't get the column called 'value.createdDateTime' and 'value.completeDateTime.' Thank you in advance for your time and kindness. Greetings from Chile!

@bi-ome 5 месяцев назад

Hello! The tool we're using for the table is Power BI Desktop - not Excel, but you could use Power Query in Excel to a similar effect. The data structure is in JSON - so it's not a table per se, the tool just reads it like a table. Power BI is actually free for individual use, so you could download it here to try it out and see if you like it! powerbi.microsoft.com/en-us/downloads/ There is a PBIX file for this here, though this is from one of the later more-detailed versions of the tutorial: github.com/chpayton/BI-Files/blob/main/Misc/Planner%20Data%20Demo.pbix The thing that will trigger licensing costs is sharing reports, because other viewers need a license to view. I should probably do a video on how to do this with just Excel, but I like Power BI a lot so I tend to use it for everything. 😅