Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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.   ...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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

v-jingzhang's avatar
v-jingzhang
Community Support
3 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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