วีดีโอ

Yeah, I had a recording blip and so I had added the info in an on-screen text - it's a calculated column, the formula was Stakeholder Names = CALCULATE(CONCATENATEX(Stakeholders, Stakeholders[Stakeholder Name], ", ")). You can add that on your main table and it'll concatenate the values from the related table (make sure you have the relationship from the earlier section). There's not a non-convoluted way to do it from Power Query or via the UI, the DAX calc column is the easiest method-- CONCATENATEX is an iterator, meaning it iterates over your main list table and concatenates the related values in the stakeholders table. You keep the two tables separate and relate them on your item ID - this lets you use the individual names as filters in slicers.

Best I can do is to the right of the bar - if you drop a field into the "Resource" well you can put text there. You can't customize a whole lot with this, but there are other Gantts made with Deneb where if you can do some coding you're able to control display more.

Yeah, I did a blog post here: christine-payton.com/how-to-use-david-baccis-gantt-deneb-template/ I just went in and updated it now for the 2.0 version of the Gantt (it was still on 1.0). The code chunk you want is at the very bottom, if you just copy/paste it in and overwrite yours it should work. The header positioning is a bit funky but I'm out of time today :)

@@bi-ome dude. You're amazing and fast. I'll check it out today and see what I can tinker with.

Good question! It's a normal slicer, so the one with the funnel icon in the visuals menu on the right. The thing that makes it a date slider is dropping a date-type field into it. There's different options like "relative" etc in the visual config menu under slicer settings--

Thank you!!

Have you tried dropping an example of the array output into Chat GPT and asking it for a formula for Power Automate Cloud? I tried that and it says to stick this in a Compose action: json(concat('{', join(InsertOutputHere, ','), '}')) Otherwise you could parse the array first and use that in the concat instead if you need more control--

Yes, that's how it worked in the example too, and we had to split off the part it was searching for to be left with the remainder. Lookaheads and lookbehinds are how you usually avoid getting the extra junk you don't need, but as far as I can tell Power Automate doesn't support those. Is there a character you can split on to remove them? You could try something like splitting on the line break ( ) and then getting the last value in the array that creates, that should leave you with just the text on the last line.

@@bi-ome thank you in getting back to me. I managed to solve the issue by using this expression "(?<=Exchange rate .* .* ).+" my extraction is working brilliantly now and have to thank you again as most of the code is based off your video. Look forward to watching the remaining series as well your future posts

@@TTtipsforlife Oooh clever, thanks for sharing and for the kind words!

From the Apply to each List Group members it seems as though the first Group Id is blank, would this cause the HTTP request to return only the odata.context and value?

Did you possible paste in the content as the example and not the body output? There are a couple of boxes in the flow history for the step, make sure to copy the right one. The schema I used was this, in case you want to just roll with that: { "type": "object", "properties": { "value": { "type": "array", "items": { "type": "object", "properties": { "id": { "type": "string" }, "displayName": { "type": [ "string", "null" ] }, "givenName": { "type": "string" }, "surname": { "type": "string" }, "userPrincipalName": { "type": "string" }, "@odata.type": { "type": "string" } }, "required": [ "@odata.type", "id", "displayName", "givenName", "mail", "surname", "userPrincipalName" ] } }, "@odata.context": { "type": "string" } } }

@@evealsdorf7402 Possibly - definitely feed it an example with data in it! I am not sure how it could have a blank group id, that's strange.

So glad!

I don’t recall, maybe 15 minutes at the most? Make sure the action that sends the mail is using the connection you granted permission to (ellipsis menu)

Make sure you are using "append to array" variable, not "set variable", and that it's in a loop on buckets. If you look at the flow history, you can click into each step and see where it's going missing - so check the "list buckets" output and make sure they're all there, then check the append step and see if they're all there, and so on--

@@bi-ome I managed to solve this! Can you tell me how I can pull the subtasks and whether they are completed or not, bringing all the tasks, whether with subtasks or not?

@@bruno.jeronimo The technique in the video gets all subtasks for each task, whether they are completed or not. Part two in this series shows how to display them with check marks next to the completed ones--

