Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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?
Solved! Go to 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"})
Here is another approach to consider:
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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)
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)
Thank you.
This worked perfectly 😀
Hi @Smauro ,
Thank you for your quick reply.
I used your first solutions "Amount = Table.AddColumn..." and it worked perfectly.