AddColumns, Groupby Data to send an attachment

(0) Share

Report

Posted on by JohnHenry

Can anyone please optimize the below code, at present, the code working perfectly.

I want this data to be sent as an attachment by using Power Automate. Looking for help.

//Code Begin

Sort(
AddColumns(
GroupBy(Filter(Chronicles,TaskDate >= FilterStartDate_1.SelectedDate && TaskDate <= FilterEndDate_1.SelectedDate),
"TaskDate",
"ByDate"
),
"TOT",
Sum(
Filter(
ByDate,
TaskDate >= FilterStartDate_1.SelectedDate && TaskDate <= FilterEndDate_1.SelectedDate
),
TaskDuration
),
"BAH",
Sum(
Filter(
ByDate,
Country = "Bahrain" && TaskDate >= FilterStartDate_1.SelectedDate && TaskDate <= FilterEndDate_1.SelectedDate
),
TaskDuration
),
"KSA",
Sum(
Filter(
ByDate,
Country = "Saudi Arabia" && TaskDate >= FilterStartDate_1.SelectedDate && TaskDate <= FilterEndDate_1.SelectedDate
),
TaskDuration
),
"QAT",
Sum(
Filter(
ByDate,
Country = "Qatar" && TaskDate >= FilterStartDate_1.SelectedDate && TaskDate <= FilterEndDate_1.SelectedDate
),
TaskDuration
),
"OMN",
Sum(
Filter(
ByDate,
Country = "Oman" && TaskDate >= FilterStartDate_1.SelectedDate && TaskDate <= FilterEndDate_1.SelectedDate
),
TaskDuration
),
"DXB",
Sum(
Filter(
ByDate,
Country = "Dubai" && TaskDate >= FilterStartDate_1.SelectedDate && TaskDate <= FilterEndDate_1.SelectedDate
),
TaskDuration
),
"ABD",
Sum(
Filter(
ByDate,
Country = "Abu Dhabi" && TaskDate >= FilterStartDate_1.SelectedDate && TaskDate <= FilterEndDate_1.SelectedDate
),
TaskDuration
),
"SD",
Sum(
Filter(
ByDate,
Country = "Stevedoring" && TaskDate >= FilterStartDate_1.SelectedDate && TaskDate <= FilterEndDate_1.SelectedDate
),
TaskDuration
),
"REG",
Sum(
Filter(
ByDate,
Country = "Regional" && TaskDate >= FilterStartDate_1.SelectedDate && TaskDate <= FilterEndDate_1.SelectedDate
),
TaskDuration
)
),
TaskDate,
SortOrder.Ascending
)

//Code End

//The Screen

Categories:

Building Power Apps

I have the same question (0)

All responses (6)

Answers (1)

Sort by

Verified answer

AhmedSalih 6,680 Moderator on at

Like
a
(1)

Report
Copy link

Link copied!
Hello, @JohnHenry, there is so much redundancy:

ClearCollect( ProcessedData, Sort( AddColumns( GroupBy( Filter( Chronicles, TaskDate >= FilterStartDate_1.SelectedDate && TaskDate <= FilterEndDate_1.SelectedDate ), "TaskDate", "AllData" ), "Data", ForAll( [ "Bahrain", "Saudi Arabia", "Qatar", "Oman", "Dubai", "Abu Dhabi", "Stevedoring", "Regional" ], Sum( Filter( AllData, Country = CurrentRecord && TaskDate >= FilterStartDate_1.SelectedDate && TaskDate <= FilterEndDate_1.SelectedDate ), TaskDuration ) ), "TOT", Sum(AllData, TaskDuration) ), TaskDate, SortOrder.Ascending ) );

Send the Data as JSON:

Set( JsonData, JSON(ProcessedData, JSONFormat.IncludeBinaryData) );
PowerAutomateFlowName.Run(JsonData)

If my reply helped you, please give a 👍 If it solved your issue, please give a 👍 & accept it as the Solution to help other community members find it more.

Visit my Blog: ahmedsalih.blog

Visit my YouTube Channel: https://www.youtube.com/@powerplatformplace/videos

Microsoft Business Application MVP

Was this reply helpful? Yes No
JohnHenry 14 on at

Like
a
(0)

Report
Copy link

Link copied!

Thank you So much Mr. Ahmed.
It shows error when I copy this code in "Items Property".
Do I need change any column name or source name. please help.

Was this reply helpful? Yes No
AhmedSalih 6,680 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

@JohnHenry, What was the error? Can you share screenshot?

Was this reply helpful? Yes No
JohnHenry 14 on at

Like
a
(0)

Report
Copy link

Link copied!

"The function 'ClearCollect' has some invalid arguments.
Name is not valid. 'ProcessedData' isn't recognized.

Was this reply helpful? Yes No
AhmedSalih 6,680 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

@JohnHenry, ClearCollect can't be in the Items property of a Gallery, use the Code in the Screen.OnVisible. To test add a Button and use it for the Collection creation.

If my reply helped you, please give a 👍 If it solved your issue, please give a 👍 & accept it as the Solution to help other community members find it more.

Visit my Blog: ahmedsalih.blog

Visit my YouTube Channel: https://www.youtube.com/@powerplatformplace/videos

Microsoft Business Application MVP

Was this reply helpful? Yes No
JohnHenry 14 on at

Like
a
(0)

Report
Copy link

Link copied!

john.henry@sarensnass.com

Was this reply helpful? Yes No