วีดีโอ

Hi, it re-runs entirely. The reason for this is that there is no way for me to find out which tasks are new or have changed in anyway.

I am glad you liked it!

You are very welcome. Greetings from the UK.

I have groups that don't have plans and I don't get the error, are you sure that this is what is causing it?

​@@BenHoward_PowerBI I'm facing 403 Forbidden error, list plans for a group this will sometimes fail the condition for which it fail are if you are not a member of the group that you own "statusCode":403,"headers "message":You do not have the required permissions to access this item. expression '@outputs('List_plans_for_a_group_3')?['body/value']' is of type 'Null'. The result must be a valid array. I found this, around minute 9-10 she is doing a troubleshooting when this happen: th-cam.com/video/mqGFbIGpCcw/w-d-xo.html

How to do this is beyond a simple response, however, it can be multiple tables in multiple worksheets, or you could load in multiple Excel files, or any combination of the two.

@@BenHoward_PowerBI Ok thanks for the reply!

Hi, it won't be because the .zip file has a different name. Did you see the subsequent videos and updates to the description, such as.... To import the zip file you need to add the query parameter "v3=false" to the end of the URL when you import the file. For example from my tenant the query is make.powerautomate.com/environments/Default-746993bd-b693-45c8-8ec1-f4755c4b728a/flows/import?v3=false from th-cam.com/video/82aYZBKiwoA/w-d-xo.html

Well, please share the video widely if you can. thanks!

@@BenHoward_PowerBI I am already doing it!

No, a Teams Channel is an area for collaboration, and a group is a selection of people. There can be a 1:1 relationship between a group and a channel, but not necessarily so, eg, one group can own multiple channels.

Glad you liked it!

Hi, no, it's related to Manchester Airport group, and Dave Scally has moved on from his position there.

thanks for the question. This is a video which shows how to automate the Power BI refresh from an Excel file stored in Teams/SharePoint. If you want to automatically refresh the Excel file, then this assumes it is populated from another (3rd party) source. I've used Power Automate to do this in the past, an example is here where I automate the refresh of Excel with Microsoft Planner data using Power Automate. th-cam.com/video/-U1Nnj95VMo/w-d-xo.html

Our pleasure - it was a great session.

Hi, I assume your URL is in the following format? tenant.sharepoint.com/sites/APOGroup/Shared%20Documents/PlannerTasks.xlsx. If you can't fix it reach out to me and we can schedule a teams call to try and fix.

@@BenHoward_PowerBI Hi Ben, I was able to fix the error. Now, I'm running into another issue: all my plans are ongoing plans with ongoing tasks, and I just need to track and visualize In Progress, late and Not started tasks, I don't need the completed tasks, and I think that's why the flow is taking so much time because is listing ALL tasks. Is there a way to run the flow without the completed tasks? Appreciate any advice you can give me. Thank you!

@@dayanagelvez5610 - I don't think there is any way to build that logic into the Power Automate - but I will check.

Hi, yes, check out this flow - github.com/ben-howard/Planner-PowerBI-with-PowerAutomate/tree/main/Single%20Team

@@BenHoward_PowerBI Thanks! I can't get the Office Scripts to work, is it simple enough for me to set up the single team flow as per the standard initial flow in your github?

@@jackscott4521 - is it that you cannot get the scripts to work, or you do not have access to scripts at all?

@@BenHoward_PowerBI I don't have access to scripts in my org

Yes, then you will have to use the process for removing all the rows from the excel sheets as per the standard initial flow. Because you are only synching a single team/planner plan then you shouldn't see any performance issues (assuming you don't have thousands of tasks!)

There is no "new" planner, there is the original planner @ tasks.office.com/ and there is project for the web @ project.microsoft.com/ - both support export to Excel.

Noted

