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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
JajatiDev
Helper II
Helper II

Power Query Assistance

Hello All,

I'm looking for assistance to convert the following IF functions in M Language.

 

  • Ageing = IF([ReceiveDate]]=TODAY(),0,NETWORKDAYS([ReceiveDate]]+1,TODAY()))
  • Age Group =
    IF(AND([Ageing]>=0,[Ageing]<=5),"[0 - 5]",
    IF(AND([Ageing]>=6,[Ageing]<=10),"[6-10]",
    IF(AND([Ageing]>=11,[Ageing]<=20),"[11 - 20]",
    IF(AND([Ageing]>=21,[Ageing]<=30),"[21 - 30]",
    IF(AND([Ageing]>=31,[Ageing]<=60),"[ 31 - 60]",
    IF(AND([Ageing]>=61,[Ageing]<=90),"[ 61 - 90]",
    "[ > 90]"))))))

 

Regards,

Jajati Dev

1 ACCEPTED SOLUTION
rubayatyasmin
Super User
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 networkdaysDateTime.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"

 

 

 

 

rubayatyasmin_0-1689517080227.png


Did I answer your question? Mark my post as a solution!super-user-logo

Proud to be a Super User!


View solution in original post

5 REPLIES 5
rubayatyasmin
Super User
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 networkdaysDateTime.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"

 

 

 

 

rubayatyasmin_0-1689517080227.png


Did I answer your question? Mark my post as a solution!super-user-logo

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.

you are going right. Networkdays also excludes weekends so you need to have a list of weekends or find a way to CALCULATE IT. I gave a link that guides a step by step way on how to use PQ equivalent of networkdays. thanks

Did I answer your question? Mark my post as a solution!super-user-logo

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. 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

Top Solution Authors