Forum Discussion

simon139's avatar
simon139
Frequent Visitor
3 years ago
Solved

Creating a Power Query loop based on 3 columns

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=106967140648728195369&rtpof=true&sd=true

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

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)




  • 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

5 Replies

  • HotChilli's avatar
    HotChilli
    Community Champion

    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's avatar
      simon139
      Frequent Visitor

      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's avatar
        simon139
        Frequent Visitor

        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])))

  • HotChilli's avatar
    HotChilli
    Community Champion

    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. 

  • simon139's avatar
    simon139
    Frequent Visitor

    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]))