Forum Discussion
Solved
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 value...
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
Solution Sage
Solution SageHi 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)
Thank you.
This worked perfectly 😀