In a table X, put the result of an aggregation for a column from an other table Y based on an ID

[Agnes_BOURLON]

Hi

I have 2 tables : EPIC and US_TASK

Table EPIC

SP_US_TASK | IssueKey

Table US_TASK

StoryPoints | EpicLink

I would like to translate the DAX formula in a Power Query, to use it in a Data Flow

Here the DAX code : SP_US_TASK = CALCULATE (SUM(US_TASK[StoryPoints]), FILTER (US_TASK, US_TASK[EpicLink] = EPIC[IssueKey]))

The aim is to have a column SP_US_TASK containing the sum of a column US_TASK[Story Points] for a specific Issue Key (that is on ID, that is called Issue Key for the table EPIC and Epic Link in US_TASK)

I tried to write : 

Table.AggregateTableColumn(EPIC, "SP_US_TASK", List.sum(Table.SelectRows("US_TASK",each EPIC[Issue Key]=US_TASK[Epic Link]))

And I got the error

The import List.sum matches no exports. Did you miss a module reference?

Détails

Reason = Expression.Error

ErrorCode = 10079

Thx in advance for your help

Agnes

[lbendlin · Accepted solution]

You cannot List.Sum a table.  You need to use a column reference.

[ZhangKun · Accepted solution]

Table.AddColumn(
    EPIC, 
    "SP_US_TASK", 
    (r) => List.sum(Table.SelectRows(US_TASK,each r[Issue Key] = [Epic Link])[StoryPoint])
)

[Anonymous · Accepted solution]

Hi,

Thanks for the solution ZhangKun  and lbendlin  offered, and i want to offer some more information for user to refer to.

hello @Agnes_BOURLON , you can try  the following code.

Table.AddColumn(#"xxx"(you last step name), "SP_US_TASK", each let a=[IssueKey]
in List.Sum(Table.SelectRows(US_TASK,each [EpicLink]=a)[StoryPoints]))

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Agnes_BOURLON]

Hi Yolo Zhu

Thanks for your help

I tried to do this but I got as a Result a [Function] in the column SP_US_TASK and not a sum

And when I click on [Function], I got this, asking to me to enter a parameter. Why?

[lbendlin]

most likely you forgot the "each"  modifier.

[Agnes_BOURLON]

Ok I 'll check

In the meantime, I found this new way : 

   #"A"= Table.AddJoinColumn(#"EPIC", "Issue key", US_TASK, "EpicLink", "StoryPointAgg"),

   #"B"= Table.AddColumn(#"A", "SPAgg", each List.Sum([StoryPointAgg][StoryPoints]))

and it seems to be ok 🙂

[lbendlin]

It may seem ok but it's a rather convoluted way of doing things. Always, always measure the performance of the various approaches with real world data.

[ZhangKun · Accepted solution]

Table.AddColumn(
    EPIC, 
    "SP_US_TASK", 
    (r) => List.sum(Table.SelectRows(US_TASK,each r[Issue Key] = [Epic Link])[StoryPoint])
)

[lbendlin · Accepted solution]

You cannot List.Sum a table.  You need to use a column reference.