Power Query: Table.SelectRows where "each" condition is not a constant

[PentaGalCXO]

I need help with Power Query M code. Assume I have a list of customers with [CustNum] as key (via query "qryCustomerList"). I would now like to add a column "SalesTable" where the field contains a table of Sales data relative to that customer (via query "qrySales"). Note that qrySales has field [SoldToNum] which ties to [CustNum], and several other fields I will need later in the query. Here is a simplified version of my query:

Let
Source = qryCustomerList,
AddSalesTable = Table.AddColumn(Source,"SalesTable", each Table.SelectRows(qrySalesData, each [SoldToNum] = [CustNum])),

The problem is PQ doesn't recognize [CustNum] in this context. It expects [CustNum] to be a field in qrySales. I need it to reference the value of [CustNum] for the current row of Source. Note that if I replace [CustNum] with a static value such as "1234" the query will run and I get a table of 1234's Sales data for each customer in Source. So I feel like I'm close. In the DAX world I think this would be a case for Earlier(), but I can't find the 'M' equivalent.

Help!

 

[Matt Allington]

Why don't you just use the UI menu option "Merge". This is exactly what it is designed to do. Or have I missed something?

 

[PentaGalCXO]

Matt- My goal is to have a table object for each row in CustList. I will then manipulate the table object on each row with M code according to values in CustList. To be a bit more transparent, the CustList is actually a Contracts table. For each customer in Contracts I need to calculate a rebate. Unfortunately all our contracts are unique where different customers earn rebates on different product sets, specific types of purchases, etc. Thus for each line in Contracts I plan to manipulate the table object with unique filters based on other columns in the Contracts table. Once the unique filters are applied I will Expand [SalesTable] & load the detail sales records to my data model. Leastwise that is the plan for now. I appreciate any guidance...

 

[Ozeroth]

Agree Merge is the best way to do this in this case.

However, if you want to reference something in the current row of the 'outer' table, you can save it in a variable before calling Table.SelectRows.
In your example, this would look like:

[COLOR=#333333]
let[/COLOR]
[COLOR=#333333]   Source = qryCustomerList,[/COLOR]
[COLOR=#333333]   AddSalesTable = Table.AddColumn(
      Source,
      "SalesTable",
      each
[B]         let CurrentCustNum = [CustNum]
         in[/B] Table.SelectRows(qrySalesData, each [SoldToNum] = CurrentCustNum)
   ),
...[/COLOR]

 

[PentaGalCXO]

Ozeroth - Your suggestion worked! Sticking the column value into a variable, then referencing the variable in Table.SelectRows may not be the most elegant solution, but simple enough to implement. Thank you very much for the assistance.