Power Query Assistance

[JajatiDev]

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

[rubayatyasmin · Accepted 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"

[JajatiDev]

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.

[rubayatyasmin]

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

[JajatiDev]

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

[JajatiDev]

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.