Видео

Not sure if this was related to this video or another one - this one is about moving pages? I do have one on using PBI with SP lists, where there's some ticketing reporting... to count tickets there you just do a COUNTROWS() DAX on the table that your tickets are in. The count by user/date/etc is all using the same measure - it's the dimension relationships that group it up on various things.

What's the reason for sending it to the SharePoint list? Wondering if you can replicate whatever that functionality is without syncing the data back and forth. The issue with trying to keep the tasks up to date in both places is that you have to handle new tasks, updating tasks, and deleting tasks that get deleted in the source. It's better to have the single source of truth be in one place so you don't get people trying to edit from the SP end expecting it to go the other direction as well. If you strongly prefer the SharePoint UI, you could also just bypass Planner entirely and use the list to store the tasks. You can add your own email reminders with Power Automate etc. I would not try to do both or try to sync to both, personally - its more trouble than its worth and there is not a huge benefit. :|

@@bi-ome Well.... we need to keep track on planner for the people who are working the tasks. The sharepoint list is for people that we dont want to have access to the list (they always seem to do weird things when they have access). We need to have the submitter information and data that is only captured via the form. So the SP List is a combination of both the form and planner task status. Im not sure how else to capture the information :'(

@@karenchambers79 oh, are you trying to essentially use it as a ticketing system then? If that's the case, I would do away with the Planner bit. You can use just the list, or if you want to get really fancy you could put a Power App on it. That way, they can submit there directly (there's even a new form feature for this in lists), and you can set it to "users can only see/edit their own items" if you want to prevent them from screwing things up. I did an article on this a ways back if it is useful at all: christine-payton.com/request-managment-in-m365-lists/

@@bi-ome I wish it was that easy, my team is currently running tasks for 12 different planners, using buckets as different steps in the process. We need to be able to provide a report (The List) that has the status and step (bucket), who its assigned etc. The report also needs to have who submitted it, date time etc so they can filter through. The only way Ive been able to do this is, a form is submitted that data gets fed into planner. Once the task is created the information from the form is sent over to SP list with the taskID. I was using the taskID to connect the planner to the list... but it isnt working now. Im losing my mind slowly. Also side note, I have no idea what Im doing. I look up tutorials and sift through forums to try to accomplish what my brain thinks can be done. Other than that... I know nothing lol

@@karenchambers79 Right, you're going to have a hard time the way you're trying to use them together in this instance. Not only is it technically difficult but the tools are going to fight you, because they're not really designed to be used this way - so it's going to be crazy-making. I think what you need is a single tool that is designed to do whatever the end goal is that you're trying to do. Planner should not be a starting point for this sort of thing - you want a system designed to handle records and relationships between different record types, so either something out of box that already does that, or a Power App on Dataverse or SharePoint is probably going to be your best bet.

Never mind. I'm a newbie to power query. It's case-sensitive. 'In' is not the same thing as 'in'.

How you set it up depends on how much manual intervention you're ok with. The least intervention would be the SP folder connector for Power BI, where it will pull all files in a folder. You can add filters to that for the file type, path, naming convention (e.g. contains "survey"). As long as you're using "unpivot other" and selecting the fields that will be the same every time when you unpivot the rest, it will be able to pull in all the data into one table. You'd have the filename associated with the survey, which should have the survey name in it to help tell responses apart. If you wanted to transform that to be a bit friendlier format you could split off the extension or something in the query editor.

Thanks! I would just add the second column to the table, update the query in the flow, and drop the second email address column in the CC on the email (or semicolon after the recipient). As long as there’s always someone in the first field, that should work.

@@bi-ome Thanks for the response! I think I understand what you are saying, but my problem is that the second email will not always be paired with the first email. For example, user 1 could be paired with user 2 on two orders, so one email total would be sent to both of them. Then user 1 could also be paired with user 3 on an order, so one email total would be sent to both of them. In total, only two emails should be sent.

@@TJKubiesa If you are trying to send them a record set, and the second email address field is not the same every time for a single email recipient 1, you would need to just send that second person a separate email with "their" records assuming don't want them to get some stuff that's not their own (just duplicate the table/copy query - can do a new flow or put it in the same flow).

I'm not sure what you mean, like attach an .eml file? That would be the file type of the email. You might try something like this, but attach it to the task instead of SharePoint: powerusers.microsoft.com/t5/Building-Flows/Flow-send-a-mail-and-save-it-as-EML-in-sharepoint/td-p/897748

The usage data isn’t super accessible to us - my understanding is you pretty much have to get it from the activity API, either with PowerShell or another tool that makes API calls, and store it somewhere yourself (the data only goes back a short time, so it takes a while to build the history). You need pretty high permission in M365 to pull that too, something like compliance admin or global admin. 😅

That’s what we do in the video. We exchange the default colors for our own colors here - just keep following the same pattern for however many buckets you have using the hex color codes.

I think it’s alphabetical by default, so super easy way is to just tack a number onto the front to force the sort. Otherwise you can try setting a custom sort order by sorting the bucket column on another column in column settings (so add a second custom column with numeric values and sort on that).

Does it give you the columns to expand? If not check your step that is appending to the assignees variable and make sure you both have it structured the same and have the right dynamic content inserted. Typos in the array will break it too.

@@bi-ome I went back and watched everything and saw I missed one teeny detail "," its fixed and running! Thank you SO much!

