Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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"
Difficult to understand your requirement. Can you please post sample output which you need?
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?
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"
Very nice, Thank you.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |