Power Query - insert column by repeating single value from another query

Stormin_

New Member
Joined
Jan 31, 2017
Messages
8
Hi,

I have a power query qryTotalSales that simply has SalesAgentID and SalesVolume as an aggregate
Code:
SalesAgentID    SalesVolume
           1             27
           2              4
           3             72
           4             11

In another power query qryThreshold I have restricted it to a single value that I need to use as a threshold
Code:
MinThreshold
          30

I want to insert the threshold value into every row in qryTotalSales so that I get an output of
Code:
SalesAgentID    SalesVolume    MinThreshold
           1             27              30
           2              4              30
           3             72              30
           4             11              30

so that I can then apply a custom true/false column with the function [SalesVolume] >= [MinThreshold] which, if true, will then waive their agent fees for the month.

I can't do this with query merge since there is no matching fields to create a join. In Access, since these are small tables, I'd normally just do a DMax to retrieve the value '30' from qryThreshold. Any idea how to do this in Power Query? (still learning)

Cheers
 
Last edited:

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
maybe add custom column with table threshold #"Added Custom" = Table.AddColumn(Source, "Custom", each Table2), then expand
 
Upvote 0
Oops, I did the classic "after not finding anything for 10 minutes, I posted the question in the forum, then discovered the actual answer a minute later"!

In qryTotalSales I create a custom column with the formula
Code:
=qryThreshold[MinThreshold]{0}
which pulls the value from TABLE[FIELD]{ROW} where ROW has order 0

I'll leave this up to help anyone else with the same question
 
Upvote 0

Forum statistics

Threads
1,223,783
Messages
6,174,524
Members
452,569
Latest member
Ron1970

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top