If you are looking for assigned-to name, buckets, subtasks, or descriptions, see this video! th-cam.com/video/q7bkWpVczz0/w-d-xo.htmlsi=XeOXFuHizwUNNS-d
I am usually pretty silent on TH-cam, 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!
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…
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.
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. :)
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.
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.
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: th-cam.com/video/q7bkWpVczz0/w-d-xo.html
Great video, I managed to import it. For those using professional access (company account), I had to use an organizational account to release the font.
Hi, I have the upgraded version of planner in teams, "project". I can create the .json file for tasks for my old system, but not since I moved to projects.
The technique to get the data out of that one is totally different but much easier - I have a video on it here: th-cam.com/video/F-RrxxUC_Q4/w-d-xo.html … maybe I shouldn’t call it easier, it’s more direct though haha.
I have an issue with the upload process in Power BI. I received an error message stating, 'An unexpected character was found in JSON input.' I checked my JSON code, and it appears correct. Do you know how to fix this?
That's weird, I would try pasting it into a JSON validator and see if that narrows down what the character is that's causing problems. You can potentially just replace those in an expression in Power Automate if you need to, but you need to find out which characters it doesn't like to do that--
@@bi-ome Hi, thanks for the response... I was checking and the JSON is ok but the error that I' have is when i paste the source link from sharepoint. When I do that, the system give me the "An unexpected character was found" error
@@felixrodriguez2724 The source step where we update the path shouldn't care about the JSON content, it's the expansion step that looks at the JSON input structure so I'm not sure why you would get that message there. Make sure you don't have any characters like #, $, /, etc in your filename?
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!
wow great work, thank you Christine! I don't understand how there no connector for sharepoint file, you'd think that would be a given but the workaround was brilliant, thank you once more
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
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
@@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.
@@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.
@@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!
@@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. 😅
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
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?
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. 🙂
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: th-cam.com/video/mqGFbIGpCcw/w-d-xo.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).
Can you do this without Power BI Desktop, I am limited to Power BI Service and unable to get the JSON file. My choices for import are excel, csv, manual, and template. Thank you for any help you might offer.
You could do it with Excel, but it would be quite a bit harder to make the flow for it. Do you have Windows? Most companies have PBI desktop in the software center even if software generally is restricted. You might check there and the Microsoft store.
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?
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.
Great video, Christine, and very well explained. Is there a way to send project names for every project created in Planner (basic or premium) as a Microsoft/SharePoint list item? This will be useful for associating more metadata against those project names on an SP List. As I understand, there is no way to attach a piece of metadata/category to a Planner project.
Totally! I did exactly this last week, planning on doing a video for it but I want to make the UX a little better - maybe use a canvas app embed. If you're trying to trigger when a new project is created, this works better with Premium Planner because there's not a trigger for it in standard. Premium would be the "when a row is created in Dataverse" on the Plans table, run with the triggering user's permissions (dropdown option in the action) or a service principal. Make sure to log the project ID as a field in the list to relate the data. Using the plan name won't work unless you add a delay, because the premium plans are all created with a placeholder name until the user updates it, so if it triggers right away it'll log that placeholder name.
@@bi-ome Thanks for the prompt response. I look forward to seeing this video. For now I think you have another great video showing "Get Planner data for a LIST of Plans" highlighting the possibilities of importing Project data into SP Lists. Very helpful 🙂
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'
@@SamAnderson-y8j 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.
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?
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
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.
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.
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
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. 😄
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. :)
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 :)
@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!
Yeah, the modern experience still has some issues - I think I'll go through and take some screenshots and send them to the new UI PM - he's been really good about collecting feedback, so maybe they'll just fix it. Fingers crossed!
Or, actually, it does show up for me - both in search and in the list if I click "show more" - but I have this issue sometimes where almost nothing shows up in the list in modern, and then I can't reproduce it when I go back later to try to take screenshots? I'm not sure what it is that I'm doing but I totally believe you that it wasn't there. I had the same issue with env variables at one point.
Yep! The User ID is in the _Assignments in "get tasks" - we use that to get the assigned-to display name in this one: th-cam.com/video/q7bkWpVczz0/w-d-xo.html
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
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. :)
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.
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 :)
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.
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. :)
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?
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.
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
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.
@@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.
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.
@@Ananya-i8g 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.
You can’t with standard Planner. But you can with premium Planner for the Web using direct query to Dataverse - I have another video on that in the channel
@@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 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).
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
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
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!
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. 😅
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?"
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.
![ละลาย (LALALYE) - DAOU PITTAYA ต้าห์อู๋ พิทยา [OFFICIAL MV]](http://i.ytimg.com/vi/wo6r9TgausI/mqdefault.jpg)
If you are looking for assigned-to name, buckets, subtasks, or descriptions, see this video! th-cam.com/video/q7bkWpVczz0/w-d-xo.htmlsi=XeOXFuHizwUNNS-d
Each of your videos are gems. Thank you Christine
aw thanks!! 😄
Definitely one of the best Power automate videos on the web. No missing steps, straightfoward and to the point. Keep making great content!
Thanks so much, that means a lot to me to hear :)
I am usually pretty silent on TH-cam, 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!
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…
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.
This was an amazing video. You made this sound so easy. I'm thankful you took the time to make this video.
This is just what I was looking for 🙂Thanks Christine.
Awesome!
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.
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. :)
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.
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.
@@bi-ome Thank you! And thank you for all your great videos!
Very cool. Thanks Christine. 🎉
Thank you so much for sharing, great video, easy to follow :)
Glad it was helpful!
great video!! is it possible to show the labels of each task in power bi?
Curious on this as well
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: th-cam.com/video/q7bkWpVczz0/w-d-xo.html
Great video, I managed to import it. For those using professional access (company account), I had to use an organizational account to release the font.
Release the font? I don't know what this means, but it sounds awesome.
@@bi-ome Sorry for complicating things. When I say source, I'm talking about the .json file that is saved within Sharepoint.
Thank you so much, great video!
I could not find "body" as dynamic content in the create sharepoint file options. Does anyone know if PowerAutomate has changed its way of working?
You need to use the search to find it, and you need to disable modern experience with the toggle. It doesn't show in the menu :)
@@bi-ome I have the same problem, I can't find "body" as dynamic content , even if I use the search bar, can you help me :) ?
@@justinelelay6762 Make sure you have the modern UI toggled off in the toolbar - some things are still not showing up in modern 🙃
Hi, I have the upgraded version of planner in teams, "project". I can create the .json file for tasks for my old system, but not since I moved to projects.
The technique to get the data out of that one is totally different but much easier - I have a video on it here:
th-cam.com/video/F-RrxxUC_Q4/w-d-xo.html
… maybe I shouldn’t call it easier, it’s more direct though haha.
I have an issue with the upload process in Power BI. I received an error message stating, 'An unexpected character was found in JSON input.' I checked my JSON code, and it appears correct. Do you know how to fix this?
That's weird, I would try pasting it into a JSON validator and see if that narrows down what the character is that's causing problems. You can potentially just replace those in an expression in Power Automate if you need to, but you need to find out which characters it doesn't like to do that--
@@bi-ome Hi, thanks for the response... I was checking and the JSON is ok but the error that I' have is when i paste the source link from sharepoint. When I do that, the system give me the "An unexpected character was found" error
@@felixrodriguez2724 The source step where we update the path shouldn't care about the JSON content, it's the expansion step that looks at the JSON input structure so I'm not sure why you would get that message there. Make sure you don't have any characters like #, $, /, etc in your filename?
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!
Yes, there is a part 2 video linked at the end of this one and in the description that gets the details. :)
This is a great video, thanks so much!
wow great work, thank you Christine! I don't understand how there no connector for sharepoint file, you'd think that would be a given but the workaround was brilliant, thank you once more
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
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
@@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.
@@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.
@@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!
@@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.
😅
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
Make sure you’re in the classic editor, toggle modern off - it doesn’t show all the cards for some reason
@@bi-ome it worked! Great! Huge thanks 🙌
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?
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. 🙂
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.
Are you in the classic editor? A lot of the dynamic content cards don't show up in modern.
thank you for the video. Do you know if it is possible to connect multiple planners across multiple teams into power bi?
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: th-cam.com/video/mqGFbIGpCcw/w-d-xo.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).
Can you do this without Power BI Desktop, I am limited to Power BI Service and unable to get the JSON file. My choices for import are excel, csv, manual, and template. Thank you for any help you might offer.
You could do it with Excel, but it would be quite a bit harder to make the flow for it. Do you have Windows? Most companies have PBI desktop in the software center even if software generally is restricted. You might check there and the Microsoft store.
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?
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.
Great video, Christine, and very well explained. Is there a way to send project names for every project created in Planner (basic or premium) as a Microsoft/SharePoint list item? This will be useful for associating more metadata against those project names on an SP List. As I understand, there is no way to attach a piece of metadata/category to a Planner project.
Totally! I did exactly this last week, planning on doing a video for it but I want to make the UX a little better - maybe use a canvas app embed. If you're trying to trigger when a new project is created, this works better with Premium Planner because there's not a trigger for it in standard. Premium would be the "when a row is created in Dataverse" on the Plans table, run with the triggering user's permissions (dropdown option in the action) or a service principal. Make sure to log the project ID as a field in the list to relate the data. Using the plan name won't work unless you add a delay, because the premium plans are all created with a placeholder name until the user updates it, so if it triggers right away it'll log that placeholder name.
@@bi-ome Thanks for the prompt response. I look forward to seeing this video. For now I think you have another great video showing "Get Planner data for a LIST of Plans" highlighting the possibilities of importing Project data into SP Lists. Very helpful 🙂
Great video, thank you!
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'
If you open the SP file, does it have the same content/structure to it it that the other file had?
@@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
@@SamAnderson-y8j 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.
@@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!
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?
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
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.
Is there a way I can get the Labels from MS Planner also into PBI?
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!
Epic video. Thank you. @@bi-ome Any updates released yet for labels?
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.
Make sure you toggle off modern UI in the toolbar. It’ll keep a bunch of things from showing up-
Great video! Very helpful indeed! What happened with the part 2 video? Can't find it and it was here before :(
I re-recorded it yesterday to streamline and add subtasks - uploading it later today as soon as I finish editing :)
Here's the updated part 2: th-cam.com/video/NdkIbJCkgOs/w-d-xo.html
@@bi-ome you are the best!
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
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. 😄
@@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.
Hi! Does this provide live planner data, or just the data within planner at the time of export?
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. :)
This was so helpful!
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 :)
@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!
Awesome, great content! I already subscribed, keep up with the excellent work, please! (:
FYI - I found that with the new designer (as of Nov 20, 2024) 'body' does not show up in the dynamic field and you must change the view to classic.
Yeah, the modern experience still has some issues - I think I'll go through and take some screenshots and send them to the new UI PM - he's been really good about collecting feedback, so maybe they'll just fix it. Fingers crossed!
Or, actually, it does show up for me - both in search and in the list if I click "show more" - but I have this issue sometimes where almost nothing shows up in the list in modern, and then I can't reproduce it when I go back later to try to take screenshots? I'm not sure what it is that I'm doing but I totally believe you that it wasn't there. I had the same issue with env variables at one point.
@ interesting, I tried deleting and rebuilding the flow twice and it only worked in classic. Oh well, I got it to work.
Great video! Is there a way to get the assigned to User ID through?
Yep! The User ID is in the _Assignments in "get tasks" - we use that to get the assigned-to display name in this one: th-cam.com/video/q7bkWpVczz0/w-d-xo.html
Hi , can you give the code from the custom column please
When creating a file I don’t see power BI … can I use share point site in site address
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
when I download the file , and try to get data using json file it's not appear! where do you think the issue is?
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. :)
Thank you for your content, must have take you some time to figure out the fiddly nuances!
😄 yes!
This is seriously clever.
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.
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 :)
Is there a work around for mac users?
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.
For some how the excel file is corrupted when I create an excel file via Power Automate
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.
Can group ID and plan ID be same ?
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. :)
Awesome video!
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?
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.
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
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.
@@saadnasr2373 I had the same error. How did you find and fix the typo?
@@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.
can only json file be used like you did? Can't we use Excel file?
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.
@@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
@@Ananya-i8g 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.
A very good video. (y)
Thanks!!
Hi its very helpful, can you try to pull the assigned to name .
Yes, I am planning on doing that soon :)
How to connect live data
You can’t with standard Planner. But you can with premium Planner for the Web using direct query to Dataverse - I have another video on that in the channel
I have errors in the overdue column
Yeah sorry you have to provide more context if you want help with that, like your formula and/or the error message
@@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
*******************************
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
@@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).
@@bi-ome All the columns are only dated
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
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
@@bi-ome thank you'
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
Thanks for sharing!!
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!
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. 😅
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?"
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.