Forum Discussion
Helper IIPower 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
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-and-Power-BI/ba-p/941662
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"
5 Replies
rubayatyasminCommunity Champion
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-and-Power-BI/ba-p/941662
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"
JajatiDevAllow 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.
- 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.
- rubayatyasminyou 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