cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Lexicron
Resolver II
Resolver II

How to use Aggregation transformation in Flow - List records?

I'm trying to groupby and do 2 types of aggregations: sum for 25 fields and count for the same 25 fields. Ideally I want something like 

 

groupby(
	(cr3df_site2),
	aggregate(
		cr3df_score01 with sum as cr3df_score01sum,
		cr3df_score02 with sum as cr3df_score02sum,
		cr3df_score01 with countcolumn as cr3df_score01count,
		cr3df_score02 with countcolumn as cr3df_score02count
	)
)

 

Clipboarder.2020.07.16.png

However count and countcolumn isn't recognised. Does anyone know if there's a way to achieve this? 

 

I need do to a count because I need to calculate mean that ignores null-fields. I believe using an average will simply divide the sum by the number of records which is not the data I need. Hence I want to do both a sum and a countcolumn (which counts non-null values of that column), the results of which I plan to write to a CDS entity, and then in my CDS entity I have calculation fields that do Sum/Count to get the mean that I need.

 

Second question: is there a way to group by 2 fields, cr3df_site2 and c43df_participanttype2? If I can group by multiple fields that would save me having to set up multiple List records to cover all my participant types.

 

My end goal is actually a little more complex than above. I do have a fetchxml query that achieves my desired goal perfectly but unfortunately it does not work in Flow. So I'm hoping there's still alternatives available.

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @JohnAageAnderse,
Thank you for responding! 🙂
Yes I have tried that. The link in my last paragraph goes to my prior post where I admittedly present quite bitter about the letdown of Flow in this area. Very disappointing. The MS support engineer I was assigned has confirmed that aggregation is currently not supported in Flow's FetchXML at the moment. Alas!
I have gone on to create a HUGE flow with variables and loops, it went all the way up to the limit of 500 templates. It does the job, but boy did Flow give me a virtual ulcer for 2 weeks! 😅

View solution in original post

3 REPLIES 3
JohnAageAnderse
Memorable Member
Memorable Member

Hello @Lexicron 

I looked at the documentation for CDS and there are two sets - one just named Common Data Service and one named Common Data Service (current environment). From your screenshot you are using the first one.

The second one has the same action "List records" and it contains a field "Fetch Xml Query" - and as you mentioned you had such, could that be an option for you? Unless you already tried 🙂

Kind regards, John

Hi @JohnAageAnderse,
Thank you for responding! 🙂
Yes I have tried that. The link in my last paragraph goes to my prior post where I admittedly present quite bitter about the letdown of Flow in this area. Very disappointing. The MS support engineer I was assigned has confirmed that aggregation is currently not supported in Flow's FetchXML at the moment. Alas!
I have gone on to create a HUGE flow with variables and loops, it went all the way up to the limit of 500 templates. It does the job, but boy did Flow give me a virtual ulcer for 2 weeks! 😅

View solution in original post

Vile
Frequent Visitor

Hi @Lexicron i'm facing the same issue here, FetchXML, do all what i need, which is the following...

<fetch version="1.0" output-format="xml-platform" aggregate="true" mapping="logical" distinct="false" >
  <entity name="entityName" >
    <attribute name="AttributeId" alias="Quantity" aggregate="countcolumn" />
    <attribute name="AttributeBrandId" alias="Brand" groupby="true" />
    <attribute name="MarketingSourceId" alias="Marketing" groupby="true" />
    <order alias="Marketing" descending="true" />
    <order alias="Brand" descending="false" />
    <filter type="and" >
      <condition attribute="createdon" operator="last-x-hours" value="24" />
      <condition attribute="MarketingSourceId" operator="not-null" />
    </filter>
  </entity>
</fetch>

 

Works like a charm on FetchBuilder, but when i add it to CDS List records... powerAutomate explodes!!! InternalServerError, and is not more than 3 records xD 😕

Can you guide me on how you achieved it?

 

Thanks in advance!!!

 

Helpful resources

Announcements
MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors
Users online (1,416)