M-Query

[Niraj_vora0106]

Hi community,

Hope you all are doing well!

I've a doubt regarding Power BI M-query.

I've created an IF statement via Calculated column, which I'm looking to create in Power Query or Transform Query using M-Query.

Below is the IF statement:

Fiscal_Year = IF(Append2[Month]<4, "FY" & Append2[Year]-1 &"-"& RIGHT(Append2[Year],4), "FY" & Append2[Year] &"-"& RIGHT(Append2[Year],4) + 1)

output: FY2021-2022

I want to create this in backend using M-query.

Please help.

[HotChilli]

You will need to Add a Custom Column in Power Query.

It will be a combination of an 'if' statement :

https://gorilla.bi/power-query/if-function-in-power-query/ 

and Text.Right

https://learn.microsoft.com/en-us/powerquery-m/text-end 

You may need other Text functions:

https://learn.microsoft.com/en-us/powerquery-m/text-functions 

because you may need to change some number fields to text so that you can use them.

You can combine text in power query with '&'.

My advice is start it simply (get something basic that works) and build it step by step adding logic

[Niraj_vora0106]

Hi @HotChilli 

I tried using M-query text functions.

below is the snip of the code I tried for creating Fiscal Year:

Please help me out if I'm doing anything wrong in this.

Used Text.From to convert Number to Text. I converted Month and Year datatypes to Text also

Still getting error

below is the error snip:

Please help!

[Padycosmos]

You may try Number.ToText 

https://learn.microsoft.com/en-us/powerquery-m/number-totext

[Niraj_vora0106]

Hi @Padycosmos 

Still getting same error which I mentioned earlier.

Please correct me if I'm wrong

[Padycosmos]

Please delete all that I have scored-off and try again:

[Niraj_vora0106]

Still getting same error.

[Padycosmos]

You may try again after making this change. If it still doesn't work, please share the pbix file, if possible

[Niraj_vora0106]

Hi @Padycosmos 

I tried but still same error.

Really thank you for your help. I'll try finding other ways too

[Padycosmos]

[Year] is a numeric field, therefore, Text.End([Year],4) will not work, instead please try 

Text.End(Number.ToText([Year]),4)

[Niraj_vora0106]

Hi,

I tried doing the way you told 

Below is the snip: for few line items it is working and for few it is not 

Below is the snip of error

[Padycosmos]

It appears that there is some problem with the data. Could you please change the Data type of the Fiscal Year Column to Whole number and apply the last formula again