Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
53 | |
21 | |
12 | |
12 |