add a column use M code to get the month

[Anonymous]

Hi, I am a fresh man use M code and I want to create a column based on this column, the current month number is 05, so when the value is 202205 and I want get a new column with value "M", the M should be current month, and previous month in the exist column is 202204,  I want to get a value"M-1" in the new column,  and if it has other month the value will be null, so how can I get a new column with M code?

Thank you.

[Vijay_A_Verma]

Difficult to understand your requirement. Can you please post sample output which you need?

[Anonymous]

today=20220520, and column A has value like 202203, 202205, 202204..

I want get a new column that based current month and column A, if column A=202205 then new column="M"

if column A=202204 then new column="M-1";

and if this month is june, 

if column A=202206 then new column="M"

if column A=202205 then new column="M-1";

I have problem with how to month 5 into "05" to match with column A and I want to learn if  your have any good ieads, Thank you

[Vijay_A_Verma]

Thanks for clarity. 

So if this is May, then what would be value for 202203? Will it be M-2?

What will be value for 202207 - Will it be M+2?

[Anonymous]

logicallly it is right, but I just need this month and previous month, so there is only two actual value M and M-1, others could set null

[Vijay_A_Verma · Accepted solution]

In a custom column use following formula where [Data] column has dates

= if Date.ToText(Date.From(DateTime.FixedLocalNow()),"yyyyMM")=Text.From([Data]) then "M"
else if Date.ToText(Date.AddMonths(Date.From(DateTime.FixedLocalNow()),-1),"yyyyMM")=Text.From([Data]) then "M-1" else null

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMjIwVorVgTJNEExTBNMMwTRHMC2UYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Data = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Data", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Date.ToText(Date.From(DateTime.FixedLocalNow()),"yyyyMM")=Text.From([Data]) then "M"
else if Date.ToText(Date.AddMonths(Date.From(DateTime.FixedLocalNow()),-1),"yyyyMM")=Text.From([Data]) then "M-1" else null)
in
    #"Added Custom"

[Anonymous]

Very nice, Thank you.