วีดีโอ

Good question, I did follow-up videos for getting “all my owned plans” here: th-cam.com/video/mqGFbIGpCcw/w-d-xo.html and “a list of plans” here: th-cam.com/video/1UfxETalb9E/w-d-xo.html 😀

If you search for those words in the code, you should be able to find where they are used and replace the text. You might have to tab through a few before you get to the likely one-

@bi-ome How to add some filed beside the "progress","start"... Thanks

It worked for me about 3 days ago, what is the behavior you’re seeing? Occasionally I have to refresh and try twice but it should still work.

😂 I'll get used to it eventually... I hope

Thanks! If you search in the code for the word "font" it will bring you to the place to change it - just replace the font names with your new fonts. I did an updated video here that explains how to change the colors also: th-cam.com/video/5w1tdoCvyPM/w-d-xo.html

Yeah, the trigger condition is important, but it's not going to update the item willy-nilly because we have the conditional branch in there - it'll only update the item if the status changes and the new value is "complete". If it updates the item, it'll trigger itself a max of one extra time to re-check the conditions. The trigger condition can't check if a specific field changed, because we have to run a "get changes" action to get that info, but it can check the existing field values (e.g. current status) which keeps it from triggering overly much.

@ I found your vid in my quest to send 1 (and only 1) email after an approval flow, or, likewise, populate a calendar with 1 (and only 1) event after that approval. It’s unnecessarily hard with the tools MS have given us (with the potential change trigger loop). I’ve seen other clunky solution with a ‘memory’ field to track the last value of the approval result field, a necessity because MS cannot use versioned field values within the trigger condition itself. Sigh.

@@roelanddewindt Yes, sending the reminder email is on my list too. Reza has a newer really good video on approvals in Lists if you haven't seen that one, it doesn't handle reminders but it is very good. You're totally right that it's unnecessarily hard to make the experience good.

Only if the field is required - you’ll have to update anything that is set to required to reset it to the same value that it already is (Power Automate foible). Is yours multi or single value?