Hi Marko, if you reach out to me via email (you'll find my email address in the Power BI template) or online etc, then we have a look via a teams call.

@@BenHoward_PowerBI Hi Ben, you are so available! However I fixed it! Thank you so much

@@showa3601 - Fantastic - great job!!

Yes, I would create a PowerAutomate script that opens and refreshes the Excel file, and incorporate this new script into the current one at the beginning.

Glad it was helpful!

No - github.com/ben-howard/Planner-PowerBI-with-PowerAutomate

@@BenHoward_PowerBI I figured out its inside the folder now , thanks a lot for helping with this video

@@BenHoward_PowerBI Hey Ben, Is there a way to extract task from Teams sub channels ?

@@umajeyaraman5130 - hi, can you elaborate on the question please, I am not sure what you mean?

Thanks for the great feedback. Hope this helps you out.

Hi, That's great, let me know how you get on. We ended up without labels for the categories on the Y-axis, but just added them in manually as text fields. Obviously once you have edited the .pbip file, you can save back as a .pbix file if you need to (which we did!)

@BenHoward_PowerBI worked great, easy to follow your tutorial and 13 columns worked a treat! Thanks 👍

wow - that was fast... Bot?

Hi, no problem. You need the desktop in order to connect to Excel file

Thanks!

Maybe I am not taking the right zip to import 😢

I am sure you are using the right zip file. Did you see the comment re using ?v3=false ?

Hi!!! Yes ! I add that query parameter to the URL before import the file . And after I recibe the error but it doesn’t appear the link to check the path .

Hi, for sure, can you send me a mail, you'll find my address in the .pbit file, or contact me via my website (applepark.co.uk)

​@@BenHoward_PowerBI , thank you for your quick response, I was able to find a person with access to Power Bi desktop, so we did necessary implementations. But now I have a further question, how do you construct "Task URL" column in your data model? I did not find it to be exported during the flow

@@MaksimBlekhshtein - the task URL is included in the .pbit that you can download from GitHub, but for ref the URL is constructed in Power Query. Here is the PQ code that does it. // Add in the task URL #"Added Custom" = Table.AddColumn(#"Changed Type", "Task URL", each "tasks.office.com/"&Lines.ToText(Tenant)&".onmicrosoft.com/en-us/Home/Task/"&[Task ID]), #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Task URL", type text}}), #"Cleaned Text" = Table.TransformColumns(#"Changed Type1",{{"Task URL", Text.Clean, type text}})

Thank you @@BenHoward_PowerBI! I will try to find it in my environment.

Glad you enjoyed it!

I think the idea is that you do your data modeling in Power Bi as a semantic model for Fabric.

@@ws3457 Yeah there's no way I could efficiently do the extensive modeling required to create a star schema in pbi. That needs to happen earlier in the chain of events (upstream), as per Roche's Maixm!

@@Tom-kp2lvThat it does!

Great!!

Hi, you need to set up connections for each resource type, you can do this from the connections tab in Power Automate, click on New Connection, and then it's best to use the search box in the top right (eg Excel) and then create your connection. Hitting refresh then back in the Power Automate import wizard on the connection will show you the new connection for the relevant connection type.

Okay, and what error do you specifically get?

@@BenHoward_PowerBI Flow save failed with code 'MultipleErrorsOccurred' and message 'The dynamic operation request to API 'excelonlinebusiness' operation 'GetSingleScript' failed with status code 'NotFound'

It goes on quite a bit looks like it cant read it

If it is timing out it is because you are hitting limits put in place by Microsoft. How long is a long time?

@BenHoward_PowerBI thanks for your quick reply. Usually 22 hours, I've seen it running for 1 day 22 hrs when I turned off the daily refresh to see what happens

@@alannahsteevens5102 - wow, that is soooo long... I'm trying to work on soln that uses the graph API but don't have much spare time at the moment - it *might* be quicker but who knows.

@@BenHoward_PowerBI I have used the graph API and it still times out. I think you're correct that it's the call limits per license, I turned off all other flows in our organization that weren't sensitive and it ran successfully taking 3 hrs again