I'm not sure if you're trying to extract a structured table that's already in the format you want, or if it's less structured. If you're simply extracting table columns as-is, Power Query can do that on files or folders and just yank in whatever you want to Excel (it has a PDF or folder source option). The technique in this video is meant to be used where the structure is irregular or where the content is mixed with other text. Regular expressions can pull out item codes as long as they follow a particular format (e.g. 3 letters, hyphen, 3 numbers, or any kind of very regular pattern). If your item codes etc are not super formulaic, you might have better success with an AI tool. There's a bunch of different ways to do that; I'm partial to just sending the text to Azure OpenAI with a prompt and asking it to extract whatever you want and send it back as JSON with a particular format. If you give it an example of the desired structure, it'll be able to send it back to you in the same format, and then you can expand it as a table with Power Query. That will let you extract a handful of things at a time with good structure to it.

The subtasks are in the flow we made, if you have them in your JSON and skip ahead to the part where we are setting up the queries in Power BI - we put the subtasks in a separate table from tasks and then create a relationship between the two tables. It is like a parent/children object relationship. We can concatenate the info from the subtasks or count them that way. It has the “is complete” true/false value in there. Comments are not available unfortunately :/

@@bi-ome Yes, I managed to bring the data from the subtasks. I was getting the detail of expanding it wrong, I hadn't understood that it was left as a list. My bad! Thank you for teaching so well and always answering people who have questions! You are an excellent teacher!

That's a good question... I found this thread where someone has a similar issue - they're using another tool but I think the same logic applies, where you can flatten or print to pdf to make the text readable. There are third-party tools that can extract field data too, but ofc they will have some associated cost. It's not super straightforward. www.reddit.com/r/excel/comments/16u54ah/how_do_i_extract_data_entered_by_a_user_into_a/

Not sure what you mean?

Yeah, you could do a single select slicer and just drop the question field in there. That would filter the responses by question. Is that what you mean?

No, it shouldn't, notifications have to have a trigger. Even the on-modified triggers wouldn't trigger off of a copy, because you're not modifying the page.

@@bi-ome thank you 😊

🤣 me too, I have a garden so got it on the brain haha

Yeah!! That's great to hear. I picked this up YEARS ago from I have no idea where, and actually forgot what the exact syntax was when I went to do it again before making this video - I tried to search for it on the internet and couldn't find anything at all. I managed to dig it out of my notes, though, and that's why I made this - it's so obscure haha. 😄

Thanks you! You too, best of luck on your work :)

Yes, because you would have to manage the updating of each item, deleting deleted items, and adding new items each time it runs in the flow, and to do that you have to run a series of condition handling and checks in addition to the update steps. The reason we send it to a single file here is that when you schedule it, it'll just replace the file and update everything in one go. The benefit of Lists would be that it'd give you a place to edit the records, but you can already edit the records in Planner, so I'm not sure what advantage the list is serving in this particular case?

Hello! If you have more than 500 or so tasks, you will want to turn on pagination in the "list tasks" settings in the ellipsis menu and set the degrees of parallelism on the loops to 1. This will get all the tasks and avoid throttling errors. But, if you have a TON of groups, you might consider only getting the specific plans you need instead - I did a follow-up video on that here: th-cam.com/video/1UfxETalb9E/w-d-xo.html

@@bi-ome Thanks so much for your quick reply! I'll take a look at that video. I have about 20 channels, each with 1 planner list which can have anywhere between 5-200 tasks in it.

The comments aren't available in the API, you're right. The least annoying but also least helpful option is to add conditional formatting to the task names in the table visual, so that people can click them to go to the task in Planner (you can make the hyperlink a calc column and use that). You wouldn't be able to tell if a task had comments before clicking it, though. You could also do what you mention with a separate list and use a Power App visual to make the experience a bit more seamless (or less seam-full? it won't be seamless haha) Or you could use premium Planner, which I think gives you access to the comment data via Dataverse (I need to double check that... I think I remember seeing comments in there). Only the person creating the plan would need a license and it's not super pricey.

Thanks! I have more videos for getting different things - so there's one for "get all my plans that I own" and another for "get a specific list of plans". The second one uses a copy of the first flow and adjusts it to just get plans from a list of plan IDs, which should work for you. Here's links - all plans I own: th-cam.com/video/mqGFbIGpCcw/w-d-xo.html and specific plans: th-cam.com/video/1UfxETalb9E/w-d-xo.html

