Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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
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
Solved! Go to Solution.
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
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 _ Jing
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
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
15 | |
12 | |
8 | |
8 | |
7 |
User | Count |
---|---|
15 | |
13 | |
8 | |
7 | |
6 |