Thanks! Always Segoe UI haha. Semibold or normal, never full bold. This feels like a twist on a coffee commercial now…

@@bi-ome thanks! For some reason your segoe looks better than mine. Ha!

Glad it helped!

Yes, I think that is covered in one of the videos - at least for the tasks - I can’t recall if it was this one, will try to find it 🤣. You can use the same technique for the plans though. We use the plan and task ID columns to concatenate a link to the task in Power Query; you can use that with conditional formatting settings in the table to make the task names clickable.

Okay so I might be losing my mind, I swear I recorded this but can’t seem to find it in any of the videos - all you do is open one of the tasks in Planner and copy the URL, remove the plan and task IDs from the URL and insert the fields in their place in PQ. Double quotes around the strings, & symbol between that and the column inserts to concatenate. If you put it in a matrix you have to use the url icon feature, table will let you make the task names clickable instead.

Awesome!

Ha! Thank you for your kind words. This process also almost broke me, I had to re-record and republish it at three times figuring out all the pieces to get it right. It frustrates me that it’s this complicated as much as it does you, trust me!

@@bi-ome I totally trust you. Its clear there's no more or less process info here than is needed. Its so well presented. And so hard for the novice to follow- Power Automate is not easy. The persistence pays off though, so thanks again for your willingness to share this info!

Turn on pagination in settings for the “list tasks” step and set the threshold above your total-

@@bi-ome That's what I thought, but it won't let me insert it... Thank you very much!

@@bi-ome I was doing something wrong, it accepted the value now... thanks again...

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

This is under the Append array variable

Sounds like your JSON has a typo or something missing. There are JSON validation tools online, you could try pasting it in there and see if it will pinpoint the place where the issue is

Make sure you're not using the modern UI - there's a toggle to turn it off in the top toolbar. A lot of the dynamic content isn't in modern. Also make sure it's nested in the same place as in the video, if you have it misplaced inside or outside the loop it needs to be in it won't show up--

@@bi-ome Thank you Christine, it was nested wrong so it couldn't show up.

Yes, I go through that in the Part 3 video - it should be linked in the description of this one :)

thanks! 😄

It's yourtenant.sharepoint.com/lists/listname, but if you are moving from Excel data to list data the structure is likely to be very different so you'll probably have to redo a lot of the query steps. They're not interchangeable like the file paths, and the references likely won't map properly... switching file paths works because the underlying data structure is exactly the same.

It sounds like your query is returning blank - does the account that's running the DAX in Power Automate have permission to view the report? I would double check that the DAX is actually evaluating

@@bi-ome It is all through my account. I fiddled with the filters on the visual in the end and got it to work. No idea why it wasn't working before but happy it seems to be working now! Thanks so much!

Personally I would just tack on another column to the same table we used in this tutorial for your second table, and use the same technique. That way when you query your dataset, you have both tables available to you as dynamic content cards in the same output - you can drop them both into the email in the loop. When you update your visual, make sure to re-copy the DAX query and paste it into the "query a dataset" so that it gets both of the table columns. :)

@@bi-ome That makes sense. Thanks for your help.

Thanks! A csv is a file with all the data (comma-separated values) in one row - it’s only when you open it with Excel that it’s a table. But the ‘create CSV’ action will create a CSV and you can use the create file action to create the csv file in SP. Make sure to remember to put .csv at the end of the file name in that step. If you’re making a CSV, don’t use HTML tables like in this video btw, just use plain data rows. :)

@@bi-ome Yesterday I was finally able to create the CSV with a table inside it! I used a combination of 3 videos with one of them is yours! Thanks so much! I love your on screen notes! Very helpful!

Answered this here, cross-linking in case someone else has the same question: www.linkedin.com/feed/update/urn:li:activity:7198339576996720641?commentUrn=urn%3Ali%3Acomment%3A%28activity%3A7198339576996720641%2C7204854094455287812%29&replyUrn=urn%3Ali%3Acomment%3A%28activity%3A7198339576996720641%2C7206439406449090560%29&dashCommentUrn=urn%3Ali%3Afsd_comment%3A%287204854094455287812%2Curn%3Ali%3Aactivity%3A7198339576996720641%29&dashReplyUrn=urn%3Ali%3Afsd_comment%3A%287206439406449090560%2Curn%3Ali%3Aactivity%3A7198339576996720641%29

Answered this here, cross-linking in case someone else has the same question: www.linkedin.com/feed/update/urn:li:activity:7198339576996720641?commentUrn=urn%3Ali%3Acomment%3A%28activity%3A7198339576996720641%2C7204854094455287812%29&replyUrn=urn%3Ali%3Acomment%3A%28activity%3A7198339576996720641%2C7206439406449090560%29&dashCommentUrn=urn%3Ali%3Afsd_comment%3A%287204854094455287812%2Curn%3Ali%3Aactivity%3A7198339576996720641%29&dashReplyUrn=urn%3Ali%3Afsd_comment%3A%287206439406449090560%2Curn%3Ali%3Aactivity%3A7198339576996720641%29

Aw thanks haha!

Thanks! Not that I could find… which at that point makes me kind of just want to make my own app, and use Power BI for the Gantt, but that’s a can of worms. Someday if I ever have massive amounts of free time…

Yeah, if you're using a date table or something you can filter out dates. So if you make a measure or calc column that gets the duration you want, you can just slot that in place of the old one, just make sure it's named the same thing.