Sure, here you go: github.com/chpayton/BI-Files/blob/main/Misc/AdventureWorks%20-%20RLS.pbix I just did a video today on how to get this same database into a free SQL server and connect in PBI if that's helpful - it's in my recent video feed

Thanks! If you edit this section here at the beginning of the JSON code and change the value from Months to Days, it will default to days: { "name": "startGrain", "value": "Months", "description": "Days, Months, Years or All" }, For the starting around today's date, I did another Gantt video a ways back with a different visual where we use filters in bookmark buttons to show just upcoming, the same thing would work on this one: kzfaq.info/get/bejne/nM1zis-JsduRk2g.html I am sure there is a way to do it with just the Deneb code, but I'm not sure what that would be at present. :)

@@bi-ome Thank you so much!

If you have a list of users, you don’t need the profile search. You can just put the “get user profile” action in a loop on your list and put their username in the dynamic content for the “get”. If you need to put the data somewhere, you can put an “append to array variable” step in the loop to get it all in one variable.

Yeah, you can hide fields in the form by enabling content type management in settings and hiding them in the content type settings. I think I show that in this video: kzfaq.info/get/bejne/gtSca8183Ze4eH0.html There’s also a new button in the toolbar for modifying forms, I think Reza did a video on it - it looks fancier. It just came out a month or two ago.

I would expect it to take over 10 minutes for that many, the for-each loops take a long time. You might try setting the degrees of parallelism to 1 in the settings on the loops - slowing it down actually speeds it up because it keeps you from getting throttled. Are you looking for the append to members array text? That one is this: { "upn": "@{items('Apply_to_each_group_member')?['userPrincipalName']}", "userId": "@{items('Apply_to_each_group_member')?['id']}", "groupId": "@{items('Apply_to_each')?['id']}", "userRole": "Member" } Append to owners array is this: { "upn": "@{items('Get_owners_for_each_group')?['userPrincipalName']}", "userId": "@{items('Get_owners_for_each_group')?['id']}", "groupId": "@{items('Apply_to_each')?['id']}", "userRole": "Owner" } The compose step after that is this: [ @{variables('groupMembers')}, @{variables('groupOwners')} ]

@@bi-ome Thanks for your support. I managed to get it to work after 58 minutes of running! It gave an error. =) However, interestingly, I got my export. Both are in SP (I can use data in Power BI), and one file shows me over 11k rows even though I put a limit of 1000. How is that possible? This is an error: (all the steps are in green..) ActionFailed. An action failed. No dependent actions succeeded. p.s If I use parallelism of 1, I am getting just some users and groups, which is wrong.

You're welcome!

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.

@@bi-ome Wow I didn't expect such a quick response on this! Thanks for the description! I saw another video linked below and there's a nice demo of you doing the formula :) Amazing video and super clear steps to follow. This is something I've been struggling with for months, and finally following your process got my data looking super organized and easy to manage. <3

@@alit7846 aw thanks!!

Thank you!!

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: kzfaq.info/get/bejne/Z7uWq6iFxtLGaXk.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: kzfaq.info/get/bejne/o9d3ecV6rNank6s.html and specific plans: kzfaq.info/get/bejne/Z7uWq6iFxtLGaXk.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.