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

[Stormin_]

Hi,

I have a power query qryTotalSales that simply has SalesAgentID and SalesVolume as an aggregate

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

MinThreshold
          30

I want to insert the threshold value into every row in qryTotalSales so that I get an output of

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

 

[sandy666]

maybe add custom column with table threshold #"Added Custom" = Table.AddColumn(Source, "Custom", each Table2), then expand

 

[Stormin_]

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

=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

 

[sandy666]

the same result with different way


have a nice day