Power Automate Flow filter array to create summary SharePoint Data

  Рет қаралды 44,889

Shane Young

Shane Young

Күн бұрын

Пікірлер: 85
@simonb7556
@simonb7556 2 жыл бұрын
Another great video by Shane.
@ShanesCows
@ShanesCows 2 жыл бұрын
Thanks Simon 😎
@jonathassabatini2072
@jonathassabatini2072 3 жыл бұрын
Hi Shane! Congrats and thank you so much man, you've been helping me a lot! Please show how to integrate the planner on the power apps!!!
@ShanesCows
@ShanesCows 3 жыл бұрын
Planner is on my radar for sure.
@bellam2413
@bellam2413 2 жыл бұрын
Thank you Shane , you always saving the day!
@ShanesCows
@ShanesCows 2 жыл бұрын
Happy to help. Have a great day. 🐶
@nutriacubica
@nutriacubica 3 жыл бұрын
New Fan of yours, keep it up you have made my life easier
@ShanesCows
@ShanesCows 3 жыл бұрын
Thanks for the kind words
@jamiehicks8382
@jamiehicks8382 2 жыл бұрын
Great video! Do you have any that show how to power automate sums by date range from SharePoint list?
@ShanesCows
@ShanesCows 2 жыл бұрын
I don’t Jamie but you should be able to evolve this video concept and do what you want.
@chriswebbtech
@chriswebbtech 3 жыл бұрын
Wow.... this is so much faster than doing an Apply to Each on a get items, then using get items with odata filter in it each loop LOL. Thanks for this video, saved me some time and MS some CPU cycles LOL.
@ShanesCows
@ShanesCows 3 жыл бұрын
Thanks. Glad it worked
@ricardobunge5957
@ricardobunge5957 3 жыл бұрын
Hi Shane -- have been watching your videos for years, and recently subscribed. Excellent content in this one, as always. One thing I really appreciate about your style is that you don't edit out your mistakes and missteps (rare though they are). Us mere mortals suffer through the same moments, and it's nice to see we're not alone. A quick comment and a request -- in this video, when you update the item, I'd like to point out to readers that the pesky dropdown columns with default values can have a pretty devastating effect on your list if you don't take the time to click each one and choose Custom Value. Bit me several times when I was starting out on my Power Platform journey. Hopefully there's a solution in the works. I know you regularly request ideas for videos from your adoring public -- how about a deep-dive into stepping through arrays, objects, nested arrays/objects, etc.? You and Reza have touched on it, as have a few others, but I still stumble. Keep up the fantastic work -- you're responsible for a great many people becoming Power Platform converts!
@ricardobunge5957
@ricardobunge5957 3 жыл бұрын
By way of example to readers of my Update Item comment -- every single one of your employees now has Red as a favorite color and every one of them Does Good Work...
@ShanesCows
@ShanesCows 3 жыл бұрын
Thanks Ricardo. I do need to do more with arrays and such. 👍
@jamesclark2020
@jamesclark2020 2 жыл бұрын
Yes, you do show us how to do cool stuff.
@ShanesCows
@ShanesCows 2 жыл бұрын
Appreciate that. 😎
@andrewjameson5918
@andrewjameson5918 2 жыл бұрын
Some of that was very useful and helped me with a flow.
@ShanesCows
@ShanesCows 2 жыл бұрын
Awesome! Lots of little things to learn for sure.
@joshuadougan
@joshuadougan 3 жыл бұрын
This is a great video. I’m looking for advice on how to create a separate summary list to aggregate the results from a source list. Rather a truncate and reload each time. Something like the following: Source list Item 1-$10 Item 2-$15 Item 1-$5 Item 2-$10 Desired Result: Item 1-$15 Item 2-$25
@ShanesCows
@ShanesCows 3 жыл бұрын
I have read this a couple of times but don't have an easy answer. GroupBy and then sum the data is the answer in Power Apps. In Flow, you will need to take what this video shows and twist it a bit. But I don't have an easy answer without doing it. Sorry.
@RD-gp1mu
@RD-gp1mu 2 жыл бұрын
This is great, thank you. For multiple conditions where each one will need to update a separate excel table, do I need to create a condition for each. I'd rather be able to use a filter array but not sure then to add in the part to update the table. Any ideas would be appreciated otherwise I've a very long list of conditions. Not sure how else to accomplish this.
@claires9018
@claires9018 10 ай бұрын
Another very helpful video, thank you Shane! My scenario is slightly different... where you have updated the employee count in the original list I need to have that employee count in a different list. It already had a 'department' column with the same values - does anyone know how to use these 'employee counts' to update the second list?
@fletchergrimlock6885
@fletchergrimlock6885 3 жыл бұрын
Shane, in the vid you do the length of a filter array for department. However, if there are two departments with the same length would they be grouped as one? Thus making the count off?
@danielmacias5880
@danielmacias5880 3 жыл бұрын
Shane, thanks im a fan of your videos, i want to know if this Filter Array on P. automate have limits? i am tryng to use it for updatting and add rows in sharepoint list but seems that my excel is too large, like 2000 registers.
@ShanesCows
@ShanesCows 3 жыл бұрын
I am not sure but wouldn’t surprise me.
@Learn-zr6su
@Learn-zr6su 3 жыл бұрын
Thanks very much for this video! It's just what I searched for. I'm making an app for counting inventory, and my customer wants to see the available qty from his ERP. I have the data from the ERP, but I need to SUM the Qty instead of counting it. Is that variable = variable + Qty or is it the INCREMENT function?
@ShanesCows
@ShanesCows 3 жыл бұрын
variable = variable + Qty is what I typically do though in flow there is an increment action I am pretty sure.
@VinaySingh-ox7bl
@VinaySingh-ox7bl Жыл бұрын
Hi Shane, I have a doubt. Suppose I have 2 SharePoint lists A and B. They have a common column(let's say Key). For all the value of Key from one table, I want to use it to access the rows of other table in Power Automate. How to do it ?
@ShanesCows
@ShanesCows Жыл бұрын
You would use Get Items and then specify an oData filter to get the records that match your criteria.
@VinaySingh-ox7bl
@VinaySingh-ox7bl Жыл бұрын
@@ShanesCows tried the same, was throwing error item not found. Could you please make a video on this 🙏 🙏🙏
@Worrelpa
@Worrelpa 3 жыл бұрын
love it when you screw up, doesn't make me feel so bad when I screw up and believe me I screw it up a lot ;) Had to use the filter array for working out thing like where data was missing and account for reminders where people had not submitted data. Pieter has his blog out there now. Not read it yet, sharepains.com/2021/03/01/sum-sharepoint-column-power-automate/
@ShanesCows
@ShanesCows 3 жыл бұрын
Thanks Phil! I do enjoy leaving the screw ups. People tell me they are always some of the most helpful parts. 😂 Not sure if that is good or bad. 🤷‍♂️
@Worrelpa
@Worrelpa 3 жыл бұрын
@@ShanesCows its good because it reminds them that no matter how good you are at something you are only human and make the same mistakes they do. ;)
@pieterveenstra2997
@pieterveenstra2997 3 жыл бұрын
Thank you for sharing Phil and thank you for mentioning my post Shane.
@bootsbax
@bootsbax 3 жыл бұрын
Thanks so much Shane! I love that you leave your brain farts in the video, we are all human and happy to know you struggle too lol. :)
@ShanesCows
@ShanesCows 3 жыл бұрын
Thanks. I have found a lot of people agree with you, me screwing up is the best.
@downernzld7904
@downernzld7904 3 жыл бұрын
Good video Shane. I am attempting to do something similar but using the Filter Array on a different Array. I'm emailing a table of information to each unique email and had it all working great except Flow insisted I keep the oData info in my table. So I eliminated this by mapping out the fields I do want. Now the Filter Array will not work on my new Array. So the Flow is Get Items (SharePoint List, utilizing oData query), then Select (to just get the fields I want), ..... some more flow items to get unique emails from the list.... all works fine until I get to the Filter Array bit so I just send a HTML Table of records for each Unique Email. All works fine from 'Get items' but gives oData info (not pretty). Using the Select to map out fileds I want breaks the Filter Array. Sorry I know thiis is long winded but I can't post an image of my flow.
@ShanesCows
@ShanesCows 3 жыл бұрын
I don’t know. 😑 That is a mouthful and I see why but without hands on keyboard I don’t think I have a fix.
@marcocruz8440
@marcocruz8440 3 жыл бұрын
Your can looks good in this vid. Not sure what you did!
@ShanesCows
@ShanesCows 3 жыл бұрын
Thanks 😎
@ImGumby49
@ImGumby49 2 жыл бұрын
Hello Shane! Recently been watching your videos on various processes in Power Automate and have appreciated the information you have provided. I am new to much of this so I have a few questions. I have a Flow that generates an output of employees that have entered information into a SharePoint list. This Flow output is based on records meeting a specific date period (Filter Query). The Get Items contains multiple records by employee which then also identifies duplicate supervisor values. I have run the COMPOSE (Union), Apply To Each, Filter Array, Create HTML Table and Send Email so that I can "group" the employee records by Supervisor. My issue is that I want the table to identify the employees by Supervisor and the related count of records completed the employee. Question 1: Is this an instance where a Parent and Child Flow would be appropriate. Question 2: If the Parent/Child Flow is not necessary, is there an efficient way to obtain an output similar to the following... Current Flow Output: Desired Flow Output: Employee Created Employee Count of Records Smith 6/28 Adams 2 Smith 6/27 Jones 1 Adams 6/28 Smith 2 Jones 6/29 Adams 6/27 Essentially I am attempting to "group" Employee entered records by Supervisor based on a time period with a count of records. Then weekly email the Supervisor the summary information. Any assistance would be appreciated.
@justsimplejake
@justsimplejake 9 ай бұрын
Great video! I've managed to get the count result based on unique values but its also counting null values. In the filter array I went advanced and did the following but it errors, did you have any suggestions? @and( equals(items('Apply_to_each')?['Description'], item()?['Description']), not(items('Apply_to_each')?['Description'], null) )
@GreyDob
@GreyDob 3 жыл бұрын
Hi Shane, I want to filter my Get Items when a date in the list is older then 90 days. Kind of expired. Can't make this work, always error in query.
@ShanesCows
@ShanesCows 3 жыл бұрын
All about the odata. Try hard coded first wit a static date. Can you get that to work? Once that works then think about how to use the date expressions to get the date you want in the format.
@mardithpascua8112
@mardithpascua8112 3 жыл бұрын
Hi Shane, thanks for this. Also, do u have a filter array action but for Person Field column? I am having had time figuring out why Filter Array - is equal to a Person Field is not working.
@mustafahussainmohsin320
@mustafahussainmohsin320 2 жыл бұрын
Hi, Shane young I have applied your logic but the Filter array is returning only 100 records.. (there are multiple entries of different users) I want to update them too.. from a flow is there any help?
@ShanesCows
@ShanesCows 2 жыл бұрын
Click on the Get Items action in flow. Click the ... then click Settings. Turn on Pagination and you should be good to go.
@skyebabi7040
@skyebabi7040 3 жыл бұрын
Thaks Shane! I've been trying to use this theory to identify empty cells within sharepoint list columns. The columns are multiple choice, and it doesn't seem to want to pick up the blanks. I've tried "null" and "Empty" as part of my filter array, and I know there are blanks, but, nothin'. Any recommendations?
@ShanesCows
@ShanesCows 3 жыл бұрын
I haven't done it but guessing since they are tables you might try Length(column) and see if that is zero.
@abdrajab4700
@abdrajab4700 Жыл бұрын
How the variable keep track of each department overall wage when you clear it after each item?
@sandramcgechan5817
@sandramcgechan5817 3 жыл бұрын
I'm thinking this array stuff would be useful - we have a lot of action trackers in SPO lists, and it would be great to be able to send action owners a weekly summary email of all the open actions they own. Would be great to see something like that demonstrated!
@ShanesCows
@ShanesCows 3 жыл бұрын
Something like this? 😀 kzbin.info/www/bejne/p3jUooSgfLKopJo
@sandramcgechan5817
@sandramcgechan5817 3 жыл бұрын
@@ShanesCows Not exactly... I'd like the body of the email to be a table listing each item from Get Items, e.g. columns for Title, Due Date, Link to item. Does that make sense?
@kavyamuniswamappa
@kavyamuniswamappa 2 жыл бұрын
How to extract the data from Dataverse into xml file.. I have two dataverse tables related and I need to extract the data from both of them into xml.. is that possible?
@ShanesCows
@ShanesCows 2 жыл бұрын
Sorry I haven’t tried that one. 😞
@raj7sam
@raj7sam 3 жыл бұрын
That's just awesome function and its use. Create video on special tips and tricks and function
@ShanesCows
@ShanesCows 3 жыл бұрын
Thanks!
@sumsumtulang25
@sumsumtulang25 Жыл бұрын
Hi Shane, how to filter/capture the OrderID (latest list of rows from Sharepointlist) for the shoppingcart?
@yeshwantjangid9974
@yeshwantjangid9974 3 жыл бұрын
I have a flow that runs approx 100 times in a day and updates items in various columns in a SharePoint list. In one cycle, the flow updates 21 columns. Problem: when the list goes beyond 5,000 items the flow skips to update few values from the list of 21. When I delete old data and bring the items below 5,000 it works. Any idea how to fix it.
@ShanesCows
@ShanesCows 3 жыл бұрын
Try adding an indexed column in SharePoint for the column you are looking up items by.
@heatherfeather2621
@heatherfeather2621 3 жыл бұрын
5k is hard and fast in my experience with power apps. l spilt lists and libraries by year or some other large metadata category. Indexing and filters have rarely worked in the long run becuase tbere is always more data!
@cesarsaavedra8520
@cesarsaavedra8520 Жыл бұрын
Can we do this with attachments ? I have many of them in an item but to be sent by e mail to different people .
@headflask
@headflask 3 жыл бұрын
I vote for part 2 of your offline app.
@ShanesCows
@ShanesCows 3 жыл бұрын
I need to revisit that for sure.
@amrymukmin3419
@amrymukmin3419 3 жыл бұрын
hi Shane, this video is what I looking for. However what if, I want to update total count in another. I try what you do for update item but i change another list. But, it say that , it can't find the list. Do you have any idea?
@mikegc3816
@mikegc3816 2 жыл бұрын
Hi Shane, have you used the order By query. I have set 'get items' 'order by' to 'fieldname asc', but when I step through the apply to each it is is still in a random order - thanks Mike
@fletchergrimlock6885
@fletchergrimlock6885 3 жыл бұрын
Question about float vs integer: why not do float all the time if they both basically do the same thing? I know I'm missing something, but I'm not sure what.
@xdaniels13
@xdaniels13 3 жыл бұрын
Thanks sane! Like every video excellent! I'm trying to do something like this but instead of updating each item. I'll like to create a new list with each department and average wage in your example. Anyone any clues how to take that. Thanks in advance
@espeolguin
@espeolguin 2 жыл бұрын
Hello, I just created a SharePoint list with lookup columns, but when I try to use it in the mobile version, the look up column was not available - read only. Is there a way to make it work? Now I am creating drop down columns to replace the lookup column, but I need the correct formula or JSON code to obtain the auto populate that the lookup column was doing before. I have 34 different values that need to used and the calculated column is limited to only 7 IF statements. Any help will be greatly appreciate it
@kelvin55141
@kelvin55141 2 жыл бұрын
Hi Shane. Any advice on how to filter data that has a calculated column (Status ne 'Complete') and created date is more than 7 days?
@kelvin55141
@kelvin55141 2 жыл бұрын
I tried a filter array but it seems like my syntax is wrong - error message is that it is not valid. @not(equals('Status','Complete')),greater('Created',-7)
@ShanesCows
@ShanesCows 2 жыл бұрын
You can’t just -7 the date. You will need to use a date function to do so. If you check my latest video in the second half I show subtracting dates.
@kelvin55141
@kelvin55141 2 жыл бұрын
@@ShanesCows , managed to get the array filter to work but now I cant get the email component to reference the array filter instead it emails the full list. Any suggestions? Decided to keep it basic so I am only filters items that are not completed from a calculated column and now I would like to send reminders to those people.
@thnkngwlnts
@thnkngwlnts 2 жыл бұрын
Hello Shane, is there a way to do a filter on a list of SharePoint libraries to return a result based on a input string? Something like a wildcard search on certain columns within a set of document libraries. I see that the contains or substringof OData filter functions are not supported for some reason.
@mattkristinabrown4515
@mattkristinabrown4515 3 жыл бұрын
This was a great video, I'm glad it and your channel! I do have one question, I'm trying to recreate something similar to this. I recreated your example for my data, and works flawlessly (just using the Title column for the count). However, what I really need to do is count a calculated column and return that result. As soon as I recreate this using the calc column, it doesn't seem to count any longer. Any ideas? The calc column is concatenating the item's Title & " -Yes" or "-No" defined as if the item was added to the list within last x amount of days (using DateDif, basically I'm attempting to return how many recent entries for a specific individual/entry on the list, which will kick off a workflow once the threshold is hit). The calculated column works as I expected. Any ideas?
@giorgoskaramousalidis7345
@giorgoskaramousalidis7345 9 ай бұрын
And how to merge data by two sharepoint lists with one to many relationship?
@sundrapillay6668
@sundrapillay6668 3 жыл бұрын
Thanks Shane
@ShanesCows
@ShanesCows 3 жыл бұрын
🐶 Happy to help
@lorde022
@lorde022 3 жыл бұрын
Hi Shane, love the video! However I need to match (in your example the department) against a filtered array result from another list. Your solution points at the same list; how do I make it look at a different one? To clarify (because after doing a lot of research I realized I didn’t explain that very well 🙈): My process is the same as in your example until the point, where you filter the array with „is equal to“ against the value from the original list (in your example „executive“); in my case the data originates from an array where I find a field with the IDs of several employees separated by commas. I have split those and put them into an array (list A) with the Initials as the title (that being the only column). I have a separate list (B) with employee data, from which I want to find the match for each one of the Initials of list A (the corresponding Initials are in the Title column of list B). Then I want a new list to be produced with the items from list A and for each item the corresponding details from list B. Tanks in advance and keep it up! :-) Dennis
@erinfield1943
@erinfield1943 2 жыл бұрын
5:30- Filtering
@patrickborge2790
@patrickborge2790 3 жыл бұрын
Hi Shane, instead of updating the employee SP list I would like to update an other sharepoint list with the number of employees per department. This means we would have an other SP list that contains all departments options and that would be updated according to the outcome of this flow. Thank you for your nice videos.
@ShanesCows
@ShanesCows 3 жыл бұрын
You can do that. You will need to figure out which record you want to update then get that record for the Update action instead. A bit different but not too bad to do.
@TheToni2123
@TheToni2123 3 жыл бұрын
Thx
@ShanesCows
@ShanesCows 3 жыл бұрын
No problem
Power Automate | Apply to Each | Why? | Arrays and Objects
19:05
DamoBird365
Рет қаралды 47 М.
How to group and calculate sum of Power Automate array items
24:54
Alireza Aliabadi
Рет қаралды 18 М.
人是不能做到吗?#火影忍者 #家人  #佐助
00:20
火影忍者一家
Рет қаралды 20 МЛН
“Don’t stop the chances.”
00:44
ISSEI / いっせい
Рет қаралды 62 МЛН
coco在求救? #小丑 #天使 #shorts
00:29
好人小丑
Рет қаралды 120 МЛН
Are you using the Microsoft Power Automate Filter Array Action wrong?
22:45
A Creative Opinion
Рет қаралды 29 М.
Copy Power BI Data to SharePoint List with Power Automate - Easy Guide
11:29
Easily Avoid Apply to Each Loops | Power Automate
10:20
FlowJoe
Рет қаралды 17 М.
Power Automate HTTP action, Rest API, and IOT
25:47
Shane Young
Рет қаралды 51 М.
How to Create Custom AutoNumbering Columns in SharePoint List
23:01
人是不能做到吗?#火影忍者 #家人  #佐助
00:20
火影忍者一家
Рет қаралды 20 МЛН