Forum Discussion
Conditional join - Limit rows based on running total
Hi all,
When using a conditional join I am contronted with some challenges that I am unsure on how to approach.
Hopefully the expertise within this community can guide me in how to proceed.
My challenge
- How can I return only the rows from the source query that is required to cover the demand in the target query? E.g. If I have a demand for 650 pcs in the target query, and the running total in the source query is {190,639,739} I would only like to return the rows for the 190 and 639 and not the 739.
The M code for the conditional join is as follows. I have also attached the PBIX file for your reference.
= Table.AddColumn(#"Sorted Rows1", "PO Table", each
//Only applicable for order types "Sales Orders" and "Delivery Orders"
if( [Order Type] = "Sales Order" or [Order Type] = "Delivery Order" and [Running Totals] < 0) then
Table.SelectRows(
RunningTotalsPO,
//Name of table
(PO) =>
//Conditions
[Material] = PO[Material] and
([Running Totals] * [StockQtyPrefix]) <= PO[Running Totals]
//Issue: How to only include the rows required, not all PO rows? (Required = the PO[Running Totals] required to cover [Running Totals]. Not all [PO]RunningTotals
)
else null
)
PBIX Material Availability for SalesOrder - PQ Copy_ver2
Regards
Arve
Hi v-jingzhang
I see that my question might have been poorly explained.
However, I managed to solve this earlier today.
To be able to limit the number of rows being merged I did a initial join of an index.
I then used this index as the threshold for how many lines that would be merged in the final conditional join.
I am sure that I can optimize the M-code by combining some of the code, but at least it seems to be working as intended.
I have attached the PBIX file for reference.
The conditional join to get the index
= Table.AddColumn(#"Changed Type", "PO Index Table", each
//Extracting the PO Index of the RunningTotalsPO table. The column PO Index will decide on how many rows that are returned.
//If condition limits where the statement below is applicable
if( [Order Type] = "Sales Order" and [Running Totals On Hand] < 0) then
Table.SelectRows(
RunningTotalsPO,
//Name of table
(PO) =>
//Conditions
[Material] = PO[Material] and
([Running Totals On Hand] * [StockQtyPrefix]) <= PO[Running Totals]
)
else null
)This index was added to the table
The index in turn was used as the threshold to limit the rumber of rows returned in the final conditional join.
The end result will look like this in a simple matrix
Material Availability for SalesOrder - PQ Copy_ver3
Regards
Arve
2 Replies
Hi Anonymous
Not sure if I understand it correctly. Do you want to filter rows in PO Table value to include rows whose PO[Running Totals] is greater than or equal to (>=) the value in Running Totals column outside? E.g. {639, 739, 741} are all greater than -460, so they should be kept? I am not sure which column is the demand column to be compared with PO[Running Totals]. Can you help clarify it with some examples?
Best Regards,
Community Support Team _ JingHi v-jingzhang
I see that my question might have been poorly explained.
However, I managed to solve this earlier today.
To be able to limit the number of rows being merged I did a initial join of an index.
I then used this index as the threshold for how many lines that would be merged in the final conditional join.
I am sure that I can optimize the M-code by combining some of the code, but at least it seems to be working as intended.
I have attached the PBIX file for reference.
The conditional join to get the index
= Table.AddColumn(#"Changed Type", "PO Index Table", each
//Extracting the PO Index of the RunningTotalsPO table. The column PO Index will decide on how many rows that are returned.
//If condition limits where the statement below is applicable
if( [Order Type] = "Sales Order" and [Running Totals On Hand] < 0) then
Table.SelectRows(
RunningTotalsPO,
//Name of table
(PO) =>
//Conditions
[Material] = PO[Material] and
([Running Totals On Hand] * [StockQtyPrefix]) <= PO[Running Totals]
)
else null
)This index was added to the table
The index in turn was used as the threshold to limit the rumber of rows returned in the final conditional join.
The end result will look like this in a simple matrix
Material Availability for SalesOrder - PQ Copy_ver3
Regards
Arve
