Відео

Yes? I would not generally do this for one handful of PDFs, it's just an example. 🙂 This will work on any PDFs that have selectable text in them, because the expression is just getting text next to or above or below other text, but not scanned PDFs.

There is! It's not in the dynamic content cards, but you can get it from the outputs by referencing the name of it directly. I go back and cover priority in... I think part 3, but maybe part 2 of this series-- missed in this first one for the lack of card, lol

Not sure what you mean, if you know what folder you want to show then you know the path you want to filter on, don't you? You can just type it in as text with equals or contains. It's going to match the folder name in SP, so if you're not sure what the folder name is there you can just go look at it in SP? If you want to combine the file contents and the file metadata, you can do an inner join between the SP Folder and Odata on the filename and path (do this before expanding the file contents so that it folds for speed).

@@bi-ome The problem was that the specific path didnt show on the filter list, since there were too many files. I ended up first filtering to folders to reduce load, then picking any path for the filter, then editing it out and remove everything after "each", replace it with Text.Contains(on defaults uses equal) and using the folder path i needed. The problem is Power Bi/Query wont let you connect to subfolders directly, so it has to load the entire site first, then filter what you want, wich is not optimal, later doing data refreshes makes them take way too much time. Here i have been waiting a couple minutes for the query changes to import into Power Bi.

@@freedomdst Righto, there's a menu option to filter on specific text with contains - it's under the "text filters" submenu when you click the column - you don't have to use the checkbox list :)

also find it silly microsoft have all these products and they still want to jump over hoops to get them connected

I don't think the comments are in the API at all. If you find something to the contrary let me know!

@@civilservant4843 It's because they want you to buy premium Planner, there are less hoops on that one. :)

Thanks for sharing!!

Thanks yeah - I have started doing resolution zoom on all the new videos. Can’t fix the old ones retroactively :/

You mean CSV files? I would look for a different tutorial on creating CSVs, this technique is for embedding HTML tables. You can add CSVs as attachments in the advanced section of the "send an email" action, but this tutorial doesn't create a CSV so don't try to attach the HTML table :)

@@bi-ome Then let me rephrase my question: How can we send 2 HTML tables in the same email?

Thanks! This wouldn't be super feasible to do... you don't have access to the history, so you'd have to store it yourself, and there's no trigger in Power Automate for when a task is modified so you'd essentially have to take snapshots on a schedule and then figure out the differences between snapshots in your PBIX. Not for the faint of heart for something that is more in the "nice to have" category. But technically with enough jank, most things are possible?

​@@bi-ome You're right! I have access to the history but too much unnecessary data is created. As far as I researched, the best option is to review the activity log history to check changes. Thanks again for the resources and keep up the great job!

​@@paulo.valverde Did you ask on Reddit? I think I am the one that responded there if you did lmao. This video series assumes you have non-premium Planner. If you have premium, you don't have to go through all this junk with the flows - the tables are just in Dataverse, and you can connect to them directly with Power BI with the Dataverse connector. When you do that, it lets you scroll through the list of tables (they will be in the default environment) - one of them has the tasks, there's another that has the assignments, and another has SOME history (not a ton). I touched on it really briefly at the end of this video: ua-cam.com/video/PhTwhN7m6Lw/v-deo.html - it's on my shortlist to do another with more detail but I've not been getting to it. Honestly the audit log route is kind of iffy with this - it would involve changing table and column settings as a sys admin with no option of a dev environment, unless you try it in a separate tenant first. I would check the out of box history table first and see if that works...

@@bi-ome That was me asking on Reddit, yes! Thanks for your patience 😂 I’m still new to Power BI (coming from Tableau) and Planner so any type of information is super helpful. I did watch the video and it helped me understand where in the Dataverse was the data, and watched your series to understand it more and for inspiration! I was able to connect to Project History but honestly, there are so many columns and data that deciding which ones are specifically useful would take a lot so I’m not going to use it for now lol

The update video here has a days/months/years axis. If it behaves like most, I believe it should "stick" to whatever you have it zoomed to when you published, but I haven't double checked to verify (if you try it and it works let me know!): ua-cam.com/video/5w1tdoCvyPM/v-deo.html

Thank you, It doesnot default to months with the zoomed in value. I tired multiple ways to debug, It doesn’t work. Please let me know if it works on your end.

Just an update, was able to default the visual to Months and was able to add Hyperlink to external application on the Task column

It's very picky about having all of the fields it needs - I would check your date fields and make sure they're date type (not date time), and that the progress is numeric.

@@bi-ome It was the date hierarchy - you are wonderful btw - thank you for doing this. this is the best

@@jasonknotts2061 Ohhhh the date hierarchy - that makes sense. If you want to do yourself a favor, you can disable that entirely in the report settings for this file and all future files. The date hierarchy is possibly the most hated feature in the history of Power BI (date table 4lyfe 😄). Thank you for your kind words!

@@bi-ome one more q - do you know how to make the # of days in the Days column be Workdays only?

@@jasonknotts2061 I'd be inclined to just remove the days column entirely and replace it with a new column that uses a measure with CALCULATE() and date table filters to remove weekends/holidays. But I am more familiar with DAX than with Deneb JSON templates. Not sure if you're working with a date table, but they are 100% extremely useful for this sort of thing-

1. In this case the variable name was Buckets, but inside the array we stored the bucket ID dynamic content in a field we called bucketId. When you create the array, whatever text label you put to the left is going to be what that "field" in the array is called. The filter is filtering the bucket array by a specific bucket ID, so we need to feed it the info about where the bucket ID is stored (so what we named the field). We give it the dynamic content card for the Buckets variable, then the field name for the bucket ID for the filter. 2. To do one group, you don't need to get or loop over groups at all - just start with the "list plans for a group" action and select the group from the dropdown menu. :)

Good question! Power Automate Cloud does not have regular expressions and pulls the file content via API, meaning it comes in as base64 (garbled random letters). In order to do anything with it, you have to either translate it out of base64 (which requires a third-party connector that is not free) or use OCR on it as if it were an image to "recognize" the text and concatenate it all together. Then you'd use another AI connector on it, typically AI Builder or Azure Open AI, to parse the text and pull out the parts you want to put in Excel. Or use SharePoint Premium, which basically packages the above into a solution for you but is more expensive. So, long story short, since Power Automate Desktop is installed on most Windows machines and is free to use locally, it's often much less of a pita and less expensive to just use that vs cloud for this particular job. Power Automate Desktop essentially is just a robot that controls your computer where you can tell it what actions to take, so it bypasses the base 64 issue and allows regular expressions so you can extract without AI. But it's technically possible to get the same output with either.

@@bi-ome you are right. Currently trying AI builder method. Again, thanks for the informative content.

The comments aren’t in either these actions or the API to my knowledge :(

OneDrive is basically a personal SharePoint site, so you can use that the exact same way. But, SharePoint is a better place for it - you can set the site permissions so that only HR has access. It's not public by default or anything. OneDrive is usually not recommended for business continuity reasons because if you leave, your OneDrive gets deleted with your account.

🤣 I hear you!

It’s technically possible, but not less work. You have to create a flat data structure, which means concatenating all the multi value fields into strings, then get it into one card (probably append to array would work here) and then feed it into the create csv action, then create the csv file with that in SP

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'.

Glad you figured it out!

It's always the typos with the hand-typing JSON! :)

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.