Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hello All,
I'm looking for assistance to convert the following IF functions in M Language.
Regards,
Jajati Dev
Solved! Go to Solution.
Hi, @JajatiDev
assuming your initial step in the PQ editor is the #"ChangedType" then the M code will be:
#"AddAgeing" = Table.AddColumn(#"ChangedType", "Ageing", each if [ReceiveDate]=DateTime.LocalNow() then 0 else need to use custom func equivalent to networkdays, DateTime.Date(DateTime.LocalNow())), type number)
this blog will help you: https://community.fabric.microsoft.com/t5/Community-Blog/Date-Networkdays-function-for-Power-Query-a...
for the rest:
#"AddAgeGroup" = Table.AddColumn(#"AddAgeing", "Age Group", each
if [Ageing]>=0 and [Ageing]<=5 then "[0 - 5]"
else if [Ageing]>=6 and [Ageing]<=10 then "[6 - 10]"
else if [Ageing]>=11 and [Ageing]<=20 then "[11 - 20]"
else if [Ageing]>=21 and [Ageing]<=30 then "[21 - 30]"
else if [Ageing]>=31 and [Ageing]<=60 then "[31 - 60]"
else if [Ageing]>=61 and [Ageing]<=90 then "[61 - 90]"
else "[ > 90]"
)
in
#"AddAgeGroup"
Proud to be a Super User!
Hi, @JajatiDev
assuming your initial step in the PQ editor is the #"ChangedType" then the M code will be:
#"AddAgeing" = Table.AddColumn(#"ChangedType", "Ageing", each if [ReceiveDate]=DateTime.LocalNow() then 0 else need to use custom func equivalent to networkdays, DateTime.Date(DateTime.LocalNow())), type number)
this blog will help you: https://community.fabric.microsoft.com/t5/Community-Blog/Date-Networkdays-function-for-Power-Query-a...
for the rest:
#"AddAgeGroup" = Table.AddColumn(#"AddAgeing", "Age Group", each
if [Ageing]>=0 and [Ageing]<=5 then "[0 - 5]"
else if [Ageing]>=6 and [Ageing]<=10 then "[6 - 10]"
else if [Ageing]>=11 and [Ageing]<=20 then "[11 - 20]"
else if [Ageing]>=21 and [Ageing]<=30 then "[21 - 30]"
else if [Ageing]>=31 and [Ageing]<=60 then "[31 - 60]"
else if [Ageing]>=61 and [Ageing]<=90 then "[61 - 90]"
else "[ > 90]"
)
in
#"AddAgeGroup"
Proud to be a Super User!
Hi, thanks for responding.
I have been working on the ageing calculation. The issue with the output is it's considering all days including the weekends between receive_data and the current date. I need to exclude weekends.
if [receive_date] = DateTime.LocalNow() then 0 else
DateTime.Date(DateTime.LocalNow()) - [receive_date]
By now I have got a good hang of IF & AND. Hoping to improve my understanding on others as well.
Proud to be a Super User!
Hi,
Here is the customer column calculation I have put in place to calculate ageing.
List.Sum({0} & List.Transform(List.Dates(Date.AddDays( [hp_receive_date], 1), Duration.Days(Date.From(DateTime.LocalNow()) - [hp_receive_date]) , #duration(1, 0, 0, 0)), each if Date.DayOfWeek(_, Day.Monday) < 5 then 1 else 0))
Allow me time to work through it because I'm a beginner. and I'm using the add column >> customer column option to provide the inputs.
Check out the July 2025 Power BI update to learn about new features.