Producing JSON Output that sums rows from Excel by Person

Beginner Questions
1

:bullseye: What is your goal?

To sum Excel records by person and produce a JSON output

:thinking: What is the problem?

The scenario has the Excel module which retrieves the records from an excel file. It contains payroll data that has hours worked on given days for employees. I would like to produce a simplified output the sums the total hours and pay by employee, so instead of having 50 rows for an employee it has the total. I’m looking for an equivalent of sumif in Excel or the Group by option in Power Query but I can’t work out how to do this.

Is this possible?

:test_tube: What have you tried so far?

I’ve tried the Group By option in the Aggregate to JSON module but it doesn’t produce an output so I’m not sure I’m using it correctly

3

Hey Ian,

why not do this directly in Excel though?

You need a Sum aggregator set to group by employee.

4

Hi Stoyan

Thanks for the reply. The fie is retrieved from a client site, so having the data in Make is the first time we see it.

I’ve tried using the Group By option on the Aggregate to JSON module (the data is sent via an API call to another application, hence the JSON) as below:

image
image467×844 34.8 KB

However this doesn’t produce an output, not sure I’m using it correctly?

Thanks,

Ian

5

This looks correct y ah. What are you getting as input and output? Can you show some screenshots?

6

Here’s the input to the JSON module, I bundle equals 1 record of data:

image
image468×676 18 KB

The “employeeNo” field in the JSON data structure is where we’ve mapped the {{36.row.b.value}} to, which is what I need to Group By.

However I get no output:

image
image440×279 3.99 KB

7

Actually it has produced an output now!

8

Not sure what I was getting wrong before