Is there method similar to extend but for rows (creating a new row)?

Question

I have the following datatable:

let DataSource = datatable(Name:string, value1:real, value2:real)
[
    'Section 1', 2211.47, 3564.05,
    'Transfer 1', 3075.44, 3717.51,
    'Transfer 2', 3460.66, 4194.16,
    'Transfer 3', 4819.36, 482.02,
    'Section 2', 1742.02, 11971.05
];

I need to add to this datatable a new row which with the following values:

Name: Transfer value1: sum of value1 for all Names containing Transfer value2: average of value2 for all Names containing Transfer

The expected outcome is:

let DataSource = datatable(Name:string, value1:real, value2:real)
[
    'Section 1', 2211.47, 3564.05,
    'Transfer', 11355.46, 2797.9,
    'Section 2', 1742.02, 11971.05
];

I don't know if there is a method similar to extend which can create a new row to achieve this result. I tried this way:

let DataSource = datatable(Name:string, value1:real, value2:real)
[
    'Section 1', 2211.47, 3564.05,
    'Transfer 1', 3075.44, 3717.51,
    'Transfer 2', 3460.66, 4194.16,
    'Transfer 3', 4819.36, 482.02,
    'Section 2', 1742.02, 11971.05
];
DataSource
| as t1 
| union (t1 | summarize Name = "Transfer", value1 = sum(value1), value2 = sum(value2))
| where Name !in ("Transfer 1", "Transfer 2", "Transfer 3") 

It works but I think it will take a lot of cpu memory if we are working in huge dataset.

Is there a better approach ?

Answer 1

You can use the extend operator to modify the Name column to group all the names containing Transfer and then use the summarize operator to compute the sum of value1 and the average of value2 for each unique value of Name. Below is the code:

Code:

let DataSource = datatable(Name:string, value1:real, value2:real)
[
'Section 1', 2211.47, 3564.05,
'Transfer 1', 3075.44, 3717.51,
'Transfer 2', 3460.66, 4194.16,
'Transfer 3', 4819.36, 482.02,
'Section 2', 1742.02, 11971.05
];
DataSource
| extend Name=iif(Name contains "Transfer", "Transfer", Name)
| summarize value1=sum(value1), value2=avg(value2) by Name

This code uses the extend operator to modify the Name column using the iif function. The iif function checks if the Name column contains the word "Transfer", then replaces the value of the Name column with the string "Transfer". Otherwise, it leaves the value of the Name column unchanged. The summarize operator is then used to compute the sum of value1 and the average of value2 for each unique value of Name.

Output:

Name	value1	value2
Section 1	2211.47	3564.05
Transfer	11355.46	2797.896666666667
Section 2	1742.02	11971.05

demo