Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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)
Solved! Go to 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
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.
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
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
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])))
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]))
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
15 | |
11 | |
8 | |
8 | |
7 |
User | Count |
---|---|
15 | |
13 | |
9 | |
7 | |
6 |