You can create custom connectors to any API in and use it in a Power Automate flow. If you can get an API key from Shift App, and your organization allows custom connectors in your environment, and you have a person who understands how to send data back and forth via API, it's possible! 🐿

You need to make sure that the variable action you're using is the "append to array" (not "append to string") and that your variable type in the dropdown for the initiation action is set to array. There are string variable options that look very similar, those won't work. :)

​@@bi-ome tks ! for replying!! My Variable is set to "Array" (Initialize variable). The "For each" is looking for the Bucket id and Name (List task & LIst bucket) from the previous steps, setting as value the Value Id and the Number Im using "Append to Array"... funny thing is the outcome from the filter, it's showing the Name and the ID for the bucket, like if it was working, but the step is falling with that error message and of course the flow is not continuing... :(

@@OptimusCrist Make sure there's no typos in the bucket array - it needs to start and end with curly brackets, have double quotes around each of the labels, and the one comma. That's the only other thing I could think of - if that JSON is invalid, it might be having trouble recognizing that its an array--

Thanks!!

Thank you for your kind words!!

I am also not an expert in Deneb! I know just enough to get this running basically. My technique for modifying things is to search for them in the code, then give that chunk of code to Chat GPT and ask it to modify it to do what I want. It's surprisingly good at this particular thing, probably because JSON is very standardized.

@@bi-ome I tried GPT for the part to make „close“ by default . The code changes made by the GPT looked logical but still dint get the desired output… . Thanks for the reply

If it’s always the last line in the subtotal you want, I would go for getting the line above the target and searching for whatever is directly after those subtotals (hopefully it’s something reliable!).

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.

When you have Power BI set to “scale to fit” on the width and/or height, it scales the entire thing including visuals down, so it’s not really an issue. You could do it in Power BI, too, but since we are already making a background I just put it in there. When you’re working with the more shapey layouts, it’s a lotttt easier to align things and get equal spacing in Figma than in PBI. But you can do it on the other side too. I will say that this does not work for the mobile views, but then you usually have to start from scratch on those anyway.

I don't think so! I tried and had the same problem. You might try a different Gantt, like Davide Bacci's Deneb template, if you want to be able to customize the little bits. That one is pure JSON code, so you can just search for where the timeline text is and just edit the font size.

@@bi-ome excellent, thank you! (I have that video opened in a tab waiting to be watched lol! Guess it's time to revisit. Thanks again.

Check your flow run and see if the steps that “get” data are getting any data. Then check the steps that put the data in the array towards the end, and make sure the data is making it into the array. Each of the steps will show the inputs/outputs when you expand them in the flow history.

Thanks i did followed the video Again and when i deleted the original file then the new file that was created had the data

I think it is sorting by the first start date for a task in the bucket. Does that line up with what you see? I thought it was alpha but it’s not.

@@bi-ome ill look into it, thanks!

🤣

Microsoft renamed datasets to semantic models, so you're in the right place! Are you the owner of the model? Only one person can own it at a time, so if it was published by someone else there will be a "take over" button at the top of the page. If that's the issue, then when you take over refresh you'll be able to manage credentials/schedule.

@@bi-ome I am the owner. I was able to click on the name where you see the last refresh date and I am listed as the owner. So not sure what to do next.

I figured it out! Basically, I had 1 index table to help order my AR aging properly that was linked to a file on my hard drive. As soon as I moved it over to the TEAMs folder where the other files are located, it allowed me to connect. Thanks again for the video.

@@CH-rn6nc Awesome!

The thing with paginated is it's just a type of report - it uses a semantic model just like a normal report. You can query the dataset that the paginated report is referencing the same way to get a table with data that matches the paginated report, but you can't "get" the paginated table per se. The only paginated-specific action in Power Automate is to send it to a file, not embed it, but you could use that if attaching a file is acceptable.

thanks!! I love abi toads so much, wish they made more desktop backgrounds!

Yeah, so you take your "tenant root" (whateveryoursiscalled.sharepoint.com) and tack on this: /_api/search/query?querytext='(contentclass:STS_Site)(contentclass:STS_Web)'&trimduplicates=false&rowlimit=50000". I put the tenant root in a parameter so that I could change it easily, but you can put it right in the URL if you want. It's basically just a URL where when Power BI hits it, it returns some particular data according to the URL parameters. This one does a search for sites and subsites.

@@bi-ome Thank you!

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!!