Sum of values in one query based on filter from another Query

[Rune_]

I have one query called

JobTasks with these columns

JobNo | JobTaskFrom | JobTaskTo

10100 | 10                | 20

I would like to add a Column called "Amount" to this Query that adds values from a query called

JobLedger

JobNo | JobTask | Amount

10100 | 11         | 50

10100 | 15         | 60

The filters for adding Amount is from the query JobTasks

JobTask >= then JobTaskFrom and <= then JobTaskTo

How do I write this function?

[mahoneypat]

Here is another approach to consider:

• merge the second query into the first one on JobNo, calling the new colum AmtTable (leave it in Table form)
• add a custom column with this function: = List.Sum([AmtTable][Amount])

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Smauro · Accepted solution]

Hi @Rune_ 

It's certainly not the quickest way, but you can try this:

    Amount = Table.AddColumn(PreviousStep, "Amount", each let no = [JobNo], from = [JobTaskFrom], to = [JobTaskTo] in List.Sum(Table.SelectRows(JobLedger, each [JobNo]=no and [JobTask]>=from and [JobTask]<=to)[Amount]), type number)

Where PreviousStep is the name of your last step.

Cheers


Edit:
This should be a bit quicker:

    #"Merge Queries" = Table.NestedJoin(PreviousStep, {"JobNo"}, Table.SelectColumns(JobLedger, {"JobNo", "JobTask", "Amount"}), {"JobNo"}, "JobLedger", JoinKind.LeftOuter),
    #"Added Amount" = Table.AddColumn(#"Merge Queries", "Amount", each let from = [JobTaskFrom], to = [JobTaskTo] in List.Sum(Table.SelectRows([JobLedger], each _[JobTask]>=from and _[JobTask]<=to)[Amount]), type number),
    #"Remove JobLedger" = Table.RemoveColumns(#"Added Amount", {"JobLedger"})

[Rune_]

Hi @Smauro 

Is there any way I can add a filter to this function

Amount = Table.AddColumn(PreviousStep, "Amount", each let no = [JobNo], from = [JobTaskFrom], to = [JobTaskTo] in List.Sum(Table.SelectRows(JobLedger, each [JobNo]=no and [JobTask]>=from and [JobTask]<=to)[Amount]), type number)

I have a column with dates and I want a filter on the dates that shows all dates < then EndOfMonth last month (example todays date is 23.06 then I would like it to show dates < then 31.05)  

[Smauro]

Hi @Rune_ 

Assuming that your [Date] column is in 'JobLedger' then this should work:

Amount = Table.AddColumn(PreviousStep, "Amount", each let no = [JobNo], from = [JobTaskFrom], to = [JobTaskTo], d = Date.StartOfMonth(DateTime.Date(DateTime.LocalNow())) in List.Sum(Table.SelectRows(JobLedger, each [Date]<d and [JobNo]=no and [JobTask]>=from and [JobTask]<=to)[Amount]), type number)

[Rune_]

Thank you. 

This worked perfectly 😀

[Rune_]

Hi @Smauro , 

Thank you for your quick reply. 

I used your first solutions "Amount = Table.AddColumn..." and it worked perfectly.