Creating a Power Query loop based on 3 columns

[simon139]

Hi Guys, 

I am not too good in Power Query / M language so really need your help here. I have a Python script which is working in PQ, but I need to rewrite it to 'M Language' as Power BI GateWay doesn't support Python scripts. 

Here is Data before Python script : 
https://docs.google.com/spreadsheets/d/1x-Kn0pyedkCnCSCMSOxq9q9KexDol9dh/edit?usp=sharing&ouid=10696...

Here is data after Python Script: 
https://docs.google.com/spreadsheets/d/1SofOSSq3bw3HKKmZwjX7KHiuK0_YDxA3/edit?usp=sharing&ouid=10696...

Python script takes columns Start_Date and Due_Date and creates "Payment Schedule" based on "billingcycle" columns. 
It looks like this - for issueid 256833 we have:
- 1st payment on 01.04.2016 
- 2nd Payment on 01.04.2016 + billing_cycle (6 months) which is 01.10.2016... and so on and so on
- last payment on 01.10.2020 which is the last cycle before 28.02.2021 

Here is Python Script which is actually in use: 

# 'dataset' holds the input data for this script

import pandas as pd

dataset.columns = ['issueid', 'issuekey', 'billingcycle', 'start_date','duedate']
dataset.start_date = pd.to_datetime(dataset.start_date)
dataset.duedate = pd.to_datetime(dataset.duedate)
dataset = dataset.reset_index()

counter = 0
arr = []
for i in dataset['index']:
      df_temp = dataset[dataset['index'] == i]
       s = pd.date_range(df_temp['start_date'][i] - pd.DateOffset(months=1), end=df_temp['duedate'][i], freq=df_temp['billingcycle'][i]) + pd.DateOffset(days=df_temp['start_date'][i].day)
       s = s[(s >= df_temp['start_date'][i]) & (s <= df_temp['duedate'][i])]
       if len(s) > 0:
               df_temp = pd.concat([df_temp]*len(s), ignore_index=True)
               df_temp['s'] = s
       else:
                df_temp['s'] = df_temp['start_date'][i]
        print(df_temp)
        counter += 1
        arr.append(df_temp)

dataset = pd.concat(arr, ignore_index=True)

[HotChilli]

You refer to the column in the same way as the other parts of the code I provided e.g. 

row[column name]

Obviously this has to be a number for the Date.AddMonths to work correctly. 

[HotChilli · Accepted solution]

You are pretty close.

I put this together for the Custom Column:

Table.AddColumn(#"Changed Type", "Custom",  (row) => 
List.Generate( () => (row[start]), 
each _ <= row[duedate], 
each Date.AddMonths(_,6)))

start and duedate are dates. I hardcoded the 6 just as an example

[simon139]

Thank you HotChilli. I got to the point where I can add hardcoded month, but I just do not know how to put that month into variable which is based on value in other column 

[simon139]

Ok I changed the formula and it actually works - thank you so much!! 

I would appreciate If you would quickly explain what does the (row) => stands for? And why it works with (row) bud it didn't work without it. 

= Table.AddColumn(#"Renamed Columns", "Custom", (row) =>
List.Generate( () => (row[start_date]),
each _ <= row[duedate],
each Date.AddMonths(_,row[billing])))

[simon139]

So I tried "List.Generate" function (without interval so far), but I get error after this (I changed dates to number as I tried almost everything) 

= Table.AddColumn(#"Changed Type1", "Custom", each List.Generate(
()=> [x=Date.From([start_date])],
each [x]<=Date.From([duedate]),
each [x=Date.AddMonths([x],1)],
each [x]))