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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

add a column use M code to get the month

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.

null_0-1653019489758.png

 

1 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"

View solution in original post

6 REPLIES 6
Vijay_A_Verma
Super User
Super User

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

Anonymous
Not applicable

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

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
Not applicable

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

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
Not applicable

Very nice, Thank you.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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