Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
NeutralAlien
Regular Visitor

M Query Nested For Loop Equivalent With Two Tables

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)
1 REPLY 1
freginier
Solution Specialist
Solution Specialist

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

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors