Forum Discussion
Sum of values in one query based on filter from another Query
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?
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"})
6 Replies
- Smauro
Solution Sage
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"})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)
- mahoneypat
Microsoft Employee
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
