cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

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

I have one query called

10100 | 10                | 20

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

JobLedger

10100 | 11         | 50

10100 | 15         | 60

How do I write this function?

1 ACCEPTED SOLUTION
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),
#"Remove JobLedger" = Table.RemoveColumns(#"Added Amount", {"JobLedger"})``````

Feel free to connect with me:

6 REPLIES 6
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

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

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),
#"Remove JobLedger" = Table.RemoveColumns(#"Added Amount", {"JobLedger"})``````

Feel free to connect with me:

Frequent Visitor

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)

Solution Sage

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)``

Feel free to connect with me:

Frequent Visitor

Thank you.

This worked perfectly 😀

Frequent Visitor

Hi @Smauro ,

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

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors