M Query Nested For Loop Equivalent With Two Tables

[NeutralAlien]

Hi,

Basically I need to perform a pretty elaborate transformation on my data and I wrote Python code that does what I need. Unfortunatelly because Microsoft in its infinate wisdom doesn't allow to use Python integration with Enterprise Gateway, I have to use Power Query.

Is there a way to do a nested loop like this within the Power Query. Note that tables (dataframes) are being updated here as the loop progresses.

for i_so_all in SO_df.index:

    item_org = SO_df.loc[i_so_all, 'item_org']

    ship_date = SO_df.loc[i_so_all, 'SHIP_DATE']

    ord_qty = SO_df.loc[i_so_all,'ORDERED_QUANTITY']

    for i_po in PO_df[(PO_df['item_org'] == item_org) & (PO_df['PROMISED_DATE'] <= ship_date)].index:

        promise_date = PO_df.loc[i_po, 'PROMISED_DATE']

        PO_qty = PO_df.loc[i_po, 'QUANTITY_LEFT']

        PO_df.loc[i_po, 'QUANTITY_LEFT'] = max(0,PO_qty-ord_qty)

        SO_df.loc[i_so_all, 'ORDERED_QUANTITY'] = max(0,ord_qty-PO_qty)

        ord_qty = max(0,ord_qty-PO_qty)

[freginier]

In Power Query you need to create a function with all your transformation and then invoke this function. 

See this link https://docs.microsoft.com/en-us/power-query/custom-function