Power Automate - Fast Data Aggregation - Group By, Sum, Count

Поделиться
HTML-код
  • Опубликовано: 5 дек 2024

Комментарии • 86

  • @bootsbax
    @bootsbax 18 часов назад +1

    i cannot even tell you how much this helped me. It wasn't working at first because i was using the Entra ID # for the users, but once i composed a list of unwanted characters and used it to remove the letters and dashes on the 'when a new item is created' flow it worked perfectly. One million thanks

  • @jamesclark2020
    @jamesclark2020 9 месяцев назад +2

    I applied this technique today at work. What a thrill it was when it did exactly what it was supposed to do! Thank you again Damien ❤

  • @ukm365
    @ukm365 10 месяцев назад +1

    It's good that you've split it out like this, Damo.
    I often did this with solutions in the community. Whilst we know that we can smoosh a lot of these into one expression, it doesn't provide the easy path to learn that.

  • @ingridschiele1475
    @ingridschiele1475 28 дней назад +1

    Again, this is a very helpful video. Thanks for this!

  • @cvkealey
    @cvkealey 10 месяцев назад +1

    Thank you for the reminder that I REALLY need to sit down and learn/get comfortable with xpath. I've been working with more flows that need to "post-process" data pulled in from SharePoint or Excel and the "filter array" action doesn't always do what I want.

  • @jamesclark2020
    @jamesclark2020 10 месяцев назад +1

    Wow. Excellent video, Damien! You made xpath seem more approachable

  • @thanura.m
    @thanura.m 9 месяцев назад +1

    thank you for the demo. i constructed a similar usecase using a loads of loops. i should use this from now on!

  • @emmanuelmaceda2475
    @emmanuelmaceda2475 10 месяцев назад +1

    I was just reading your old post from 2021 on this topic with a hack using length function. Excellent vid as usual. Thank you for sharing!

  • @om0729
    @om0729 10 месяцев назад +2

    I really love 💖 your videos. please keep sharing the knowledge. this is another great example where we can perform group by with in the Power Automate.👏👏👏

  • @neilthackeray181
    @neilthackeray181 6 месяцев назад +1

    Simply AMAZING! very easy to follow what could've otherwise been a complicated process. Thank You Damo

  • @wendylumsden2224
    @wendylumsden2224 7 месяцев назад +1

    Thank you so much! I have learnt heaps and will apply to many of my flows which create custom reports from Dataverse tables. Greatly appreciate your generously sharing your knowledge to assist others

  • @AndrewBromley-b3q
    @AndrewBromley-b3q 5 месяцев назад +1

    Hi Damien. Your video is super-informative and pretty easy for a layman like me to understand. I followed it step-by-step, except in my case I am running a query against a Power BI dataset. I'm certainly not an expert and I mimicked your flow as closely as possible but, I think that my attempt is failing because I can't seem to get the grouped categories into the same structure as your in the XML output. My output looks like this:
    [
    {
    "Category": ""
    },
    {
    "Category": "- ACC CLOSED"
    },
    {
    "Category": "- ADD INCOME"
    },
    {
    "Category": "- INTERNAL"
    },
    {
    "Category": "* CASH 1 _DEL Mon - INV Fri - CALL Thu"
    },
    {
    "Category": "* CASH 2 _DEL Tue - INV Mon - CALL Fri"
    },
    {
    "Category": "* CASH 3 _DEL Wed - INV Tue - CALL Mon"
    },
    {
    "Category": "* CASH 4 _DEL Thu - INV Wed - CALL Tue"
    },
    {
    "Category": "* CASH 5 _DEL Fri - INV Thu - CALL Wed"
    },
    {
    "Category": "* EFT 1 _DEL Mon - INV Fri - CALL Thu"
    },
    {
    "Category": "* EFT 2 _DEL Tue - INV Mon - CALL Fri"
    },
    {
    "Category": "* EFT 3 _DEL Wed - INV Tue - CALL Mon"
    },
    {
    "Category": "* EFT 4 _DEL Thu - INV Wed - CALL Tue"
    }
    ]
    As you can see, my output contains all of the squiggly brackets { } and I can only guess that it is this that is the stumbling block for later flow steps. I get this message at the Compose XML step "This XML file does not appear to have any style information associated with it. The document tree is shown below."
    72 DEGREES BAKERY & FROZEN FOODS
    0
    0
    2314.5
    0
    0
    2314.5
    I'm very inexperienced and not entirely sure that I have identified the actual error but it is the one thing that stands out for me. I'd really appreciate it if you could point me in the right direction. It will save me bucket loads of time if I can get the desired result. I've spent so much time on this and I'm super keen to get a positive result.
    Many thanks for your assistance,
    Andrew

  • @islamshaban216
    @islamshaban216 2 месяца назад +1

    Big fan of your work, Man!👏❣

    • @DamoBird365
      @DamoBird365  2 месяца назад

      Thank you ever so much 👍

  • @Stevencyw88
    @Stevencyw88 3 месяца назад +1

    thank you so much. very clear explanation. this is what i am looking for.

  • @isaiaguillon9878
    @isaiaguillon9878 2 месяца назад +1

    This is great! Thank you for sharing, in the Xpather it says the html element name must be lowercase, it does this with complex columns, any advise?

    • @tomka5050
      @tomka5050 Месяц назад

      I am having the same issue as well

  • @vuxuanhai7311
    @vuxuanhai7311 2 месяца назад +1

    it's awesome. Thank you so much!

  • @PetroZabenko
    @PetroZabenko 2 месяца назад

    awesome tricks/way to make groupBy - Thanks

  • @dessyducasse3003
    @dessyducasse3003 7 месяцев назад +1

    Thanks this method is what I was looking

  • @glal5735
    @glal5735 2 месяца назад +1

    Thank you. Great video very explanatory. Can you show situation where you have more than 2 or 3 excel spreadsheets in SharePoint and you want to count number of rows on each spread sheet and write result to another spreadsheet.

    • @DamoBird365
      @DamoBird365  2 месяца назад

      Write all the rows to a spreadsheet or write the count of rows to a spreadsheet?

  •  10 месяцев назад +1

    "Old friend classic designer" FTW :) Great demo Damo again. 👍💡🥳

    • @DamoBird365
      @DamoBird365  10 месяцев назад +1

      Ah yes. I’m working hard to feedback my findings with PG. It will get there.

    • @DamoBird365
      @DamoBird365  10 месяцев назад +1

      😂😂😂

  • @temidayolonge4566
    @temidayolonge4566 10 месяцев назад +1

    Great Video as always.

  • @arturmusialak8704
    @arturmusialak8704 10 месяцев назад +1

    Great video! Thanks a lot 👍

  • @mikepsychles
    @mikepsychles 3 месяца назад +1

    Thanks for this excellent video! It works perfectly for me, but I have one issue (on my end...). I've been looking at some of your other videos and cannot quite sort out how to query the values of the select array. In other words, when I look at the output of the final "select," I see each of the records with indivdual SUMs (I modified yours somewhat) and I need to find a way to query that data to find the records with that SUM > 1. Any help would be greatly appreciated.

    • @DamoBird365
      @DamoBird365  3 месяца назад

      @@mikepsychles just use a filter array on the body output of the select where the item()?[‘sum’] greater than 1 👍

    • @mikepsychles
      @mikepsychles 3 месяца назад

      @@DamoBird365 Thanks for your reply! I tried that, acutally, and my syntax must be wrong... I am returning 6 values with the Select - but just two of them are SUM'd values. I'm getting ... "The evaluation of 'query' action 'where' expression '@greater(item('TotalHCAllocation'), 1)' failed: 'The template language function 'item' must not have any parameters.'. (TotalHCAllocation is the first SUM'd value). " .... too bad I can't add a screenshot here.

  • @mrZerg00s
    @mrZerg00s 10 месяцев назад +1

    Thank you! I had no idea xpath was so powerful. 😲

  • @SirThomasR
    @SirThomasR 8 месяцев назад +1

    I have a similar list, with a column called County and Status and I want to 1.) go through the list and count the number of items grouped by the county name and be provided totals and then 2.) I have another column called status that’s a choice column and then I want to count the number of Active, Closed, and Pending statuses also by the county and be given totals. Would your demonstration work with what I am looking to do as well?

    • @DamoBird365
      @DamoBird365  8 месяцев назад

      It would certainly help you with most of your requirements 👍

  • @sankhlayashraj
    @sankhlayashraj 9 месяцев назад +1

    Thank you for the lovely video Damien. Leart a lot. I had one question for you on the xpath expression - The name in the map of is the name that you have given; however in the expression, are those the internal names of the columns. Also want to know would that be same if I am trying to get the count of data from a choice column in SP.

    • @DamoBird365
      @DamoBird365  9 месяцев назад

      The easiest way to find the name is to look at the output history. SharePoint might have field1,2,3 etc if you’ve created a list from an import. Good luck 👍

    • @sankhlayashraj
      @sankhlayashraj 9 месяцев назад +1

      Thank you so much. I was able to create a flow which is working perfectly fine. Appreciate your help.

  • @humayonsalim1877
    @humayonsalim1877 7 месяцев назад

    This is very useful. Can I ask how you would build the XPath expression with the sum() inside this xpath(outputs('Compose_XML), concat('(//values[EmployeeNumber=',item(),']/Email)[1]/text()'))). ?

  • @aaronalbers7280
    @aaronalbers7280 5 месяцев назад +1

    Does xpath in this context support string lookups instead of integers? For example, lets say you wanted to to use John Smith instead. "//values[Name="John Smith"]/Expense[1]/text()"

  • @ma.teresatabaco5398
    @ma.teresatabaco5398 6 месяцев назад +1

    Hi Damo, thank you for sharing this great video. This is exactly what I'm looking for. However, I want to ask how about the calculated columns? Is it possible to use this also? I'm sorry I am new to Power Automate.

    • @DamoBird365
      @DamoBird365  6 месяцев назад

      In theory yes, but what you need to look at is the history of get items. Run the flow, check the history, can you see the calculated column value in output history. If yes, the logic is the same.

    • @ma.teresatabaco5398
      @ma.teresatabaco5398 6 месяцев назад

      @@DamoBird365 Hi Damo, yes, I can see the calculated columns now. For many days I did a lot of searching online for this. I'm happy I found your channel.

    • @ma.teresatabaco5398
      @ma.teresatabaco5398 6 месяцев назад

      By the way, can you help me on how to call this flow from another Sharepoint List? I have second SP List with a column for the sum (by employee) of the calculated column from first SP list. I am not sure if you have video regarding this, but I'm trying to watch all your videos one by one. If you have, please share with me here the link... Thank you so much.

  • @wjameswilliams
    @wjameswilliams 10 месяцев назад +1

    I really feel for newcomers to Power Automate who will have no idea about the classic designer but also that will have no chance at figuring their way around all the bugs in the new editor. It's difficult enough to determine the issue when you know what you're doing!
    That said, this video was excellent. Avoiding unnecessary loops is so important, not least because of consumption but also because it's so much faster! Scheduling overheads can add minutes to a flow because of a loop before any actions even take place, which can even be exacerbated by other Microsoft customers' use of the multi-tenant environment 😞

    • @DamoBird365
      @DamoBird365  10 месяцев назад +2

      It’s definitely not an easy time to jump into Power Automate with both classic and new designer on the go and new having so many quirky bugs - agreed. I’m not a personal fan of Copilot in Power Automate either with so much of the automation incomplete but it will improve no doubt. I’ll keep sharing ideas on the new designer and hopefully folk can return to these videos. The concept won’t change and hopefully the video stands the test of time. I am planning on some newbie content 🤞 Just got to find the time as this is really just a hobby, albeit one I enjoy.

  • @SuperGameBros0610
    @SuperGameBros0610 29 дней назад +1

    Very cool video Damien! This was exactly what I was looking for, as I was trying to create a new array with summed data based on an API call.
    The only problem is the so called number data from said API is in string format. So I'm trying to work out how you could sum it anyways. No luck so far.
    Do you know of a way?

    • @DamoBird365
      @DamoBird365  29 дней назад

      Could you use a select and convert the string to int() or float()?

    • @SuperGameBros0610
      @SuperGameBros0610 29 дней назад +1

      @@DamoBird365Thanks for the quick response! I'm going to try that out. I have about 70-100 item that are in string format. So I'll see if it's possible!

    • @SuperGameBros0610
      @SuperGameBros0610 23 дня назад

      Just following up, It kind of worked. Turns out my numbers were already in int format so that wasn't the problem, but by applying (number(.) = number(.)] behind the value it worked.
      My only issue is that power automate says that my concat function is invalid
      xpath(outputs('ComposeXML'),concat('sum(//values[title[normalize-space(.)=',item(),']]/enterprise[number(.) = number(.)])'))
      the normalize space is needed because of the format of my data. For some reason the element pairs are line seperated which creates white space that then needs to be normalized.
      Could you provide assisstance on what I could do to concat the data anyways?
      Thanks!

  • @hernandogarrido139
    @hernandogarrido139 15 дней назад +1

    Hi, thanks for sharing your method. I used it and it works for some records but I noticed it did not work on others where my employee ID had "-". For example employee "1234" brings the "name" correctly but for employee "1234-01" brings a "null" name. Do you know why?

    • @DamoBird365
      @DamoBird365  15 дней назад

      @@hernandogarrido139 I’m curious, I don’t know why. Please share if you find out.

  • @filipwinski4219
    @filipwinski4219 10 месяцев назад +1

    Awesome!!!

  • @abbeyesval
    @abbeyesval 10 месяцев назад +1

    thanks for this excellent video, I just have a question, how can I add number format on the xpath function in order to summarize the costs? I tried with formatNumber(Item()?['AmountLocalCurrency'],'N2') on the initial table, however the sum result that I receive is NAN, hope you can help me

    • @DamoBird365
      @DamoBird365  10 месяцев назад

      I wonder if your currency is a string? You could try float() around the item()?[‘currency’]. The history will let you see if number is in quotes (a string) or not (a number).

  • @We_went_camping_again
    @We_went_camping_again 5 часов назад

    First of all, thank you for a very detailed video! I am facing an issue when the element I am searching for is string, for example: //values[ItemNumber = S0001] - this does not find the element. Any suggestions?

  • @ukm365
    @ukm365 10 месяцев назад +1

    OOOOOH ... xpather is LUSH.

  • @chritoni
    @chritoni 9 месяцев назад

    Thanks! This provided a great way to handle large invoicing and accounting materials with efficiency. I´d really like to see how you would handle something that is large enough to require paging.

    • @DamoBird365
      @DamoBird365  9 месяцев назад +1

      Paging from the data source? Or something else? I’ve got graph api paging here ruclips.net/video/4IphRdADJBc/видео.htmlsi=jTbBHRjpUY68veTW

    • @chritoni
      @chritoni 9 месяцев назад +1

      @@DamoBird365 Yes, I suppose I meant paging a source and in general I´m especially interested in handling a large number of rows in just about any setting. Did not find paging behind that link, but if you have such a video, I´ll find it eventually 🙈 Thanks for you blog and videos 👍👍👍

    • @DamoBird365
      @DamoBird365  9 месяцев назад +1

      Oooops, wrong link apologies 👉 Boost Your Power Automate Skills with Complex Arrays, Select, XML, and Join Techniques
      ruclips.net/video/afqvGAb20Dw/видео.html

  • @klebermedeiros8742
    @klebermedeiros8742 7 месяцев назад +1

    Very good. Is it possible to get a max value, using a expression like sum?

    • @DamoBird365
      @DamoBird365  7 месяцев назад

      There is a max expression learn.microsoft.com/en-us/azure/logic-apps/workflow-definition-language-functions-reference#max

  • @McIlravyInc
    @McIlravyInc 10 месяцев назад +1

    I use power query and VBA to do this, but i can see that this might be useful in conjunction with power apps to return some data for the user.
    Any other use cases where this technique is better than power query/bi/vba ? (VBA is no bigger lift than flows or apps IMO).

    • @DamoBird365
      @DamoBird365  10 месяцев назад +1

      There are definitely other products / platforms that can do this but some folk work in the low code space and have this requirement. An apply to each and a variable is not an efficient way of doing this. Office Scripts could achieve this easily I bet. But some orgs don’t have Office Scripts. Good to have options.

    • @McIlravyInc
      @McIlravyInc 10 месяцев назад +1

      Yes this technique has a lot of gems in it for other processes too.
      If they have power platform they have Excel also, and i find greater trust and acceptance with excel solutions in my area (especially because they can edit the email before sending, decide not to send one or more, etc) than just having emails come automagically (as one friend calls it).
      But I can see a use in a power app I'm thinking of for sure!

    • @DamoBird365
      @DamoBird365  10 месяцев назад +1

      @@McIlravyInc nice 👍 let me know how you get on.

  • @thiagopais5785
    @thiagopais5785 2 месяца назад

    Is it possible to group by employee by selecting the rows with the maximum date?

  • @mk15minut64
    @mk15minut64 10 месяцев назад +1

    Can you make the same video in classic ui? Or maybe provide a zip copy of the flow?

    • @DamoBird365
      @DamoBird365  10 месяцев назад +1

      I’ve not got plans to do videos in both designer. But the video should give you everything you need.

  • @mikepsychles
    @mikepsychles 4 месяца назад

    Why does this PA editor work for you and not for me. It does not save the parameters I add (like xpath strings, etc.) so I have to save them out to notepad every time I write one.

  • @benjaminlahsen6721
    @benjaminlahsen6721 3 месяца назад +1

    GOAT

    • @DamoBird365
      @DamoBird365  3 месяца назад

      Thanks, I’ll take that.

  • @PavanCh-q2u
    @PavanCh-q2u Месяц назад

    Hi, great content..
    I have similar use where I am getting data in Power BI and want to export it to excel using Power Automate button, but here is a point..
    I have total around 7-8 categorical or text fields/columns, which are dynamic based on field parameter so I mean sometimes user may select 4 or sometimes 5 or sometimes 8 so all these fields should be group by and come in first column/one column with hierarchical structure like:
    Name Value
    Column1 100
    Column2 100
    Column3 100
    Column4 100
    Column5 100
    Type1 50
    Subtype1 25
    Subtype2 25
    Type2 50
    Subtype1 35
    Subtype2 15
    So till "Column5" I was able to achieve the values what is expected using json and inside json have used xpath expressions after xml and xpath as you shown in initial steps, but I got stuck from Type1, Type2 of Column5. Any help/suggestions are really appreciated. Currently I am working on real time scenario. Thanks in advance.

  • @klebermedeiros8742
    @klebermedeiros8742 7 месяцев назад

    My flow gives an error when the dynamic item has letters and numbers together. What can it be?

    • @klebermedeiros8742
      @klebermedeiros8742 7 месяцев назад +1

      It worked when I inserting " before '.

    • @DamoBird365
      @DamoBird365  7 месяцев назад

      Glad you worked it out ok 👍

    • @joao0790
      @joao0790 6 месяцев назад

      @@klebermedeiros8742 Hi Kleber, what do you mean by inserting "before"? I am having the same issue. The field I am using to find the xml value is a string (very long, indeed), and I noticed using the xpather, it cannot find elements. Do you have any clue?

    • @bootsbax
      @bootsbax 28 дней назад

      @@joao0790 I'm having the same problem (using user id which is a long string of letters and numbers and it won't work )