To clarify, if your assigned-to field is single value, you can set the field to use the dynamic content card for your assigned to "claims" by inserting that (or email) and it will reset it to the current value. If your assigned-to field is multivalue, it becomes substantially more difficult because it wants the claims in an array, so you have to for-each on the assignees field to append to an array variable and then insert that in the array editor (it's the little "T" icon next to the field, erase whatever is in there and put in your appended array variable). At the end of the day, if you don't NEED a field to be required, it's easier to just ... set it to not required. That way you don't need to re-set the fields every flow run.

It'd be tricky to get them in the same row... theoretically anything is possible if you learn Deneb though. :) As a hacky trick, if your content is very short you could try using a measure to concatenate all the text into one value and display that, but it'd require some DAX (concatenatex() is what you'd want there).

The team coincides to the Microsoft 365 Group, right? Off the top of my head I think the group is in the "owner" columns on the task and project tables, and I think there was one with the group name - that should do it. The team membership is in a related table also if you need that - that'd let you create a dimension out of it to filter both projects and tasks if you relate it with the group ID.

Looks like this person has a video on how to get second highest: th-cam.com/video/fe9Pv7vOzL4/w-d-xo.html I also had luck getting the top 2 rows instead of 1 row, then getting the top 1 of that sorted ascending instead of descending - so bottom of 2 rows (e.g. SecondHighestProduct = SELECTCOLUMNS( TOPN( 1, TOPN( 2, VALUES('Product'[Product Name]), [Latest Fiscal Year Sales], DESC ), [Latest Fiscal Year Sales], ASC ), "ProductName", 'Product'[Product Name] )

Is it in the JSON file? If it stops at 500 tasks, you need to enable pagination. See the FAQ linked in the description for how to handle pagination and larger plans-

Make sure you reset the assignees variable at the end of the loop as shown, or it will keep tacking on values from all the prior tasks onto each task as it goes. Also check your loop nesting, I think I saw one other person had this happen and it was a mis-nested step

😆 The completed date is important, you’re right! You might also want due date reminders… that’s on the list too haha

@@bi-ome Yes, but that trigger will function a little differently. It will assure the due date is always in the future 😸

@@barttrudeau9237 Well, only if you don’t want it to keep reminding people who are extremely tardy 😆 But yeah, it will check the due date for sure!

It sounds like there is an issue with your for-each loops. Like things are not nested properly or you’re not looping over exactly the right values-

I think I responded to this but it popped up again for me - you count the rows on the array (not the HTML) with length() and use an if-condition on that - if it’s >0 send one email, if not send the other email.

Ok. Is it possible to show me the flow?

It still works for me today on Version: 2.139.1576.0 64-bit (January 2025). I would try pasting in the exact measure name and waiting a good 30 seconds and see if it pops up? Sometimes it takes way longer than you expect. I tried creating a couple new measures in a new file to test and they do eventually come up, but it took a long while. If they don't, if you put in the name exactly the same and hit submit it usually resolves with a blue underline.

Yeah, I have since started turning on UI zoom - can't go back and fix the old ones though!

Doing anything that involves writing to the premium Planner tables is an unholy pain - you have to go through the Project Schedule API, not the Dataverse connectors. It’s something for more of a Dynamics developer type role… technically any of us can do anything, but the time investment that would be required for me to do it would be very high, and I wouldn’t enjoy it. I also don’t necessarily want to reinvent the wheel - Microsoft is pouring development effort into unifying the task experience into a single UI that includes premium and basic Planner together right now, so likely anything I do in that direction will be obsolete in a year. Hence I’m sticking more to the reporting side because that feels pretty stable. But if you’re interested, the thing that tends to help to search for is Project Operations content, because it’s built on the same tables and people put apps on it!

Click and drag the (…) ellipsis menu in the corner instead of trying to click the edge of the box 😀

I think you could get a count of rows in the array that goes to html with length(your array), then do an if-condition on if the row count( if count is > 0 do one thing and if not do another)

Thanks, I am! Same to you!

You should still be able to replace errors in the expansion step, did that not work in your case? Whichever step produces errors, if you replace the errors there with something else, the next steps should continue working. But if not, is it possible to filter your query down to only the PDFs you need before expanding? You can filter on path, name, other metadata if you have anything useful to work with there.

Yes, it's a bit roundabout but there is a "convert file" action in the OneDrive connector set. So you can create an HTML file out of your HTML with "create file" in OneDrive (.html extension) and then use the "convert file" action in OneDrive to convert from HTML to PDF. Then you can attach that to the email in the attachments section using the outputs from the "convert file" action. This is all happening in the "for each user" loop, so its using the rows for each person that we're already getting, just doing some extra steps with them.

So after creating HTML table, I will create the HTML file with HTML table in onedrive. Then, convert it to PDF and attach as email for each user, right?

@ Yep! Or if you have premium, you can just use paginated reports - this is what they’re designed for.

Ok. So after creating HTML table, then export to file for paginated reports, right?

@ No, if you are on premium capacity you want a totally different action set - I believe Guy in a Cube has a video on it if you search their channel. The technique in this video is the non-premium route to a similar solution.

Aw that's so great!! Thanks so much for sharing with me :)

I'll put it on my list. But generally speaking, if you have a user's email address and have some data about your user's department structure, you can use that user data as a dimension by relating it on the user email. Make sure that the case is all uppercase or all lowercase on both sides for the relationship to work. If your scoring is attached to text, you can typically split the column to get the numbers in one column or the text in the other - since it needs to be numeric to get an average.

You’d need to use the Graph API, it’ll let you get extra fields and filter the query more precisely. MS has a documentation page with syntax-

@@WinterWang-h3o Where are you seeing the duplication? Any multivalue fields will cause duplicate rows when expanded in the tasks table directly, that’s why when we expanded in the second video we put them in their own tables and relate them with a relationship instead.

It depends how large your organization is. If you have more than 5000 total tasks, you'd really want to start looking at Logic Apps or premium Planner instead - premium Planner data is stored in Dataverse tables that you can create a view-all role for to pull everything without Power Automate (just use the Dataverse connector - I have a video on that too). You'll hit the API calls per day limit on Power Automate if you're pulling at a large organizational scale. There's pros and cons to premium Planner, but it really is more scalable and is where Microsoft is pushing people for org-level reporting. Only project managers need licenses, so it's not heinously expensive.

@@bi-ome thank you for your reply. But if we have less than 5000 tasks, and not premium planner, is it achievable in the way which you do report in this video? First thing, which come in mind is to add user under which flow is working to all groups to see this plans. But maybe there is a better way

@ Yes, if you have less than 5k tasks, you could add a service account (this is a licensed account not for a specific person) to the groups as a member or owner and use the “get my owned groups” or the equivalent for groups it is a member of. It might be possible to use a service principal as well, but I’ve not looked into that as much-

I'm not sure what the issue would be here, sorry!

It’ll pick up new tasks when both the flow and Power BI refresh run. The flags it will not pull changes for automatically when you add new ones, you have to go in and change the query in PBI (I go through it in one of these… part 2 maybe?). I would recommend trying to come up with consistent flags from the get go if you can. Other apps like premium Planner or MS Lists, even, will handle the flags and flag changes much better (I have videos on both of those and how the flags work there).

I've been having issues with people taking the files I share and reposting them as their own without attribution, so I've been sharing less of the files and more of the technique. In the next month or two I may try to package everything into a channel membership or similar, though.

​@@bi-ome OK, I understand, no problem, anyway on our side we never connect our corporate Power BI directly to the sources, in this case, for data in Dataverse we will configure a shortcut in a Fabric lakehouse. On the other hand, in self-service mode for our end users, your direct method of accessing data from Dataverse can be interesting. I would have provided your Power BI as an example to our end users to make their own reports on Planner data. Thank you, you are doing a good job.

The Planner connector isn't getting the Dataverse data - the standard Planner data is stored somewhere totally different. If you're looking for the data in premium Planner in Dataverse, you'll need to be a member of the group the plan belongs to to see it. If you need to be able to see more, your Power Platform administrator can create a role that can view more (or all) and apply that to specific people. You'll have the same difficulties in Power Automate as Power BI, because what's controlling what you see in the tables in both is your group membership and access level - but make sure you have the correct environment selected if you're looking for premium Planner data, because there could be multiple environments.

@@bi-ome thanks for your quick answer ;) really usefull

Thanks!! That’s such a nice comment! 😄

ooh that sounds like a great idea!

Yeah, I have a couple Gantt videos already, just not with this dataset. The best free ones, as far as I can tell, are David Bacci's Deneb template (th-cam.com/video/5w1tdoCvyPM/w-d-xo.html) or the Microsoft one in app source (this one is... clearly lacking, but easier to configure th-cam.com/video/fgCWlXLu-c4/w-d-xo.html). Luckily the core visuals roadmap says they are going to eventually add a brand new Gantt, but there's no date commitment on that so I don't know how long it will take.

It's hard to say what's going on without looking at the query. I would do a code comparison check to make sure the content inside the files matches exactly, just in case, and then make sure the URL is valid if you go to it in a browser.

Make sure you hit refresh in the query editor (not report view) - it caches the data for... basically forever there. Also make sure they're actually being pulled in, sometimes you need to go back to the expansion step and make sure the boxes next to them are checked (particularly if you rename things or add new flags).

@@bi-ome Thanks for getting back to me so quickly! Its not a problem in the PowerBI report - the data isnt even showing in the Power Automate flow when it has run/in the JSON file that the PowerBI report is looking at. Its like it doesn't think that there are any labels set in the first place even though I know that there are and I have picked the right colours in the flow!

@@EmilySmith-p7q Are you super sure you have the right colors? Some of them are backwards from what you'd expect.. e.g. light blue is actually darker blue than blue, and same thing for the greens. The "light" colors are not light at ALL. I put a list of them here if you want to double check: christine-payton.com/microsoft-planner-project-for-the-web-color-label-values/

@@bi-ome I've just gone for the simple top colours pink, red, yellow and green and have even removed my own label descriptions on them so they are just showing as the colour. I am going to try building a new flow to see if that makes any difference but I can't see any reason why it wouldnt bring these in!

@@bi-ome Right so, I have worked out that I can get the True to pull through if I initialise a variable and then append to an array variable BUT I can't work out where to put it so it isn't duplicating loads in the JSON file

I would just sync the SharePoint files to the desktop with the OneDrive sync button in the library. Power Automate Cloud has a lot of AI features that will extract data from PDFs also, but they're not free and they work very differently (but it's a totally valid option if you have budget). I think Damien Bird has a tutorial out there on it if I recall?

It won't be 1:1 - you could have two tasks with the same completed date, even if you don't currently, so you want it set accordingly. The date table side will be 1, the task side will be many. The null values are fine, you'll just want to add a filter to remove them on visuals where you're grouping by month/year/whatever or they'll show up in a (Blank) category!

@@bi-ome Hello and thank you for your fast reply! So 1:N is OK, perfect. Looking forward seeing your next tutorials, BR Katrin

Right? There's SO many task management tools in Microsoft 365, go figure that sometimes it's still better to just make your own, lol. 😂

Thanks! 😄 The 5k threshold is for the view only, so the export to Excel and the Power BI connection are not affected (assuming you're not using the "list view" connector option, it's opt-in and hidden by default in Power BI). I double checked an Excel export just now to make sure, and it handled 12k with no issue - but I've seen reports of people having problems with 30-50k+ where it refuses to do more than that. Power BI will handle a TON, to where you'll run into slowness issues before you'll hit a discrete cap... keep the number of transformations low and remove columns you're not using (this step "folds" if done first) and you should be fine there. For things like Power Automate, you'll run into issues with pagination and loops - so enable pagination on "list items" actions and set the threshold above your max. For-each loops max out at 5k unless you have premium Power Automate, so just try to avoid those... they'll be slow anyway.

That's a really good question. I think you'd have to write the back-end column names into the query for the URLs you're using similar to what they do in the documentation here, but using the list of sites instead of one hard-coded site: learn.microsoft.com/en-us/sharepoint/dev/sp-add-ins/use-odata-query-operations-in-sharepoint-rest-requests I don't think the custom fields are in the browser, but it's been such a long time since I did this that it's left my brain.

@@bi-ome Thanks Christine, I am pretty new to Power BI so this is way beyond my ability right now :)